Sun, 17 May 2020 16:38:04 +0200
moves project index to separate sub path
unfortunately the current menu highlighting strategy sucks and this is the easiest way so that everything looks correct
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 | |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
31 | import de.uapcore.lightpit.dao.ProjectDao; |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
32 | import de.uapcore.lightpit.entities.Project; |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
33 | import de.uapcore.lightpit.entities.User; |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
34 | |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
35 | import java.sql.Connection; |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
36 | import java.sql.PreparedStatement; |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
37 | import java.sql.ResultSet; |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
38 | import java.sql.SQLException; |
47
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
39 | import java.util.ArrayList; |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
40 | import java.util.List; |
38
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
41 | import java.util.Objects; |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
42 | |
47
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
43 | import static de.uapcore.lightpit.dao.Functions.setForeignKeyOrNull; |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
44 | import static de.uapcore.lightpit.dao.Functions.setStringOrNull; |
38
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
45 | |
59 | 46 | public final class PGProjectDao implements ProjectDao { |
47
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
47 | |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
48 | private final PreparedStatement insert, update, list, find; |
38
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
49 | |
47
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
50 | public PGProjectDao(Connection connection) throws SQLException { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
51 | list = connection.prepareStatement( |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
52 | "select id, name, description, repourl, " + |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
53 | "userid, username, lastname, givenname, mail " + |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
54 | "from lpit_project " + |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
55 | "left join lpit_user owner on lpit_project.owner = owner.userid " + |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
56 | "order by name"); |
38
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
57 | |
47
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
58 | find = connection.prepareStatement( |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
59 | "select id, name, description, repourl, " + |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
60 | "userid, username, lastname, givenname, mail " + |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
61 | "from lpit_project " + |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
62 | "left join lpit_user owner on lpit_project.owner = owner.userid " + |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
63 | "where id = ?"); |
38
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
64 | |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
65 | insert = connection.prepareStatement( |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
66 | "insert into lpit_project (name, description, repourl, owner) values (?, ?, ?, ?)" |
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 | update = connection.prepareStatement( |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
69 | "update lpit_project set name = ?, description = ?, repourl = ?, owner = ? where id = ?" |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
70 | ); |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
71 | } |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
72 | |
47
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
73 | public Project mapColumns(ResultSet result) throws SQLException { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
74 | final var proj = new Project(result.getInt("id")); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
75 | proj.setName(result.getString("name")); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
76 | proj.setDescription(result.getString("description")); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
77 | proj.setRepoUrl(result.getString("repourl")); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
78 | |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
79 | final int id = result.getInt("userid"); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
80 | if (id != 0) { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
81 | final var user = new User(id); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
82 | user.setUsername(result.getString("username")); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
83 | user.setGivenname(result.getString("givenname")); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
84 | user.setLastname(result.getString("lastname")); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
85 | user.setMail(result.getString("mail")); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
86 | proj.setOwner(user); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
87 | } |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
88 | |
38
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
89 | return proj; |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
90 | } |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
91 | |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
92 | @Override |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
93 | public void save(Project instance) throws SQLException { |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
94 | Objects.requireNonNull(instance.getName()); |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
95 | insert.setString(1, instance.getName()); |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
96 | setStringOrNull(insert, 2, instance.getDescription()); |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
97 | setStringOrNull(insert, 3, instance.getRepoUrl()); |
51
dd0a45ae25d7
adds the possibility to add users / developers
Mike Becker <universe@uap-core.de>
parents:
47
diff
changeset
|
98 | setForeignKeyOrNull(insert, 4, instance.getOwner(), User::getId); |
38
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
99 | insert.executeUpdate(); |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
100 | } |
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 | @Override |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
103 | public boolean update(Project instance) throws SQLException { |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
104 | Objects.requireNonNull(instance.getName()); |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
105 | update.setString(1, instance.getName()); |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
106 | setStringOrNull(update, 2, instance.getDescription()); |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
107 | setStringOrNull(update, 3, instance.getRepoUrl()); |
51
dd0a45ae25d7
adds the possibility to add users / developers
Mike Becker <universe@uap-core.de>
parents:
47
diff
changeset
|
108 | setForeignKeyOrNull(update, 4, instance.getOwner(), User::getId); |
47
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
109 | update.setInt(5, instance.getId()); |
38
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
110 | return update.executeUpdate() > 0; |
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
111 | } |
47
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
112 | |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
113 | @Override |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
114 | public List<Project> list() throws SQLException { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
115 | List<Project> projects = new ArrayList<>(); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
116 | try (var result = list.executeQuery()) { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
117 | while (result.next()) { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
118 | projects.add(mapColumns(result)); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
119 | } |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
120 | } |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
121 | return projects; |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
122 | } |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
123 | |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
124 | @Override |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
125 | public Project find(int id) throws SQLException { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
126 | find.setInt(1, id); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
127 | try (var result = find.executeQuery()) { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
128 | if (result.next()) { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
129 | return mapColumns(result); |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
130 | } else { |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
131 | return null; |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
132 | } |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
133 | } |
57cfb94ab99f
projects can now be added and updated
Mike Becker <universe@uap-core.de>
parents:
38
diff
changeset
|
134 | } |
38
cf85ef18f231
adds DAO for Project entity and save/update methods
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
135 | } |