Mercurial > dive4elements > framework

annotate artifact-database/src/main/java/de/intevation/artifactdatabase/SQLExecutor.java @ 195:63f555bbdbc0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix a foreign key constraint violation when deleting outdated artifacts. artifacts/trunk@1425 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 08 Mar 2011 10:35:40 +0000
parents 25d472a67a9f
children
rev   line source
174
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 /*
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 * Copyright (c) 2011 by Intevation GmbH
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 *
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 * This program is free software under the LGPL (>=v2.1)
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 * Read the file LGPL.txt coming with the software for details
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 * or visit http://www.gnu.org/licenses/ if it does not exist.
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 */
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 package de.intevation.artifactdatabase;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 import java.sql.Connection;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 import java.sql.PreparedStatement;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 import java.sql.ResultSet;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 import java.sql.SQLException;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15 import javax.sql.DataSource;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 import org.apache.log4j.Logger;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 public class SQLExecutor {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21 private static Logger logger = Logger.getLogger(SQLExecutor.class);
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 public Connection conn;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24 public PreparedStatement stmnt;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 public ResultSet result;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27 public SQLExecutor() {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 public void reset() throws SQLException {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31 if (result != null) {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32 result.close();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 result = null;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
35 if (stmnt != null) {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36 result = null;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37 stmnt.close();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41 public PreparedStatement prepareStatement(String query)
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42 throws SQLException {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 return stmnt = conn.prepareStatement(query);
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
45
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46 public void close() {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
47 if (result != null) {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
48 try { result.close(); }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49 catch (SQLException sqle) {}
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51 if (stmnt != null) {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
52 try { stmnt.close(); }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
53 catch (SQLException sqle) {}
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
54 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
55 if (conn != null) {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
56 try { conn.close(); }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57 catch (SQLException sqle) {}
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61 public boolean runWrite() {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
62 DataSource dataSource = DBConnection.getDataSource();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63 try {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
64 conn = dataSource.getConnection();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
65 try {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
66 conn.setAutoCommit(false);
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
67 return doIt();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 catch (SQLException sqle) {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
70 conn.rollback();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 throw sqle;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
72 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
73 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
74 catch (SQLException sqle) {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
75 logger.error(sqle.getLocalizedMessage(), sqle);
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
76 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
77 finally {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
78 close();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
79 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
80 return false;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
81 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
82
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83 public boolean runRead() {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
84 DataSource dataSource = DBConnection.getDataSource();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
85 try {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
86 conn = dataSource.getConnection();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
87 return doIt();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
88 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
89 catch (SQLException sqle) {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
90 logger.error(sqle.getLocalizedMessage(), sqle);
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
91 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
92 finally {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
93 close();
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
94 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
95 return false;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
96 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
97
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
98 public boolean doIt() throws SQLException {
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
99 return true;
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
100 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
101 }
25d472a67a9f Reduce the code repetition and the complexity of the backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
102 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
http://dive4elements.wald.intevation.org