comparison: src/main/java/de/uapcore/lightpit/dao/postgres/PGComponentDao.java
src/main/java/de/uapcore/lightpit/dao/postgres/PGComponentDao.java
- changeset 138
- e2aa673dd473
- parent 128
- 947d0f6a6a83
- child 153
- e914fbf4decc
equal
deleted
inserted
replaced
| 137:a7e543ab0c5f | 138:e2aa673dd473 |
|---|---|
| 37 | 37 |
| 38 import java.sql.Connection; | 38 import java.sql.Connection; |
| 39 import java.sql.PreparedStatement; | 39 import java.sql.PreparedStatement; |
| 40 import java.sql.ResultSet; | 40 import java.sql.ResultSet; |
| 41 import java.sql.SQLException; | 41 import java.sql.SQLException; |
| 42 import java.util.ArrayList; | |
| 43 import java.util.List; | 42 import java.util.List; |
| 44 import java.util.Objects; | |
| 45 | 43 |
| 46 public final class PGComponentDao implements ComponentDao { | 44 public final class PGComponentDao implements ComponentDao { |
| 47 | 45 |
| 48 private final PreparedStatement insert, update, list, find; | 46 private final PreparedStatement insert, update, list, find, findByNode; |
| 49 | 47 |
| 50 public PGComponentDao(Connection connection) throws SQLException { | 48 public PGComponentDao(Connection connection) throws SQLException { |
| 51 list = connection.prepareStatement( | 49 final var query = "select id, name, node, color, ordinal, description, " + |
| 52 "select id, name, color, ordinal, description, " + | 50 "userid, username, givenname, lastname, mail " + |
| 53 "userid, username, givenname, lastname, mail " + | 51 "from lpit_component " + |
| 54 "from lpit_component " + | 52 "left join lpit_user on lead = userid"; |
| 55 "left join lpit_user on lead = userid " + | 53 |
| 56 "where project = ? " + | 54 list = connection.prepareStatement(query + " where project = ? " + |
| 57 "order by ordinal desc, lower(name) desc"); | 55 "order by ordinal desc, lower(name) desc"); |
| 58 | 56 |
| 59 find = connection.prepareStatement( | 57 find = connection.prepareStatement(query + " where id = ? "); |
| 60 "select id, name, color, ordinal, description, " + | 58 |
| 61 "userid, username, givenname, lastname, mail " + | 59 findByNode = connection.prepareStatement(query + " where project = ? and node = ?"); |
| 62 "from lpit_component " + | |
| 63 "left join lpit_user on lead = userid " + | |
| 64 "where id = ? "); | |
| 65 | 60 |
| 66 insert = connection.prepareStatement( | 61 insert = connection.prepareStatement( |
| 67 "insert into lpit_component (project, name, color, ordinal, description, lead) values (?, ?, ?, ?, ?, ?)" | 62 "insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)" |
| 68 ); | 63 ); |
| 69 | 64 |
| 70 update = connection.prepareStatement( | 65 update = connection.prepareStatement( |
| 71 "update lpit_component set name = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?" | 66 "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?" |
| 72 ); | 67 ); |
| 73 } | 68 } |
| 74 | 69 |
| 75 private static Component mapColumns(ResultSet result) throws SQLException { | 70 private static Component mapColumns(ResultSet result) throws SQLException { |
| 76 final var component = new Component(result.getInt("id")); | 71 final var component = new Component(result.getInt("id")); |
| 77 component.setName(result.getString("name")); | 72 component.setName(result.getString("name")); |
| 73 component.setNode(result.getString("node")); | |
| 78 try { | 74 try { |
| 79 component.setColor(new WebColor(result.getString("color"))); | 75 component.setColor(new WebColor(result.getString("color"))); |
| 80 } catch (IllegalArgumentException ex) { | 76 } catch (IllegalArgumentException ex) { |
| 81 // if someone tempered with the database we default the color to black | 77 // if someone tempered with the database we default the color to black |
| 82 component.setColor(new WebColor("000000")); | 78 component.setColor(new WebColor("000000")); |
| 85 component.setDescription(result.getString("description")); | 81 component.setDescription(result.getString("description")); |
| 86 component.setLead(PGUserDao.mapColumns(result)); | 82 component.setLead(PGUserDao.mapColumns(result)); |
| 87 return component; | 83 return component; |
| 88 } | 84 } |
| 89 | 85 |
| 86 private static int setColumns(PreparedStatement stmt, Component instance) throws SQLException { | |
| 87 int column = 0; | |
| 88 stmt.setString(++column, instance.getName()); | |
| 89 stmt.setString(++column, instance.getNode()); | |
| 90 stmt.setString(++column, instance.getColor().getHex()); | |
| 91 stmt.setInt(++column, instance.getOrdinal()); | |
| 92 Functions.setStringOrNull(stmt, ++column, instance.getDescription()); | |
| 93 Functions.setForeignKeyOrNull(stmt, ++column, instance.getLead(), User::getId); | |
| 94 return column; | |
| 95 } | |
| 96 | |
| 90 @Override | 97 @Override |
| 91 public void save(Component instance, Project project) throws SQLException { | 98 public void save(Component instance, Project project) throws SQLException { |
| 92 Objects.requireNonNull(instance.getName()); | 99 int column = setColumns(insert, instance); |
| 93 insert.setInt(1, project.getId()); | 100 insert.setInt(++column, project.getId()); |
| 94 insert.setString(2, instance.getName()); | |
| 95 insert.setString(3, instance.getColor().getHex()); | |
| 96 insert.setInt(4, instance.getOrdinal()); | |
| 97 Functions.setStringOrNull(insert, 5, instance.getDescription()); | |
| 98 Functions.setForeignKeyOrNull(insert, 6, instance.getLead(), User::getId); | |
| 99 insert.executeUpdate(); | 101 insert.executeUpdate(); |
| 100 } | 102 } |
| 101 | 103 |
| 102 @Override | 104 @Override |
| 103 public boolean update(Component instance) throws SQLException { | 105 public boolean update(Component instance) throws SQLException { |
| 104 if (instance.getId() < 0) return false; | 106 if (instance.getId() < 0) return false; |
| 105 Objects.requireNonNull(instance.getName()); | 107 int column = setColumns(update, instance); |
| 106 Objects.requireNonNull(instance.getColor()); | 108 update.setInt(++column, instance.getId()); |
| 107 update.setString(1, instance.getName()); | |
| 108 update.setString(2, instance.getColor().getHex()); | |
| 109 update.setInt(3, instance.getOrdinal()); | |
| 110 Functions.setStringOrNull(update, 4, instance.getDescription()); | |
| 111 Functions.setForeignKeyOrNull(update, 5, instance.getLead(), User::getId); | |
| 112 update.setInt(6, instance.getId()); | |
| 113 return update.executeUpdate() > 0; | 109 return update.executeUpdate() > 0; |
| 114 } | 110 } |
| 111 | |
| 115 | 112 |
| 116 @Override | 113 @Override |
| 117 public List<Component> list(Project project) throws SQLException { | 114 public List<Component> list(Project project) throws SQLException { |
| 118 list.setInt(1, project.getId()); | 115 list.setInt(1, project.getId()); |
| 119 List<Component> components = new ArrayList<>(); | 116 return Functions.list(list, PGComponentDao::mapColumns); |
| 120 try (var result = list.executeQuery()) { | |
| 121 while (result.next()) { | |
| 122 components.add(mapColumns(result)); | |
| 123 } | |
| 124 } | |
| 125 return components; | |
| 126 } | 117 } |
| 127 | 118 |
| 128 @Override | 119 @Override |
| 129 public Component find(int id) throws SQLException { | 120 public Component find(int id) throws SQLException { |
| 130 find.setInt(1, id); | 121 find.setInt(1, id); |
| 131 try (var result = find.executeQuery()) { | 122 return Functions.find(find, PGComponentDao::mapColumns); |
| 132 if (result.next()) { | 123 } |
| 133 return mapColumns(result); | 124 |
| 134 } else { | 125 @Override |
| 135 return null; | 126 public Component findByNode(Project project, String node) throws SQLException { |
| 136 } | 127 findByNode.setInt(1, project.getId()); |
| 137 } | 128 findByNode.setString(2, node);; |
| 129 return Functions.find(findByNode, PGComponentDao::mapColumns); | |
| 138 } | 130 } |
| 139 } | 131 } |
