Mercurial > dive4elements > river

annotate flys-aft/src/main/java/de/intevation/db/ConnectionBuilder.java @ 4774:9dcc32fc5167

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved check for calculation range intersection to SQL statement.
author Sascha L. Teichmann <teichmann@intevation.de>
date Sat, 05 Jan 2013 13:46:27 +0100
parents f0b3ad10f67f
children d9f1202cef78
rev   line source
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 package de.intevation.db;
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 import de.intevation.utils.XML;
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4
4772
f939e1e6cfa4 Sorted imports.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4086
diff changeset
5 import java.sql.Connection;
f939e1e6cfa4 Sorted imports.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4086
diff changeset
6 import java.sql.DatabaseMetaData;
f939e1e6cfa4 Sorted imports.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4086
diff changeset
7 import java.sql.DriverManager;
f939e1e6cfa4 Sorted imports.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4086
diff changeset
8 import java.sql.SQLException;
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
9 import java.sql.Statement;
4772
f939e1e6cfa4 Sorted imports.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4086
diff changeset
10
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
11 import java.util.ArrayList;
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 import java.util.HashMap;
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
13 import java.util.List;
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15 import javax.xml.xpath.XPathConstants;
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16
4772
f939e1e6cfa4 Sorted imports.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4086
diff changeset
17 import org.apache.log4j.Logger;
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18
4772
f939e1e6cfa4 Sorted imports.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4086
diff changeset
19 import org.w3c.dom.Document;
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
20 import org.w3c.dom.Node;
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
21 import org.w3c.dom.NodeList;
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 public class ConnectionBuilder
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24 {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 private static Logger log = Logger.getLogger(ConnectionBuilder.class);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
27 public static final String XPATH_DRIVER = "/sync/side[@name=$type]/db/driver/text()";
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
28 public static final String XPATH_USER = "/sync/side[@name=$type]/db/user/text()";
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
29 public static final String XPATH_PASSWORD = "/sync/side[@name=$type]/db/password/text()";
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
30 public static final String XPATH_URL = "/sync/side[@name=$type]/db/url/text()";
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
31 public static final String XPATH_EXEC_LOGIN = "/sync/side[@name=$type]/db/execute-login/statement";
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
33 protected String type;
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
34 protected String driver;
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
35 protected String user;
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
36 protected String password;
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
37 protected String url;
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
38 protected List<String> loginStatements;
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 public ConnectionBuilder(String type, Document document) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41 this.type = type;
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42 extractCredentials(document);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
45 protected static List<String> extractStrings(NodeList nodes) {
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
46 int N = nodes.getLength();
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
47 List<String> result = new ArrayList<String>(N);
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
48 for (int i = 0; i < N; ++i) {
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
49 result.add(nodes.item(i).getTextContent());
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
50 }
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
51 return result;
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
52 }
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
53
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
54 protected void extractCredentials(Document document) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
55 HashMap<String, String> map = new HashMap<String, String>();
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
56 map.put("type", type);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58 driver = (String)XML.xpath(
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59 document, XPATH_DRIVER, XPathConstants.STRING, null, map);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60 user = (String)XML.xpath(
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61 document, XPATH_USER, XPathConstants.STRING, null, map);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
62 password = (String)XML.xpath(
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63 document, XPATH_PASSWORD, XPathConstants.STRING, null, map);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
64 url = (String)XML.xpath(
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
65 document, XPATH_URL, XPathConstants.STRING, null, map);
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
66 loginStatements = extractStrings((NodeList)XML.xpath(
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
67 document, XPATH_EXEC_LOGIN, XPathConstants.NODESET, null, map));
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 if (log.isDebugEnabled()) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
70 log.debug("driver: " + driver);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 log.debug("user: " + user);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
72 log.debug("password: *******");
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
73 log.debug("url: " + url);
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
74 log.debug("number of login statements: " + loginStatements.size());
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
75 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
76 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
77
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
78 public Connection getConnection() throws SQLException {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
79
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
80 if (driver != null && driver.length() > 0) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
81 try {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
82 Class.forName(driver);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
84 catch (ClassNotFoundException cnfe) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
85 throw new SQLException(cnfe);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
86 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
87 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
88
4086
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
89 Connection connection =
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
90 DriverManager.getConnection(url, user, password);
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
91
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
92 connection.setAutoCommit(false);
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
93
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
94 DatabaseMetaData metaData = connection.getMetaData();
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
95
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
96 if (metaData.supportsTransactionIsolationLevel(
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
97 Connection.TRANSACTION_READ_UNCOMMITTED)) {
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
98 connection.setTransactionIsolation(
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
99 Connection.TRANSACTION_READ_UNCOMMITTED);
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
100 }
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
101
4773
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
102 for (String sql: loginStatements) {
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
103 Statement stmnt = connection.createStatement();
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
104 try {
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
105 stmnt.execute(sql);
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
106 }
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
107 finally {
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
108 stmnt.close();
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
109 }
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
110 }
f0b3ad10f67f Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4772
diff changeset
111
4086
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
112 return connection;
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
113 }
4073
2c70fae83d0c start syncing at river level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4072
diff changeset
114
4074
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
115 public ConnectedStatements getConnectedStatements() throws SQLException {
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
116 return new ConnectedStatements(
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
117 getConnection(),
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
118 new Statements(type, driver != null ? driver : "")
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
119 .getStatements());
4073
2c70fae83d0c start syncing at river level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4072
diff changeset
120 }
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
121 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
122 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
http://dive4elements.wald.intevation.org