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 } |