Mercurial > dive4elements > river

annotate flys-aft/src/main/java/de/intevation/db/ConnectionBuilder.java @ 4773:f0b3ad10f67f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added after login SQL statements for database connection. Can be used to alter the current schema to avoid naming clashes.
author Sascha L. Teichmann <teichmann@intevation.de>
date Sat, 05 Jan 2013 13:30:57 +0100
parents f939e1e6cfa4
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