universe@38: /* universe@38: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER. universe@38: * universe@38: * Copyright 2018 Mike Becker. All rights reserved. universe@38: * universe@38: * Redistribution and use in source and binary forms, with or without universe@38: * modification, are permitted provided that the following conditions are met: universe@38: * universe@38: * 1. Redistributions of source code must retain the above copyright universe@38: * notice, this list of conditions and the following disclaimer. universe@38: * universe@38: * 2. Redistributions in binary form must reproduce the above copyright universe@38: * notice, this list of conditions and the following disclaimer in the universe@38: * documentation and/or other materials provided with the distribution. universe@38: * universe@38: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" universe@38: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE universe@38: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE universe@38: * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE universe@38: * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR universe@38: * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF universe@38: * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS universe@38: * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN universe@38: * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) universe@38: * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE universe@38: * POSSIBILITY OF SUCH DAMAGE. universe@38: * universe@38: */ universe@38: package de.uapcore.lightpit.dao.postgres; universe@38: universe@59: import de.uapcore.lightpit.dao.VersionDao; universe@86: import de.uapcore.lightpit.entities.Project; universe@86: import de.uapcore.lightpit.entities.Version; universe@86: import de.uapcore.lightpit.entities.VersionStatus; universe@38: universe@38: import java.sql.Connection; universe@38: import java.sql.PreparedStatement; universe@38: import java.sql.ResultSet; universe@38: import java.sql.SQLException; universe@47: import java.util.ArrayList; universe@47: import java.util.List; universe@38: import java.util.Objects; universe@38: universe@59: public final class PGVersionDao implements VersionDao { universe@38: universe@47: private final PreparedStatement insert, update, list, find; universe@38: universe@59: public PGVersionDao(Connection connection) throws SQLException { universe@47: list = connection.prepareStatement( universe@83: "select versionid, project, p.name as projectname, v.name, ordinal, status " + universe@83: "from lpit_version v " + universe@83: "join lpit_project p on v.project = p.projectid " + universe@59: "where project = ? " + universe@83: "order by ordinal desc, lower(v.name) desc"); universe@47: universe@47: find = connection.prepareStatement( universe@83: "select versionid, project, p.name as projectname, v.name, ordinal, status " + universe@83: "from lpit_version v " + universe@83: "join lpit_project p on v.project = p.projectid " + universe@75: "where versionid = ?"); universe@38: universe@38: insert = connection.prepareStatement( universe@59: "insert into lpit_version (project, name, ordinal, status) values (?, ?, ?, ?::version_status)" universe@38: ); universe@38: update = connection.prepareStatement( universe@75: "update lpit_version set name = ?, ordinal = ?, status = ?::version_status where versionid = ?" universe@38: ); universe@38: } universe@38: universe@75: private Version mapColumns(ResultSet result) throws SQLException { universe@75: final var project = new Project(result.getInt("project")); universe@83: project.setName(result.getString("projectname")); universe@86: final var version = new Version(result.getInt("versionid")); universe@86: version.setProject(project); universe@59: version.setName(result.getString("name")); universe@59: version.setOrdinal(result.getInt("ordinal")); universe@59: version.setStatus(VersionStatus.valueOf(result.getString("status"))); universe@59: return version; universe@38: } universe@38: universe@38: @Override universe@59: public void save(Version instance) throws SQLException { universe@38: Objects.requireNonNull(instance.getName()); universe@59: Objects.requireNonNull(instance.getProject()); universe@59: insert.setInt(1, instance.getProject().getId()); universe@59: insert.setString(2, instance.getName()); universe@59: insert.setInt(3, instance.getOrdinal()); universe@59: insert.setString(4, instance.getStatus().name()); universe@38: insert.executeUpdate(); universe@38: } universe@38: universe@38: @Override universe@59: public boolean update(Version instance) throws SQLException { universe@75: if (instance.getId() < 0) return false; universe@38: Objects.requireNonNull(instance.getName()); universe@38: update.setString(1, instance.getName()); universe@59: update.setInt(2, instance.getOrdinal()); universe@59: update.setString(3, instance.getStatus().name()); universe@59: update.setInt(4, instance.getId()); universe@38: return update.executeUpdate() > 0; universe@38: } universe@47: universe@47: @Override universe@59: public List list(Project project) throws SQLException { universe@59: list.setInt(1, project.getId()); universe@59: List versions = new ArrayList<>(); universe@47: try (var result = list.executeQuery()) { universe@47: while (result.next()) { universe@75: final var v = mapColumns(result); universe@75: v.setProject(project); universe@75: versions.add(v); universe@47: } universe@47: } universe@59: return versions; universe@47: } universe@47: universe@47: @Override universe@59: public Version find(int id) throws SQLException { universe@47: find.setInt(1, id); universe@47: try (var result = find.executeQuery()) { universe@47: if (result.next()) { universe@47: return mapColumns(result); universe@47: } else { universe@47: return null; universe@47: } universe@47: } universe@47: } universe@38: }