Mercurial > hg > LightPIT / file comparison

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

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

changeset 83
24a3596b8f98
parent 75
33b6843fdf8a
child 86
0a658e53177c
equal deleted inserted replaced
82:4ec7f2600c83 83:24a3596b8f98
41 41
42 import static de.uapcore.lightpit.dao.Functions.*; 42 import static de.uapcore.lightpit.dao.Functions.*;
43 43
44 public final class PGIssueDao implements IssueDao { 44 public final class PGIssueDao implements IssueDao {
45 45
46 private final PreparedStatement insert, update, list, find, affectedVersions, scheduledVersions, resolvedVersions; 46 private final PreparedStatement insert, update, list, find;
47 private final PreparedStatement affectedVersions, scheduledVersions, resolvedVersions;
48 private final PreparedStatement clearAffected, clearScheduled, clearResolved;
49 private final PreparedStatement insertAffected, insertScheduled, insertResolved;
47 50
48 public PGIssueDao(Connection connection) throws SQLException { 51 public PGIssueDao(Connection connection) throws SQLException {
49 list = connection.prepareStatement( 52 list = connection.prepareStatement(
50 "select issueid, project, status, category, subject, description, " + 53 "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
51 "userid, username, givenname, lastname, mail, " + 54 "userid, username, givenname, lastname, mail, " +
52 "created, updated, eta " + 55 "created, updated, eta " +
53 "from lpit_issue " + 56 "from lpit_issue i " +
57 "left join lpit_project p on project = projectid " +
54 "left join lpit_user on userid = assignee " + 58 "left join lpit_user on userid = assignee " +
55 "where project = ? "); 59 "where project = ? ");
56 60
57 find = connection.prepareStatement( 61 find = connection.prepareStatement(
58 "select issueid, project, status, category, subject, description, " + 62 "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
59 "userid, username, givenname, lastname, mail, " + 63 "userid, username, givenname, lastname, mail, " +
60 "created, updated, eta " + 64 "created, updated, eta " +
61 "from lpit_issue " + 65 "from lpit_issue i " +
66 "left join lpit_project p on project = projectid " +
62 "left join lpit_user on userid = assignee " + 67 "left join lpit_user on userid = assignee " +
63 "where issueid = ? "); 68 "where issueid = ? ");
64 69
65 insert = connection.prepareStatement( 70 insert = connection.prepareStatement(
66 "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " + 71 "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " +
70 "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " + 75 "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " +
71 "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?" 76 "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
72 ); 77 );
73 78
74 affectedVersions = connection.prepareStatement( 79 affectedVersions = connection.prepareStatement(
75 "select v.versionid, v.name, v.status, v.ordinal " + 80 "select versionid, name, status, ordinal " +
76 "from lpit_version v join lpit_issue_affected_version using (versionid) " + 81 "from lpit_version join lpit_issue_affected_version using (versionid) " +
77 "where issueid = ? " + 82 "where issueid = ? " +
78 "order by v.ordinal, v.name" 83 "order by ordinal, name"
79 ); 84 );
85 clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?");
86 insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)");
80 87
81 scheduledVersions = connection.prepareStatement( 88 scheduledVersions = connection.prepareStatement(
82 "select v.versionid, v.name, v.status, v.ordinal " + 89 "select versionid, name, status, ordinal " +
83 "from lpit_version v join lpit_issue_scheduled_version using (versionid) " + 90 "from lpit_version join lpit_issue_scheduled_version using (versionid) " +
84 "where issueid = ? " + 91 "where issueid = ? " +
85 "order by v.ordinal, v.name" 92 "order by ordinal, name"
86 ); 93 );
94 clearScheduled = connection.prepareStatement("delete from lpit_issue_scheduled_version where issueid = ?");
95 insertScheduled = connection.prepareStatement("insert into lpit_issue_scheduled_version (issueid, versionid) values (?,?)");
87 96
88 resolvedVersions = connection.prepareStatement( 97 resolvedVersions = connection.prepareStatement(
89 "select v.versionid, v.name, v.status, v.ordinal " + 98 "select versionid, name, status, ordinal " +
90 "from lpit_version v join lpit_issue_resolved_version using (versionid) " + 99 "from lpit_version v join lpit_issue_resolved_version using (versionid) " +
91 "where issueid = ? " + 100 "where issueid = ? " +
92 "order by v.ordinal, v.name" 101 "order by ordinal, name"
93 ); 102 );
103 clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?");
104 insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)");
94 } 105 }
95 106
96 private User obtainAssignee(ResultSet result) throws SQLException { 107 private User obtainAssignee(ResultSet result) throws SQLException {
97 final int id = result.getInt("userid"); 108 final int id = result.getInt("userid");
98 if (id != 0) { 109 if (id != 0) {
107 } 118 }
108 } 119 }
109 120
110 private Issue mapColumns(ResultSet result) throws SQLException { 121 private Issue mapColumns(ResultSet result) throws SQLException {
111 final var project = new Project(result.getInt("project")); 122 final var project = new Project(result.getInt("project"));
123 project.setName(result.getString("projectname"));
112 final var issue = new Issue(result.getInt("issueid"), project); 124 final var issue = new Issue(result.getInt("issueid"), project);
113 issue.setStatus(IssueStatus.valueOf(result.getString("status"))); 125 issue.setStatus(IssueStatus.valueOf(result.getString("status")));
114 issue.setCategory(IssueCategory.valueOf(result.getString("category"))); 126 issue.setCategory(IssueCategory.valueOf(result.getString("category")));
115 issue.setSubject(result.getString("subject")); 127 issue.setSubject(result.getString("subject"));
116 issue.setDescription(result.getString("description")); 128 issue.setDescription(result.getString("description"));
120 issue.setEta(result.getDate("eta")); 132 issue.setEta(result.getDate("eta"));
121 return issue; 133 return issue;
122 } 134 }
123 135
124 private Version mapVersion(ResultSet result, Project project) throws SQLException { 136 private Version mapVersion(ResultSet result, Project project) throws SQLException {
125 final var version = new Version(result.getInt("v.versionid"), project); 137 final var version = new Version(result.getInt("versionid"), project);
126 version.setName(result.getString("v.name")); 138 version.setName(result.getString("name"));
127 version.setOrdinal(result.getInt("v.ordinal")); 139 version.setOrdinal(result.getInt("ordinal"));
128 version.setStatus(VersionStatus.valueOf(result.getString("v.status"))); 140 version.setStatus(VersionStatus.valueOf(result.getString("status")));
129 return version; 141 return version;
142 }
143
144 private void updateVersionLists(Issue instance) throws SQLException {
145 clearAffected.setInt(1, instance.getId());
146 clearScheduled.setInt(1, instance.getId());
147 clearResolved.setInt(1, instance.getId());
148 insertAffected.setInt(1, instance.getId());
149 insertScheduled.setInt(1, instance.getId());
150 insertResolved.setInt(1, instance.getId());
151 clearAffected.executeUpdate();
152 clearScheduled.executeUpdate();
153 clearResolved.executeUpdate();
154 for (Version v : instance.getAffectedVersions()) {
155 insertAffected.setInt(2, v.getId());
156 insertAffected.executeUpdate();
157 }
158 for (Version v : instance.getScheduledVersions()) {
159 insertScheduled.setInt(2, v.getId());
160 insertScheduled.executeUpdate();
161 }
162 for (Version v : instance.getResolvedVersions()) {
163 insertResolved.setInt(2, v.getId());
164 insertResolved.executeUpdate();
165 }
130 } 166 }
131 167
132 @Override 168 @Override
133 public void save(Issue instance) throws SQLException { 169 public void save(Issue instance) throws SQLException {
134 Objects.requireNonNull(instance.getSubject()); 170 Objects.requireNonNull(instance.getSubject());
142 setDateOrNull(insert, 7, instance.getEta()); 178 setDateOrNull(insert, 7, instance.getEta());
143 // insert and retrieve the ID 179 // insert and retrieve the ID
144 final var rs = insert.executeQuery(); 180 final var rs = insert.executeQuery();
145 rs.next(); 181 rs.next();
146 instance.setId(rs.getInt(1)); 182 instance.setId(rs.getInt(1));
183 updateVersionLists(instance);
147 } 184 }
148 185
149 @Override 186 @Override
150 public boolean update(Issue instance) throws SQLException { 187 public boolean update(Issue instance) throws SQLException {
151 if (instance.getId() < 0) return false; 188 if (instance.getId() < 0) return false;
155 update.setString(3, instance.getSubject()); 192 update.setString(3, instance.getSubject());
156 setStringOrNull(update, 4, instance.getDescription()); 193 setStringOrNull(update, 4, instance.getDescription());
157 setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId); 194 setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId);
158 setDateOrNull(update, 6, instance.getEta()); 195 setDateOrNull(update, 6, instance.getEta());
159 update.setInt(7, instance.getId()); 196 update.setInt(7, instance.getId());
160 return update.executeUpdate() > 0; 197 boolean success = update.executeUpdate() > 0;
198 if (success) {
199 updateVersionLists(instance);
200 return true;
201 } else {
202 return false;
203 }
161 } 204 }
162 205
163 @Override 206 @Override
164 public List<Issue> list(Project project) throws SQLException { 207 public List<Issue> list(Project project) throws SQLException {
165 list.setInt(1, project.getId()); 208 list.setInt(1, project.getId());

Mercurial Repository: LightPIT

mercurial