src/main/java/de/uapcore/lightpit/dao/postgres/PGComponentDao.java

Sat, 31 Oct 2020 09:56:00 +0100

author
Mike Becker <universe@uap-core.de>
date
Sat, 31 Oct 2020 09:56:00 +0100
changeset 155
3ace475224f7
parent 153
e914fbf4decc
permissions
-rw-r--r--

fixes issue sorter not recognizing issues as "equal" if their ETAs are both null

38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
1 /*
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
2 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
3 *
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
4 * Copyright 2018 Mike Becker. All rights reserved.
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
5 *
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
6 * Redistribution and use in source and binary forms, with or without
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
7 * modification, are permitted provided that the following conditions are met:
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
8 *
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
9 * 1. Redistributions of source code must retain the above copyright
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
10 * notice, this list of conditions and the following disclaimer.
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
11 *
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
12 * 2. Redistributions in binary form must reproduce the above copyright
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
13 * notice, this list of conditions and the following disclaimer in the
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
14 * documentation and/or other materials provided with the distribution.
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
15 *
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
16 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
17 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
18 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
19 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
20 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
21 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
22 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
23 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
24 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
25 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
26 * POSSIBILITY OF SUCH DAMAGE.
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
27 *
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
28 */
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
29 package de.uapcore.lightpit.dao.postgres;
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
30
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
31 import de.uapcore.lightpit.dao.ComponentDao;
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
32 import de.uapcore.lightpit.dao.Functions;
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
33 import de.uapcore.lightpit.entities.Component;
86
0a658e53177c improves issue overview and adds progress information
Mike Becker <universe@uap-core.de>
parents: 83
diff changeset
34 import de.uapcore.lightpit.entities.Project;
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
35 import de.uapcore.lightpit.entities.User;
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
36 import de.uapcore.lightpit.types.WebColor;
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
37
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
38 import java.sql.Connection;
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
39 import java.sql.PreparedStatement;
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
40 import java.sql.ResultSet;
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
41 import java.sql.SQLException;
47
57cfb94ab99f projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents: 38
diff changeset
42 import java.util.List;
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
43
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
44 public final class PGComponentDao implements ComponentDao {
47
57cfb94ab99f projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents: 38
diff changeset
45
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
46 private final PreparedStatement insert, update, list, find, findByNode;
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
47
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
48 public PGComponentDao(Connection connection) throws SQLException {
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
49 final var query = "select id, name, node, color, ordinal, description, " +
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
50 "userid, username, givenname, lastname, mail " +
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
51 "from lpit_component " +
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
52 "left join lpit_user on lead = userid";
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
53
153
e914fbf4decc wrong sort order for components - fixes #35
Mike Becker <universe@uap-core.de>
parents: 138
diff changeset
54 list = connection.prepareStatement(query + " where project = ? order by ordinal, lower(name)");
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
55
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
56 find = connection.prepareStatement(query + " where id = ? ");
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
57
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
58 findByNode = connection.prepareStatement(query + " where project = ? and node = ?");
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
59
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
60 insert = connection.prepareStatement(
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
61 "insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)"
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
62 );
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
63
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
64 update = connection.prepareStatement(
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
65 "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
66 );
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
67 }
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
68
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
69 private static Component mapColumns(ResultSet result) throws SQLException {
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
70 final var component = new Component(result.getInt("id"));
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
71 component.setName(result.getString("name"));
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
72 component.setNode(result.getString("node"));
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
73 try {
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
74 component.setColor(new WebColor(result.getString("color")));
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
75 } catch (IllegalArgumentException ex) {
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
76 // if someone tempered with the database we default the color to black
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
77 component.setColor(new WebColor("000000"));
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
78 }
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
79 component.setOrdinal(result.getInt("ordinal"));
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
80 component.setDescription(result.getString("description"));
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
81 component.setLead(PGUserDao.mapColumns(result));
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
82 return component;
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
83 }
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
84
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
85 private static int setColumns(PreparedStatement stmt, Component instance) throws SQLException {
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
86 int column = 0;
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
87 stmt.setString(++column, instance.getName());
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
88 stmt.setString(++column, instance.getNode());
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
89 stmt.setString(++column, instance.getColor().getHex());
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
90 stmt.setInt(++column, instance.getOrdinal());
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
91 Functions.setStringOrNull(stmt, ++column, instance.getDescription());
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
92 Functions.setForeignKeyOrNull(stmt, ++column, instance.getLead(), User::getId);
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
93 return column;
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
94 }
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
95
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
96 @Override
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
97 public void save(Component instance, Project project) throws SQLException {
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
98 int column = setColumns(insert, instance);
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
99 insert.setInt(++column, project.getId());
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
100 insert.executeUpdate();
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
101 }
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
102
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
103 @Override
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
104 public boolean update(Component instance) throws SQLException {
75
33b6843fdf8a adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents: 62
diff changeset
105 if (instance.getId() < 0) return false;
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
106 int column = setColumns(update, instance);
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
107 update.setInt(++column, instance.getId());
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
108 return update.executeUpdate() > 0;
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
109 }
47
57cfb94ab99f projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents: 38
diff changeset
110
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
111
47
57cfb94ab99f projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents: 38
diff changeset
112 @Override
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
113 public List<Component> list(Project project) throws SQLException {
59
c759c60507a2 adds version management
Mike Becker <universe@uap-core.de>
parents: 51
diff changeset
114 list.setInt(1, project.getId());
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
115 return Functions.list(list, PGComponentDao::mapColumns);
47
57cfb94ab99f projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents: 38
diff changeset
116 }
57cfb94ab99f projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents: 38
diff changeset
117
57cfb94ab99f projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents: 38
diff changeset
118 @Override
128
947d0f6a6a83 changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents: 86
diff changeset
119 public Component find(int id) throws SQLException {
47
57cfb94ab99f projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents: 38
diff changeset
120 find.setInt(1, id);
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
121 return Functions.find(find, PGComponentDao::mapColumns);
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
122 }
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
123
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
124 @Override
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
125 public Component findByNode(Project project, String node) throws SQLException {
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
126 findByNode.setInt(1, project.getId());
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
127 findByNode.setString(2, node);;
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
128 return Functions.find(findByNode, PGComponentDao::mapColumns);
47
57cfb94ab99f projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents: 38
diff changeset
129 }
38
cf85ef18f231 adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff changeset
130 }

mercurial