3 * Copyright © 2013 František Kučera (frantovo.cz)
5 * This program is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation, either version 3 of the License, or
8 * (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License
16 * along with this program. If not, see <http://www.gnu.org/licenses/>.
18 package info.globalcode.sql.dk;
20 import static info.globalcode.sql.dk.jmx.ConnectionManagement.incrementCounter;
21 import static info.globalcode.sql.dk.jmx.ConnectionManagement.resetCounter;
22 import info.globalcode.sql.dk.batch.Batch;
23 import info.globalcode.sql.dk.batch.BatchException;
24 import info.globalcode.sql.dk.configuration.DatabaseDefinition;
25 import info.globalcode.sql.dk.configuration.Properties;
26 import info.globalcode.sql.dk.configuration.Property;
27 import info.globalcode.sql.dk.formatting.ColumnsHeader;
28 import info.globalcode.sql.dk.formatting.Formatter;
29 import info.globalcode.sql.dk.jmx.ConnectionManagement;
30 import info.globalcode.sql.dk.jmx.ConnectionManagement.COUNTER;
31 import java.sql.Connection;
32 import java.sql.DriverManager;
33 import java.sql.PreparedStatement;
34 import java.sql.ResultSet;
35 import java.sql.SQLException;
36 import java.sql.SQLWarning;
37 import java.util.logging.Level;
38 import java.util.logging.Logger;
41 * Represents connected database. Is derived from {@linkplain DatabaseDefinition}.
42 * Wraps {@linkplain Connection}.
44 * Is responsible for executing {@linkplain SQLCommand} and passing results to the
45 * {@linkplain Formatter}.
47 * @author Ing. František Kučera (frantovo.cz)
49 public class DatabaseConnection implements AutoCloseable {
51 private static final Logger log = Logger.getLogger(DatabaseConnection.class.getName());
52 private static final String JDBC_PROPERTY_USER = "user";
53 public static final String JDBC_PROPERTY_PASSWORD = "password";
54 private final DatabaseDefinition databaseDefinition;
55 private final Connection connection;
56 private final Properties properties;
58 * Could be null = JMX is disabled → must check, see functions in
59 * {@linkplain ConnectionManagement}
61 private final ConnectionManagement connectionMBean;
65 * @param databaseDefinition DB url, name, password etc.
66 * @param properties additional properties from CLI
67 * @param connectionMBean JMX management bean | null = disabled JMX reporting
68 * @throws SQLException
70 public DatabaseConnection(DatabaseDefinition databaseDefinition, Properties properties, ConnectionManagement connectionMBean) throws SQLException {
71 this.databaseDefinition = databaseDefinition;
72 this.properties = properties;
73 this.connectionMBean = connectionMBean;
75 if (properties.hasProperty(JDBC_PROPERTY_PASSWORD)) {
76 log.log(Level.WARNING, "Passing DB password as CLI parameter is insecure!");
79 Properties credentials = new Properties();
80 credentials.add(new Property(JDBC_PROPERTY_USER, databaseDefinition.getUserName()));
81 credentials.add(new Property(JDBC_PROPERTY_PASSWORD, databaseDefinition.getPassword()));
82 credentials.setDefaults(databaseDefinition.getProperties());
83 properties.setDefaults(credentials);
84 java.util.Properties javaProperties = properties.getJavaProperties();
86 connection = DriverManager.getConnection(databaseDefinition.getUrl(), javaProperties);
89 public void executeQuery(SQLCommand sqlCommand, Formatter formatter) throws SQLException {
90 formatter.writeStartBatch();
91 formatter.writeStartDatabase(databaseDefinition);
92 formatter.writeStartStatement();
93 formatter.writeQuery(sqlCommand.getQuery());
94 formatter.writeParameters(sqlCommand.getParameters());
95 processCommand(sqlCommand, formatter);
96 formatter.writeEndStatement();
97 formatter.writeEndDatabase();
98 formatter.writeEndBatch();
101 public void executeBatch(Batch batch, Formatter formatter) throws SQLException, BatchException {
102 formatter.writeStartBatch();
103 formatter.writeStartDatabase(databaseDefinition);
104 while (batch.hasNext()) {
105 SQLCommand sqlCommand = batch.next();
106 formatter.writeStartStatement();
107 formatter.writeQuery(sqlCommand.getQuery());
108 formatter.writeParameters(sqlCommand.getParameters());
109 processCommand(sqlCommand, formatter);
110 formatter.writeEndStatement();
112 formatter.writeEndDatabase();
113 formatter.writeEndBatch();
116 private void processCommand(SQLCommand sqlCommand, Formatter formatter) throws SQLException {
117 incrementCounter(connectionMBean, COUNTER.COMMAND);
118 resetCounter(connectionMBean, COUNTER.RECORD_CURRENT);
120 try (PreparedStatement ps = sqlCommand.prepareStatement(connection)) {
121 log.log(Level.FINE, "Statement prepared");
122 sqlCommand.parametrize(ps);
124 boolean isRS = ps.execute();
125 log.log(Level.FINE, "Statement executed");
127 try (ResultSet rs = ps.getResultSet()) {
128 processResultSet(rs, formatter);
131 processUpdateResult(ps, formatter);
135 while (ps.getMoreResults() || ps.getUpdateCount() > -1) {
136 ResultSet rs = ps.getResultSet();
138 processUpdateResult(ps, formatter);
140 processResultSet(rs, formatter);
148 private void processUpdateResult(PreparedStatement ps, Formatter formatter) throws SQLException {
149 formatter.writeUpdatesResult(ps.getUpdateCount());
152 private void processResultSet(ResultSet rs, Formatter formatter) throws SQLException {
153 formatter.writeStartResultSet(new ColumnsHeader(rs.getMetaData()));
155 int columnCount = rs.getMetaData().getColumnCount();
158 incrementCounter(connectionMBean, COUNTER.RECORD_CURRENT);
159 incrementCounter(connectionMBean, COUNTER.RECORD_TOTAL);
161 formatter.writeStartRow();
163 for (int i = 1; i <= columnCount; i++) {
164 formatter.writeColumnValue(rs.getObject(i));
167 formatter.writeEndRow();
170 formatter.writeEndResultSet();
173 private void logWarnings(PreparedStatement ps) throws SQLException {
174 SQLWarning w = ps.getWarnings();
176 log.log(Level.WARNING, "SQL: {0}", w.getLocalizedMessage());
177 w = w.getNextWarning();
183 * Tests if this connection is live.
185 * @return true if test was successful
186 * @throws SQLException if test fails
188 public boolean test() throws SQLException {
189 connection.getAutoCommit();
194 public void close() throws SQLException {