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@128: import de.uapcore.lightpit.dao.ComponentDao; universe@128: import de.uapcore.lightpit.dao.Functions; universe@128: import de.uapcore.lightpit.entities.Component; universe@86: import de.uapcore.lightpit.entities.Project; universe@128: import de.uapcore.lightpit.entities.User; universe@128: import de.uapcore.lightpit.types.WebColor; 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@128: public final class PGComponentDao implements ComponentDao { universe@38: universe@47: private final PreparedStatement insert, update, list, find; universe@38: universe@128: public PGComponentDao(Connection connection) throws SQLException { universe@47: list = connection.prepareStatement( universe@128: "select id, name, color, ordinal, description, " + universe@128: "userid, username, givenname, lastname, mail " + universe@128: "from lpit_component " + universe@128: "left join lpit_user on lead = userid " + universe@59: "where project = ? " + universe@128: "order by ordinal desc, lower(name) desc"); universe@47: universe@47: find = connection.prepareStatement( universe@128: "select id, name, color, ordinal, description, " + universe@128: "userid, username, givenname, lastname, mail " + universe@128: "from lpit_component " + universe@128: "left join lpit_user on lead = userid " + universe@128: "where id = ? "); universe@38: universe@38: insert = connection.prepareStatement( universe@128: "insert into lpit_component (project, name, color, ordinal, description, lead) values (?, ?, ?, ?, ?, ?)" universe@38: ); universe@128: universe@38: update = connection.prepareStatement( universe@128: "update lpit_component set name = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?" universe@38: ); universe@38: } universe@38: universe@128: private static Component mapColumns(ResultSet result) throws SQLException { universe@128: final var component = new Component(result.getInt("id")); universe@128: component.setName(result.getString("name")); universe@128: try { universe@128: component.setColor(new WebColor(result.getString("color"))); universe@128: } catch (IllegalArgumentException ex) { universe@128: // if someone tempered with the database we default the color to black universe@128: component.setColor(new WebColor("000000")); universe@128: } universe@128: component.setOrdinal(result.getInt("ordinal")); universe@128: component.setDescription(result.getString("description")); universe@128: component.setLead(PGUserDao.mapColumns(result)); universe@128: return component; universe@38: } universe@38: universe@38: @Override universe@128: public void save(Component instance, Project project) throws SQLException { universe@38: Objects.requireNonNull(instance.getName()); universe@128: insert.setInt(1, project.getId()); universe@59: insert.setString(2, instance.getName()); universe@128: insert.setString(3, instance.getColor().getHex()); universe@128: insert.setInt(4, instance.getOrdinal()); universe@128: Functions.setStringOrNull(insert, 5, instance.getDescription()); universe@128: Functions.setForeignKeyOrNull(insert, 6, instance.getLead(), User::getId); universe@38: insert.executeUpdate(); universe@38: } universe@38: universe@38: @Override universe@128: public boolean update(Component instance) throws SQLException { universe@75: if (instance.getId() < 0) return false; universe@38: Objects.requireNonNull(instance.getName()); universe@128: Objects.requireNonNull(instance.getColor()); universe@38: update.setString(1, instance.getName()); universe@128: update.setString(2, instance.getColor().getHex()); universe@128: update.setInt(3, instance.getOrdinal()); universe@128: Functions.setStringOrNull(update, 4, instance.getDescription()); universe@128: Functions.setForeignKeyOrNull(update, 5, instance.getLead(), User::getId); universe@128: update.setInt(6, instance.getId()); universe@38: return update.executeUpdate() > 0; universe@38: } universe@47: universe@47: @Override universe@128: public List list(Project project) throws SQLException { universe@59: list.setInt(1, project.getId()); universe@128: List components = new ArrayList<>(); universe@47: try (var result = list.executeQuery()) { universe@47: while (result.next()) { universe@128: components.add(mapColumns(result)); universe@47: } universe@47: } universe@128: return components; universe@47: } universe@47: universe@47: @Override universe@128: public Component 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: }