Mercurial > hg > LightPIT / file comparison

comparison: src/main/java/de/uapcore/lightpit/dao/postgres/PGProjectDao.java

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

changeset 138
e2aa673dd473
parent 128
947d0f6a6a83
equal deleted inserted replaced
137:a7e543ab0c5f 138:e2aa673dd473
26 * POSSIBILITY OF SUCH DAMAGE. 26 * POSSIBILITY OF SUCH DAMAGE.
27 * 27 *
28 */ 28 */
29 package de.uapcore.lightpit.dao.postgres; 29 package de.uapcore.lightpit.dao.postgres;
30 30
31 import de.uapcore.lightpit.dao.Functions;
31 import de.uapcore.lightpit.dao.ProjectDao; 32 import de.uapcore.lightpit.dao.ProjectDao;
32 import de.uapcore.lightpit.entities.IssueSummary; 33 import de.uapcore.lightpit.entities.IssueSummary;
33 import de.uapcore.lightpit.entities.Project; 34 import de.uapcore.lightpit.entities.Project;
34 import de.uapcore.lightpit.entities.User; 35 import de.uapcore.lightpit.entities.User;
35 36
36 import java.sql.Connection; 37 import java.sql.Connection;
37 import java.sql.PreparedStatement; 38 import java.sql.PreparedStatement;
38 import java.sql.ResultSet; 39 import java.sql.ResultSet;
39 import java.sql.SQLException; 40 import java.sql.SQLException;
40 import java.util.ArrayList;
41 import java.util.List; 41 import java.util.List;
42 import java.util.Objects;
43 42
44 import static de.uapcore.lightpit.dao.Functions.setForeignKeyOrNull; 43 import static de.uapcore.lightpit.dao.Functions.setForeignKeyOrNull;
45 import static de.uapcore.lightpit.dao.Functions.setStringOrNull; 44 import static de.uapcore.lightpit.dao.Functions.setStringOrNull;
46 45
47 public final class PGProjectDao implements ProjectDao { 46 public final class PGProjectDao implements ProjectDao {
48 47
49 private final PreparedStatement insert, update, list, find; 48 private final PreparedStatement insert, update, list, find, findByNode;
50 private final PreparedStatement issue_summary; 49 private final PreparedStatement issue_summary;
51 50
52 public PGProjectDao(Connection connection) throws SQLException { 51 public PGProjectDao(Connection connection) throws SQLException {
53 list = connection.prepareStatement( 52 final var query = "select projectid, name, node, description, repourl, " +
54 "select projectid, name, description, repourl, " + 53 "userid, username, lastname, givenname, mail " +
55 "userid, username, lastname, givenname, mail " + 54 "from lpit_project " +
56 "from lpit_project " + 55 "left join lpit_user owner on lpit_project.owner = owner.userid ";
57 "left join lpit_user owner on lpit_project.owner = owner.userid " +
58 "order by name");
59 56
60 find = connection.prepareStatement( 57 list = connection.prepareStatement(query + " order by name");
61 "select projectid, name, description, repourl, " + 58
62 "userid, username, lastname, givenname, mail " + 59 find = connection.prepareStatement(query + " where projectid = ?");
63 "from lpit_project " + 60 findByNode = connection.prepareStatement(query + " where node = ?");
64 "left join lpit_user owner on lpit_project.owner = owner.userid " +
65 "where projectid = ?");
66 61
67 issue_summary = connection.prepareStatement( 62 issue_summary = connection.prepareStatement(
68 "select phase, count(*) as total "+ 63 "select phase, count(*) as total "+
69 "from lpit_issue " + 64 "from lpit_issue " +
70 "join lpit_issue_phases using(status) " + 65 "join lpit_issue_phases using(status) " +
71 "where project = ? "+ 66 "where project = ? "+
72 "group by phase " 67 "group by phase "
73 ); 68 );
74 69
75 insert = connection.prepareStatement( 70 insert = connection.prepareStatement(
76 "insert into lpit_project (name, description, repourl, owner) values (?, ?, ?, ?)" 71 "insert into lpit_project (name, node, description, repourl, owner) values (?, ?, ?, ?, ?)"
77 ); 72 );
78 update = connection.prepareStatement( 73 update = connection.prepareStatement(
79 "update lpit_project set name = ?, description = ?, repourl = ?, owner = ? where projectid = ?" 74 "update lpit_project set name = ?, node = ?, description = ?, repourl = ?, owner = ? where projectid = ?"
80 ); 75 );
81 } 76 }
82 77
83 public Project mapColumns(ResultSet result) throws SQLException { 78 private static Project mapColumns(ResultSet result) throws SQLException {
84 final var proj = new Project(result.getInt("projectid")); 79 final var proj = new Project(result.getInt("projectid"));
85 proj.setName(result.getString("name")); 80 proj.setName(result.getString("name"));
81 proj.setNode(result.getString("node"));
86 proj.setDescription(result.getString("description")); 82 proj.setDescription(result.getString("description"));
87 proj.setRepoUrl(result.getString("repourl")); 83 proj.setRepoUrl(result.getString("repourl"));
88 proj.setOwner(PGUserDao.mapColumns(result)); 84 proj.setOwner(PGUserDao.mapColumns(result));
89 85
90 return proj; 86 return proj;
110 } 106 }
111 } 107 }
112 return summary; 108 return summary;
113 } 109 }
114 110
111 private static int setColumns(PreparedStatement stmt, Project instance) throws SQLException {
112 int column = 0;
113 stmt.setString(++column, instance.getName());
114 stmt.setString(++column, instance.getNode());
115 setStringOrNull(stmt, ++column, instance.getDescription());
116 setStringOrNull(stmt, ++column, instance.getRepoUrl());
117 setForeignKeyOrNull(stmt, ++column, instance.getOwner(), User::getId);
118 return column;
119 }
120
115 @Override 121 @Override
116 public void save(Project instance) throws SQLException { 122 public void save(Project instance) throws SQLException {
117 Objects.requireNonNull(instance.getName()); 123 setColumns(insert, instance);
118 insert.setString(1, instance.getName());
119 setStringOrNull(insert, 2, instance.getDescription());
120 setStringOrNull(insert, 3, instance.getRepoUrl());
121 setForeignKeyOrNull(insert, 4, instance.getOwner(), User::getId);
122 insert.executeUpdate(); 124 insert.executeUpdate();
123 } 125 }
124 126
125 @Override 127 @Override
126 public boolean update(Project instance) throws SQLException { 128 public boolean update(Project instance) throws SQLException {
127 if (instance.getId() < 0) return false; 129 if (instance.getId() < 0) return false;
128 Objects.requireNonNull(instance.getName()); 130 int column = setColumns(update, instance);
129 update.setString(1, instance.getName()); 131 update.setInt(++column, instance.getId());
130 setStringOrNull(update, 2, instance.getDescription());
131 setStringOrNull(update, 3, instance.getRepoUrl());
132 setForeignKeyOrNull(update, 4, instance.getOwner(), User::getId);
133 update.setInt(5, instance.getId());
134 return update.executeUpdate() > 0; 132 return update.executeUpdate() > 0;
135 } 133 }
136 134
137 @Override 135 @Override
138 public List<Project> list() throws SQLException { 136 public List<Project> list() throws SQLException {
139 List<Project> projects = new ArrayList<>(); 137 return Functions.list(list, PGProjectDao::mapColumns);
140 try (var result = list.executeQuery()) {
141 while (result.next()) {
142 final var project = mapColumns(result);
143 projects.add(project);
144 }
145 }
146 return projects;
147 } 138 }
148 139
149 @Override 140 @Override
150 public Project find(int id) throws SQLException { 141 public Project find(int id) throws SQLException {
151 find.setInt(1, id); 142 find.setInt(1, id);
152 try (var result = find.executeQuery()) { 143 return Functions.find(find, PGProjectDao::mapColumns);
153 if (result.next()) { 144 }
154 final var project = mapColumns(result); 145
155 return project; 146 @Override
156 } else { 147 public Project findByNode(String node) throws SQLException {
157 return null; 148 findByNode.setString(1, node);
158 } 149 return Functions.find(findByNode, PGProjectDao::mapColumns);
159 }
160 } 150 }
161 } 151 }

Mercurial Repository: LightPIT

mercurial