1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGComponentDao.java Thu Oct 15 13:31:52 2020 +0200
1.3 @@ -0,0 +1,139 @@
1.4 +/*
1.5 + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
1.6 + *
1.7 + * Copyright 2018 Mike Becker. All rights reserved.
1.8 + *
1.9 + * Redistribution and use in source and binary forms, with or without
1.10 + * modification, are permitted provided that the following conditions are met:
1.11 + *
1.12 + * 1. Redistributions of source code must retain the above copyright
1.13 + * notice, this list of conditions and the following disclaimer.
1.14 + *
1.15 + * 2. Redistributions in binary form must reproduce the above copyright
1.16 + * notice, this list of conditions and the following disclaimer in the
1.17 + * documentation and/or other materials provided with the distribution.
1.18 + *
1.19 + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
1.20 + * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
1.21 + * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
1.22 + * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
1.23 + * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
1.24 + * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
1.25 + * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
1.26 + * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
1.27 + * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
1.28 + * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
1.29 + * POSSIBILITY OF SUCH DAMAGE.
1.30 + *
1.31 + */
1.32 +package de.uapcore.lightpit.dao.postgres;
1.33 +
1.34 +import de.uapcore.lightpit.dao.ComponentDao;
1.35 +import de.uapcore.lightpit.dao.Functions;
1.36 +import de.uapcore.lightpit.entities.Component;
1.37 +import de.uapcore.lightpit.entities.Project;
1.38 +import de.uapcore.lightpit.entities.User;
1.39 +import de.uapcore.lightpit.types.WebColor;
1.40 +
1.41 +import java.sql.Connection;
1.42 +import java.sql.PreparedStatement;
1.43 +import java.sql.ResultSet;
1.44 +import java.sql.SQLException;
1.45 +import java.util.ArrayList;
1.46 +import java.util.List;
1.47 +import java.util.Objects;
1.48 +
1.49 +public final class PGComponentDao implements ComponentDao {
1.50 +
1.51 + private final PreparedStatement insert, update, list, find;
1.52 +
1.53 + public PGComponentDao(Connection connection) throws SQLException {
1.54 + list = connection.prepareStatement(
1.55 + "select id, name, color, ordinal, description, " +
1.56 + "userid, username, givenname, lastname, mail " +
1.57 + "from lpit_component " +
1.58 + "left join lpit_user on lead = userid " +
1.59 + "where project = ? " +
1.60 + "order by ordinal desc, lower(name) desc");
1.61 +
1.62 + find = connection.prepareStatement(
1.63 + "select id, name, color, ordinal, description, " +
1.64 + "userid, username, givenname, lastname, mail " +
1.65 + "from lpit_component " +
1.66 + "left join lpit_user on lead = userid " +
1.67 + "where id = ? ");
1.68 +
1.69 + insert = connection.prepareStatement(
1.70 + "insert into lpit_component (project, name, color, ordinal, description, lead) values (?, ?, ?, ?, ?, ?)"
1.71 + );
1.72 +
1.73 + update = connection.prepareStatement(
1.74 + "update lpit_component set name = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
1.75 + );
1.76 + }
1.77 +
1.78 + private static Component mapColumns(ResultSet result) throws SQLException {
1.79 + final var component = new Component(result.getInt("id"));
1.80 + component.setName(result.getString("name"));
1.81 + try {
1.82 + component.setColor(new WebColor(result.getString("color")));
1.83 + } catch (IllegalArgumentException ex) {
1.84 + // if someone tempered with the database we default the color to black
1.85 + component.setColor(new WebColor("000000"));
1.86 + }
1.87 + component.setOrdinal(result.getInt("ordinal"));
1.88 + component.setDescription(result.getString("description"));
1.89 + component.setLead(PGUserDao.mapColumns(result));
1.90 + return component;
1.91 + }
1.92 +
1.93 + @Override
1.94 + public void save(Component instance, Project project) throws SQLException {
1.95 + Objects.requireNonNull(instance.getName());
1.96 + insert.setInt(1, project.getId());
1.97 + insert.setString(2, instance.getName());
1.98 + insert.setString(3, instance.getColor().getHex());
1.99 + insert.setInt(4, instance.getOrdinal());
1.100 + Functions.setStringOrNull(insert, 5, instance.getDescription());
1.101 + Functions.setForeignKeyOrNull(insert, 6, instance.getLead(), User::getId);
1.102 + insert.executeUpdate();
1.103 + }
1.104 +
1.105 + @Override
1.106 + public boolean update(Component instance) throws SQLException {
1.107 + if (instance.getId() < 0) return false;
1.108 + Objects.requireNonNull(instance.getName());
1.109 + Objects.requireNonNull(instance.getColor());
1.110 + update.setString(1, instance.getName());
1.111 + update.setString(2, instance.getColor().getHex());
1.112 + update.setInt(3, instance.getOrdinal());
1.113 + Functions.setStringOrNull(update, 4, instance.getDescription());
1.114 + Functions.setForeignKeyOrNull(update, 5, instance.getLead(), User::getId);
1.115 + update.setInt(6, instance.getId());
1.116 + return update.executeUpdate() > 0;
1.117 + }
1.118 +
1.119 + @Override
1.120 + public List<Component> list(Project project) throws SQLException {
1.121 + list.setInt(1, project.getId());
1.122 + List<Component> components = new ArrayList<>();
1.123 + try (var result = list.executeQuery()) {
1.124 + while (result.next()) {
1.125 + components.add(mapColumns(result));
1.126 + }
1.127 + }
1.128 + return components;
1.129 + }
1.130 +
1.131 + @Override
1.132 + public Component find(int id) throws SQLException {
1.133 + find.setInt(1, id);
1.134 + try (var result = find.executeQuery()) {
1.135 + if (result.next()) {
1.136 + return mapColumns(result);
1.137 + } else {
1.138 + return null;
1.139 + }
1.140 + }
1.141 + }
1.142 +}
