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.List; universe@38: universe@128: public final class PGComponentDao implements ComponentDao { universe@38: universe@138: private final PreparedStatement insert, update, list, find, findByNode; universe@38: universe@128: public PGComponentDao(Connection connection) throws SQLException { universe@138: final var query = "select id, name, node, color, ordinal, description, " + universe@138: "userid, username, givenname, lastname, mail " + universe@138: "from lpit_component " + universe@138: "left join lpit_user on lead = userid"; universe@138: universe@153: list = connection.prepareStatement(query + " where project = ? order by ordinal, lower(name)"); universe@47: universe@138: find = connection.prepareStatement(query + " where id = ? "); universe@138: universe@138: findByNode = connection.prepareStatement(query + " where project = ? and node = ?"); universe@38: universe@38: insert = connection.prepareStatement( universe@138: "insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)" universe@38: ); universe@128: universe@38: update = connection.prepareStatement( universe@138: "update lpit_component set name = ?, node = ?, 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@138: component.setNode(result.getString("node")); 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@138: private static int setColumns(PreparedStatement stmt, Component instance) throws SQLException { universe@138: int column = 0; universe@138: stmt.setString(++column, instance.getName()); universe@138: stmt.setString(++column, instance.getNode()); universe@138: stmt.setString(++column, instance.getColor().getHex()); universe@138: stmt.setInt(++column, instance.getOrdinal()); universe@138: Functions.setStringOrNull(stmt, ++column, instance.getDescription()); universe@138: Functions.setForeignKeyOrNull(stmt, ++column, instance.getLead(), User::getId); universe@138: return column; universe@138: } universe@138: universe@38: @Override universe@128: public void save(Component instance, Project project) throws SQLException { universe@138: int column = setColumns(insert, instance); universe@138: insert.setInt(++column, project.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@138: int column = setColumns(update, instance); universe@138: update.setInt(++column, instance.getId()); universe@38: return update.executeUpdate() > 0; universe@38: } universe@47: universe@138: universe@47: @Override universe@128: public List list(Project project) throws SQLException { universe@59: list.setInt(1, project.getId()); universe@138: return Functions.list(list, PGComponentDao::mapColumns); universe@47: } universe@47: universe@47: @Override universe@128: public Component find(int id) throws SQLException { universe@47: find.setInt(1, id); universe@138: return Functions.find(find, PGComponentDao::mapColumns); universe@138: } universe@138: universe@138: @Override universe@138: public Component findByNode(Project project, String node) throws SQLException { universe@138: findByNode.setInt(1, project.getId()); universe@138: findByNode.setString(2, node);; universe@138: return Functions.find(findByNode, PGComponentDao::mapColumns); universe@47: } universe@38: }