1 package uk.ac.ebi.intenz.mapper;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Types;
8 import java.util.ArrayList;
9 import java.util.List;
10
11 import org.apache.log4j.Logger;
12
13 import uk.ac.ebi.intenz.domain.constants.EnzymeNameQualifierConstant;
14 import uk.ac.ebi.intenz.domain.constants.EnzymeNameTypeConstant;
15 import uk.ac.ebi.intenz.domain.constants.EnzymeSourceConstant;
16 import uk.ac.ebi.intenz.domain.constants.EnzymeViewConstant;
17 import uk.ac.ebi.intenz.domain.constants.Status;
18 import uk.ac.ebi.intenz.domain.enzyme.EnzymeName;
19
20
21
22
23
24
25
26 public class EnzymeNameMapper {
27
28 private static final Logger LOGGER =
29 Logger.getLogger(EnzymeNameMapper.class.getName());
30
31 private static final String COLUMNS = "enzyme_id, name, name_class, warning, status, source, note, order_in, web_view";
32
33 public EnzymeNameMapper() {
34 }
35
36 private String exportSibNamesStatement() {
37 return "SELECT " + COLUMNS +
38 " FROM names WHERE enzyme_id = ?" +
39 " AND (web_view = ? OR web_view = ? OR web_view = ? OR web_view = ?)" +
40 " FOR UPDATE ORDER BY UPPER(name)";
41 }
42
43 private String findStatement() {
44 return "SELECT " + COLUMNS +
45 " FROM names" +
46 " WHERE enzyme_id = ?" +
47 " ORDER BY order_in";
48 }
49
50 private String findByClassStatement() {
51 return "SELECT " + COLUMNS +
52 " FROM names" +
53 " WHERE enzyme_id = ? AND name_class = ?" +
54 " ORDER BY order_in";
55 }
56
57 private String insertStatement() {
58 return "INSERT INTO names (enzyme_id, name, name_class, warning, status, source, note, order_in, web_view) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
59 }
60
61 private String updateNameStatement() {
62 return "UPDATE names SET name = ?, warning = ?, status = ?, source = ?, web_view = ? " +
63 "WHERE enzyme_id = ? AND name_class = ?";
64 }
65
66 private String deleteNamesStatement() {
67 return "DELETE names WHERE enzyme_id = ? AND name_class = ?";
68 }
69
70
71
72
73
74
75
76
77
78 public List<EnzymeName> find(Long enzymeId, Connection con) throws SQLException {
79 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
80 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
81
82 PreparedStatement findStatement = null;
83 ResultSet rs = null;
84 List<EnzymeName> result = new ArrayList<EnzymeName>();
85 boolean noResult = true;
86
87 try {
88
89 findStatement = con.prepareStatement(findStatement());
90 findStatement.setLong(1, enzymeId.longValue());
91 rs = findStatement.executeQuery();
92 while (rs.next()) {
93 EnzymeName name = doLoad(rs);
94 if (name != null) {
95 noResult = false;
96 result.add(name);
97 }
98 }
99 } finally {
100 if (rs != null) rs.close();
101 if (findStatement != null) findStatement.close();
102 }
103
104 if (noResult) return null;
105 return result;
106 }
107
108
109
110
111
112
113
114
115
116
117
118
119 public List<EnzymeName> exportSibNames(Long enzymeId, Connection con) throws SQLException {
120 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
121 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
122
123 PreparedStatement findStatement = null;
124 ResultSet rs = null;
125 List<EnzymeName> result = new ArrayList<EnzymeName>();
126 boolean noResult = true;
127
128 try {
129
130 findStatement = con.prepareStatement(exportSibNamesStatement());
131 findStatement.setLong(1, enzymeId.longValue());
132 findStatement.setString(2, EnzymeViewConstant.INTENZ.toString());
133 findStatement.setString(3, EnzymeViewConstant.IUBMB_SIB.toString());
134 findStatement.setString(4, EnzymeViewConstant.SIB.toString());
135 findStatement.setString(5, EnzymeViewConstant.SIB_INTENZ.toString());
136 rs = findStatement.executeQuery();
137 while (rs.next()) {
138 EnzymeName name = doLoad(rs);
139 if (name != null) {
140 noResult = false;
141 result.add(name);
142 }
143 }
144 } finally {
145 if (rs != null) rs.close();
146 if (findStatement != null) findStatement.close();
147 }
148
149 if (noResult) return null;
150 return result;
151 }
152
153
154
155
156
157
158
159
160
161
162
163 public List<EnzymeName> findCommonNames(Long enzymeId, Connection con) throws SQLException {
164 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
165 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
166
167 PreparedStatement findStatement = null;
168 ResultSet rs = null;
169 boolean noResult = true;
170 List<EnzymeName> commonNames = new ArrayList<EnzymeName>();
171 EnzymeName result = null;
172
173 try {
174
175 findStatement = con.prepareStatement(findByClassStatement());
176 findStatement.setLong(1, enzymeId.longValue());
177 findStatement.setString(2, EnzymeNameTypeConstant.COMMON_NAME.toString());
178 rs = findStatement.executeQuery();
179 while (rs.next()) {
180 result = doLoad(rs);
181 if (result != null) {
182 noResult = false;
183 commonNames.add(result);
184 }
185 }
186 } finally {
187 if (rs != null) rs.close();
188 if (findStatement != null) findStatement.close();
189 }
190
191 if (noResult) return null;
192 return commonNames;
193 }
194
195
196
197
198
199
200
201
202
203 public EnzymeName findSystematicName(Long enzymeId, Connection con) throws SQLException {
204 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
205 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
206
207 PreparedStatement findStatement = null;
208 ResultSet rs = null;
209 EnzymeName result = null;
210 try {
211
212 findStatement = con.prepareStatement(findByClassStatement());
213 findStatement.setLong(1, enzymeId.longValue());
214 findStatement.setString(2, EnzymeNameTypeConstant.SYSTEMATIC_NAME.toString());
215 rs = findStatement.executeQuery();
216 if (rs.next()) {
217 result = doLoad(rs);
218 }
219 } finally {
220 if (rs != null) rs.close();
221 if (findStatement != null) findStatement.close();
222 }
223
224 return result;
225 }
226
227 public void update(EnzymeName name, Long enzymeId, Status status, int orderIn, Connection con)
228 throws SQLException {
229 if (name == null) throw new NullPointerException("Parameter 'name' must not be null.");
230 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
231 if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
232 if (orderIn < 1) throw new IllegalArgumentException("Parameter 'orderIn' must not be < 1.");
233 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
234
235 PreparedStatement updateNameStatement = null;
236 try {
237 updateNameStatement = con.prepareStatement(updateNameStatement());
238 doUpdate(name, enzymeId, status, orderIn, updateNameStatement);
239 updateNameStatement.execute();
240
241
242
243
244 } finally {
245 if (updateNameStatement != null) updateNameStatement.close();
246 }
247 }
248
249 public void insertNames(List<EnzymeName> names, Long enzymeId, Status status, Connection con)
250 throws SQLException {
251 if (names == null) throw new NullPointerException("Parameter 'names' must not be null.");
252 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
253 if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
254 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
255 for (int iii = 0; iii < names.size(); iii++) {
256 insert(names.get(iii), enzymeId, status, (iii+1), con);
257 }
258 }
259
260 public void insert(EnzymeName enzymeName, Long enzymeId, Status status, int orderIn, Connection con)
261 throws SQLException {
262 if (enzymeName == null) throw new NullPointerException("Parameter 'enzymeName' must not be null.");
263 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
264 if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
265 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
266
267 PreparedStatement insertStatement = null;
268 try {
269 insertStatement = con.prepareStatement(insertStatement());
270 doInsert(enzymeName, enzymeId, status, orderIn, insertStatement);
271 insertStatement.execute();
272
273
274
275
276 } finally {
277 if (insertStatement != null) insertStatement.close();
278 }
279 }
280
281 public void reload(List<EnzymeName> names, Long enzymeId, EnzymeNameTypeConstant type, Status status,
282 Connection con)
283 throws SQLException {
284 deleteNames(enzymeId, type, con);
285 insertNames(names, enzymeId, status, con);
286 }
287
288 public void deleteNames(Long enzymeId, EnzymeNameTypeConstant nameType, Connection con) throws SQLException {
289 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
290 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
291
292 PreparedStatement deleteNamesStatement = null;
293 try {
294 deleteNamesStatement = con.prepareStatement(deleteNamesStatement());
295 deleteNamesStatement.setLong(1, enzymeId.longValue());
296 deleteNamesStatement.setString(2, nameType.toString());
297 deleteNamesStatement.execute();
298
299
300
301
302 } finally {
303 if (deleteNamesStatement != null) deleteNamesStatement.close();
304 }
305 }
306
307
308
309
310
311
312
313
314
315
316
317
318 private EnzymeName doLoad(ResultSet rs) throws SQLException {
319 assert rs != null : "Parameter 'rs' must not be null.";
320
321 String name = "";
322 String nameClass = "";
323 String warning = "";
324 String source = "";
325 String webView = "";
326
327 if (rs.getString("name") != null) name = rs.getString("name");
328 if (rs.getString("name_class") != null) nameClass = rs.getString("name_class");
329 if (rs.getString("warning") != null) warning = rs.getString("warning");
330 if (rs.getString("source") != null) source = rs.getString("source");
331 if (rs.getString("web_view") != null) webView = rs.getString("web_view");
332
333 return EnzymeName.valueOf(name, EnzymeNameTypeConstant.valueOf(nameClass.toUpperCase()),
334 EnzymeNameQualifierConstant.valueOf(warning.toUpperCase()),
335 EnzymeSourceConstant.valueOf(source), EnzymeViewConstant.valueOf(webView));
336 }
337
338
339
340
341
342
343
344
345
346
347 private void doInsert(EnzymeName name, Long enzymeId, Status status, int orderIn,
348 PreparedStatement insertStatement) throws SQLException {
349 assert name != null : "Parameter 'name' must not be null.";
350 assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
351 assert status != null : "Parameter 'status' must not be null.";
352 assert orderIn > 0 : "Parameter 'orderIn' must not be < 1.";
353 assert insertStatement != null : "Parameter 'insertStatement' must not be null.";
354
355 insertStatement.setLong(1, enzymeId.longValue());
356 insertStatement.setString(2, name.getName());
357 insertStatement.setString(3, name.getType().toString());
358 if (name.getQualifier() == EnzymeNameQualifierConstant.UNDEF)
359 insertStatement.setNull(4, Types.VARCHAR);
360 else
361 insertStatement.setString(4, name.getQualifier().toString());
362 insertStatement.setString(5, status.getCode());
363 insertStatement.setString(6, name.getSource().toString());
364 insertStatement.setNull(7, Types.VARCHAR);
365 insertStatement.setInt(8, orderIn);
366 insertStatement.setString(9, name.getView().toString());
367 }
368
369 private void doUpdate(EnzymeName name, Long enzymeId, Status status, int orderIn,
370 PreparedStatement updateStatement) throws SQLException {
371 assert name != null : "Parameter 'name' must not be null.";
372 assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
373 assert status != null : "Parameter 'status' must not be null.";
374 assert orderIn > 0 : "Parameter 'orderIn' must not be < 1.";
375 assert updateStatement != null : "Parameter 'updateStatement' must not be null.";
376
377 updateStatement.setString(1, name.getName());
378 if (name.getQualifier() == EnzymeNameQualifierConstant.UNDEF)
379 updateStatement.setNull(2, Types.VARCHAR);
380 else
381 updateStatement.setString(2, name.getQualifier().toString());
382 updateStatement.setString(3, status.getCode());
383 updateStatement.setString(4, name.getSource().toString());
384 updateStatement.setString(5, name.getView().toString());
385 updateStatement.setLong(6, enzymeId.longValue());
386 updateStatement.setString(7, name.getType().toString());
387 }
388 }