Mercurial > dive4elements > river

annotate flys-aft/src/main/java/de/intevation/db/ConnectionBuilder.java @ 5312:2c1045a1e3fe

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added new states and transitions to UESK calculation and adjusted states and UI. TODO: Some refactoring and code cleanup.
author Raimund Renkert <rrenkert@intevation.de>
date Thu, 14 Mar 2013 17:25:00 +0100
parents d9f1202cef78
children
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.NodeList;
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 public class ConnectionBuilder
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24 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
25
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
26 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
27 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
28 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
29 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
30 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
31
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
32 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
33 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
34 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
35 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
36 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
37 protected List<String> loginStatements;
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39 public ConnectionBuilder(String type, Document document) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 this.type = type;
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41 extractCredentials(document);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43
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
44 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
45 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
46 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
47 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
48 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
49 }
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 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
51 }
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
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
53 protected void extractCredentials(Document document) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
54 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
55 map.put("type", type);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
56
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57 driver = (String)XML.xpath(
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58 document, XPATH_DRIVER, XPathConstants.STRING, null, map);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59 user = (String)XML.xpath(
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60 document, XPATH_USER, XPathConstants.STRING, null, map);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61 password = (String)XML.xpath(
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
62 document, XPATH_PASSWORD, XPathConstants.STRING, null, map);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63 url = (String)XML.xpath(
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
64 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
65 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
66 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
67
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68 if (log.isDebugEnabled()) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 log.debug("driver: " + driver);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
70 log.debug("user: " + user);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 log.debug("password: *******");
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
72 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
73 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
74 }
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 public Connection getConnection() throws SQLException {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
78
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
79 if (driver != null && driver.length() > 0) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
80 try {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
81 Class.forName(driver);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
82 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83 catch (ClassNotFoundException cnfe) {
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
84 throw new SQLException(cnfe);
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
85 }
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
4086
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
88 Connection connection =
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
89 DriverManager.getConnection(url, user, password);
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
90
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
91 connection.setAutoCommit(false);
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
92
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
93 DatabaseMetaData metaData = connection.getMetaData();
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
94
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
95 if (metaData.supportsTransactionIsolationLevel(
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
96 Connection.TRANSACTION_READ_UNCOMMITTED)) {
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
97 connection.setTransactionIsolation(
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
98 Connection.TRANSACTION_READ_UNCOMMITTED);
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
99 }
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
100
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
101 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
102 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
103 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
104 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
105 }
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 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
107 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
108 }
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
4086
ee0c60757a94 Added transaction support
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4074
diff changeset
111 return connection;
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
112 }
4073
2c70fae83d0c start syncing at river level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4072
diff changeset
113
4074
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
114 public ConnectedStatements getConnectedStatements() throws SQLException {
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
115 return new ConnectedStatements(
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
116 getConnection(),
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
117 new Statements(type, driver != null ? driver : "")
100c4e0a45e1 Improved infrastructure for prepared statements.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4073
diff changeset
118 .getStatements());
4073
2c70fae83d0c start syncing at river level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4072
diff changeset
119 }
4072
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
120 }
88f801888d85 load configuration an setup db connections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
121 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
http://dive4elements.wald.intevation.org