1.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGComponentDao.java Thu Oct 22 12:00:34 2020 +0200
1.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGComponentDao.java Thu Oct 22 13:03:26 2020 +0200
1.3 @@ -39,42 +39,38 @@
1.4 import java.sql.PreparedStatement;
1.5 import java.sql.ResultSet;
1.6 import java.sql.SQLException;
1.7 -import java.util.ArrayList;
1.8 import java.util.List;
1.9 -import java.util.Objects;
1.10
1.11 public final class PGComponentDao implements ComponentDao {
1.12
1.13 - private final PreparedStatement insert, update, list, find;
1.14 + private final PreparedStatement insert, update, list, find, findByNode;
1.15
1.16 public PGComponentDao(Connection connection) throws SQLException {
1.17 - list = connection.prepareStatement(
1.18 - "select id, name, color, ordinal, description, " +
1.19 - "userid, username, givenname, lastname, mail " +
1.20 - "from lpit_component " +
1.21 - "left join lpit_user on lead = userid " +
1.22 - "where project = ? " +
1.23 + final var query = "select id, name, node, color, ordinal, description, " +
1.24 + "userid, username, givenname, lastname, mail " +
1.25 + "from lpit_component " +
1.26 + "left join lpit_user on lead = userid";
1.27 +
1.28 + list = connection.prepareStatement(query + " where project = ? " +
1.29 "order by ordinal desc, lower(name) desc");
1.30
1.31 - find = connection.prepareStatement(
1.32 - "select id, name, color, ordinal, description, " +
1.33 - "userid, username, givenname, lastname, mail " +
1.34 - "from lpit_component " +
1.35 - "left join lpit_user on lead = userid " +
1.36 - "where id = ? ");
1.37 + find = connection.prepareStatement(query + " where id = ? ");
1.38 +
1.39 + findByNode = connection.prepareStatement(query + " where project = ? and node = ?");
1.40
1.41 insert = connection.prepareStatement(
1.42 - "insert into lpit_component (project, name, color, ordinal, description, lead) values (?, ?, ?, ?, ?, ?)"
1.43 + "insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)"
1.44 );
1.45
1.46 update = connection.prepareStatement(
1.47 - "update lpit_component set name = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
1.48 + "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
1.49 );
1.50 }
1.51
1.52 private static Component mapColumns(ResultSet result) throws SQLException {
1.53 final var component = new Component(result.getInt("id"));
1.54 component.setName(result.getString("name"));
1.55 + component.setNode(result.getString("node"));
1.56 try {
1.57 component.setColor(new WebColor(result.getString("color")));
1.58 } catch (IllegalArgumentException ex) {
1.59 @@ -87,53 +83,49 @@
1.60 return component;
1.61 }
1.62
1.63 + private static int setColumns(PreparedStatement stmt, Component instance) throws SQLException {
1.64 + int column = 0;
1.65 + stmt.setString(++column, instance.getName());
1.66 + stmt.setString(++column, instance.getNode());
1.67 + stmt.setString(++column, instance.getColor().getHex());
1.68 + stmt.setInt(++column, instance.getOrdinal());
1.69 + Functions.setStringOrNull(stmt, ++column, instance.getDescription());
1.70 + Functions.setForeignKeyOrNull(stmt, ++column, instance.getLead(), User::getId);
1.71 + return column;
1.72 + }
1.73 +
1.74 @Override
1.75 public void save(Component instance, Project project) throws SQLException {
1.76 - Objects.requireNonNull(instance.getName());
1.77 - insert.setInt(1, project.getId());
1.78 - insert.setString(2, instance.getName());
1.79 - insert.setString(3, instance.getColor().getHex());
1.80 - insert.setInt(4, instance.getOrdinal());
1.81 - Functions.setStringOrNull(insert, 5, instance.getDescription());
1.82 - Functions.setForeignKeyOrNull(insert, 6, instance.getLead(), User::getId);
1.83 + int column = setColumns(insert, instance);
1.84 + insert.setInt(++column, project.getId());
1.85 insert.executeUpdate();
1.86 }
1.87
1.88 @Override
1.89 public boolean update(Component instance) throws SQLException {
1.90 if (instance.getId() < 0) return false;
1.91 - Objects.requireNonNull(instance.getName());
1.92 - Objects.requireNonNull(instance.getColor());
1.93 - update.setString(1, instance.getName());
1.94 - update.setString(2, instance.getColor().getHex());
1.95 - update.setInt(3, instance.getOrdinal());
1.96 - Functions.setStringOrNull(update, 4, instance.getDescription());
1.97 - Functions.setForeignKeyOrNull(update, 5, instance.getLead(), User::getId);
1.98 - update.setInt(6, instance.getId());
1.99 + int column = setColumns(update, instance);
1.100 + update.setInt(++column, instance.getId());
1.101 return update.executeUpdate() > 0;
1.102 }
1.103
1.104 +
1.105 @Override
1.106 public List<Component> list(Project project) throws SQLException {
1.107 list.setInt(1, project.getId());
1.108 - List<Component> components = new ArrayList<>();
1.109 - try (var result = list.executeQuery()) {
1.110 - while (result.next()) {
1.111 - components.add(mapColumns(result));
1.112 - }
1.113 - }
1.114 - return components;
1.115 + return Functions.list(list, PGComponentDao::mapColumns);
1.116 }
1.117
1.118 @Override
1.119 public Component find(int id) throws SQLException {
1.120 find.setInt(1, id);
1.121 - try (var result = find.executeQuery()) {
1.122 - if (result.next()) {
1.123 - return mapColumns(result);
1.124 - } else {
1.125 - return null;
1.126 - }
1.127 - }
1.128 + return Functions.find(find, PGComponentDao::mapColumns);
1.129 + }
1.130 +
1.131 + @Override
1.132 + public Component findByNode(Project project, String node) throws SQLException {
1.133 + findByNode.setInt(1, project.getId());
1.134 + findByNode.setString(2, node);;
1.135 + return Functions.find(findByNode, PGComponentDao::mapColumns);
1.136 }
1.137 }
