diff -r ef075cd7ce55 -r f47e82cd6077 src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat Oct 17 15:21:56 2020 +0200 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat Oct 17 19:56:50 2020 +0200 @@ -31,13 +31,11 @@ import de.uapcore.lightpit.dao.IssueDao; import de.uapcore.lightpit.entities.*; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; +import java.sql.*; import java.util.ArrayList; import java.util.List; import java.util.Objects; +import java.util.Optional; import static de.uapcore.lightpit.dao.Functions.*; @@ -51,43 +49,50 @@ public PGIssueDao(Connection connection) throws SQLException { list = connection.prepareStatement( - "select issueid, project, p.name as projectname, status, category, subject, i.description, " + + "select issueid, i.project, p.name as projectname, component, c.name as componentname, " + + "status, category, subject, i.description, " + "userid, username, givenname, lastname, mail, " + "created, updated, eta " + "from lpit_issue i " + - "join lpit_project p on project = projectid " + + "join lpit_project p on i.project = projectid " + + "left join lpit_component c on component = c.id " + "left join lpit_user on userid = assignee " + - "where project = ? "); + "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)"); listForVersion = connection.prepareStatement( "with issue_version as ( "+ "select issueid, versionid from lpit_issue_affected_version union "+ "select issueid, versionid from lpit_issue_resolved_version) "+ - "select issueid, project, p.name as projectname, status, category, subject, i.description, " + + "select issueid, i.project, p.name as projectname, component, c.name as componentname, " + + "status, category, subject, i.description, " + "userid, username, givenname, lastname, mail, " + "created, updated, eta " + "from lpit_issue i " + - "join lpit_project p on project = projectid " + + "join lpit_project p on i.project = projectid " + + "left join lpit_component c on component = c.id " + "left join issue_version using (issueid) "+ "left join lpit_user on userid = assignee " + - "where coalesce(versionid,-1) = ? " + "where coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)" ); find = connection.prepareStatement( - "select issueid, project, p.name as projectname, status, category, subject, i.description, " + + "select issueid, i.project, p.name as projectname, component, c.name as componentname, " + + "status, category, subject, i.description, " + "userid, username, givenname, lastname, mail, " + "created, updated, eta " + "from lpit_issue i " + - "left join lpit_project p on project = projectid " + + "join lpit_project p on i.project = projectid " + + "left join lpit_component c on component = c.id " + "left join lpit_user on userid = assignee " + "where issueid = ? "); insert = connection.prepareStatement( - "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " + + "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " + "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid" ); update = connection.prepareStatement( - "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " + + "update lpit_issue set " + + "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " + "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?" ); @@ -123,8 +128,15 @@ private Issue mapColumns(ResultSet result) throws SQLException { final var project = new Project(result.getInt("project")); project.setName(result.getString("projectname")); + var component = new Component(result.getInt("component")); + if (result.wasNull()) { + component = null; + } else { + component.setName(result.getString("componentname")); + } final var issue = new Issue(result.getInt("issueid")); issue.setProject(project); + issue.setComponent(component); issue.setStatus(IssueStatus.valueOf(result.getString("status"))); issue.setCategory(IssueCategory.valueOf(result.getString("category"))); issue.setSubject(result.getString("subject")); @@ -161,17 +173,24 @@ } } + private int setData(PreparedStatement stmt, int column, Issue instance) throws SQLException { + setForeignKeyOrNull(stmt, ++column, instance.getComponent(), Component::getId); + stmt.setString(++column, instance.getStatus().name()); + stmt.setString(++column, instance.getCategory().name()); + stmt.setString(++column, instance.getSubject()); + setStringOrNull(stmt, ++column, instance.getDescription()); + setForeignKeyOrNull(stmt, ++column, instance.getAssignee(), User::getId); + setDateOrNull(stmt, ++column, instance.getEta()); + return column; + } + @Override public void save(Issue instance, Project project) throws SQLException { Objects.requireNonNull(instance.getSubject()); instance.setProject(project); - insert.setInt(1, instance.getProject().getId()); - insert.setString(2, instance.getStatus().name()); - insert.setString(3, instance.getCategory().name()); - insert.setString(4, instance.getSubject()); - setStringOrNull(insert, 5, instance.getDescription()); - setForeignKeyOrNull(insert, 6, instance.getAssignee(), User::getId); - setDateOrNull(insert, 7, instance.getEta()); + int column = 0; + insert.setInt(++column, instance.getProject().getId()); + setData(insert, column, instance); // insert and retrieve the ID final var rs = insert.executeQuery(); rs.next(); @@ -183,13 +202,8 @@ public boolean update(Issue instance) throws SQLException { if (instance.getId() < 0) return false; Objects.requireNonNull(instance.getSubject()); - update.setString(1, instance.getStatus().name()); - update.setString(2, instance.getCategory().name()); - update.setString(3, instance.getSubject()); - setStringOrNull(update, 4, instance.getDescription()); - setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId); - setDateOrNull(update, 6, instance.getEta()); - update.setInt(7, instance.getId()); + int column = setData(update, 0, instance); + update.setInt(++column, instance.getId()); boolean success = update.executeUpdate() > 0; if (success) { updateVersionLists(instance); @@ -199,8 +213,7 @@ } } - private List list(PreparedStatement query, int arg) throws SQLException { - query.setInt(1, arg); + private List executeQuery(PreparedStatement query) throws SQLException { List issues = new ArrayList<>(); try (var result = query.executeQuery()) { while (result.next()) { @@ -212,12 +225,30 @@ @Override public List list(Project project) throws SQLException { - return list(list, project.getId()); + list.setInt(1, project.getId()); + list.setNull(2, Types.INTEGER); + return executeQuery(list); } @Override - public List list(Version version) throws SQLException { - return list(listForVersion, version == null ? -1 : version.getId()); + public List list(Project project, Component component, Version version) throws SQLException { + listForVersion.setInt(1, Optional.ofNullable(version).map(Version::getId).orElse(-1)); + listForVersion.setInt(2, Optional.ofNullable(component).map(Component::getId).orElse(-1)); + return executeQuery(listForVersion); + } + + @Override + public List list(Project project, Version version) throws SQLException { + listForVersion.setInt(1, Optional.ofNullable(version).map(Version::getId).orElse(-1)); + listForVersion.setNull(2, Types.INTEGER); + return executeQuery(listForVersion); + } + + @Override + public List list(Project project, Component component) throws SQLException { + list.setInt(1, project.getId()); + list.setInt(2, Optional.ofNullable(component).map(Component::getId).orElse(-1)); + return executeQuery(list); } @Override