java/sql-dk/src/info/globalcode/sql/dk/DatabaseConnection.java
author František Kučera <franta-hg@frantovo.cz>
Wed, 08 Jan 2014 19:18:52 +0100
branchv_0
changeset 146 4f4f515df807
parent 142 da1e38386d84
child 155 eb3676c6929b
permissions -rw-r--r--
BatchDecoder: basic decoder
     1 /**
     2  * SQL-DK
     3  * Copyright © 2013 František Kučera (frantovo.cz)
     4  *
     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.
     9  *
    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.
    14  *
    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/>.
    17  */
    18 package info.globalcode.sql.dk;
    19 
    20 import info.globalcode.sql.dk.batch.Batch;
    21 import info.globalcode.sql.dk.batch.BatchException;
    22 import info.globalcode.sql.dk.configuration.DatabaseDefinition;
    23 import info.globalcode.sql.dk.configuration.Properties;
    24 import info.globalcode.sql.dk.configuration.Property;
    25 import info.globalcode.sql.dk.formatting.ColumnsHeader;
    26 import info.globalcode.sql.dk.formatting.Formatter;
    27 import java.sql.Connection;
    28 import java.sql.DriverManager;
    29 import java.sql.PreparedStatement;
    30 import java.sql.ResultSet;
    31 import java.sql.SQLException;
    32 import java.sql.SQLWarning;
    33 import java.util.logging.Level;
    34 import java.util.logging.Logger;
    35 
    36 /**
    37  *
    38  * @author Ing. František Kučera (frantovo.cz)
    39  */
    40 public class DatabaseConnection implements AutoCloseable {
    41 
    42 	private static final Logger log = Logger.getLogger(DatabaseConnection.class.getName());
    43 	private static final String JDBC_PROPERTY_USER = "user";
    44 	public static final String JDBC_PROPERTY_PASSWORD = "password";
    45 	private DatabaseDefinition databaseDefinition;
    46 	private Connection connection;
    47 	private Properties properties;
    48 
    49 	public DatabaseConnection(DatabaseDefinition databaseDefinition, Properties properties) throws SQLException {
    50 		this.databaseDefinition = databaseDefinition;
    51 		this.properties = properties;
    52 
    53 		if (properties.hasProperty(JDBC_PROPERTY_PASSWORD)) {
    54 			log.log(Level.WARNING, "Passing DB password as CLI parameter is insecure!");
    55 		}
    56 
    57 		Properties credentials = new Properties();
    58 		credentials.add(new Property(JDBC_PROPERTY_USER, databaseDefinition.getUserName()));
    59 		credentials.add(new Property(JDBC_PROPERTY_PASSWORD, databaseDefinition.getPassword()));
    60 		credentials.setDefaults(databaseDefinition.getProperties());
    61 		properties.setDefaults(credentials);
    62 		java.util.Properties javaProperties = properties.getJavaProperties();
    63 
    64 		connection = DriverManager.getConnection(databaseDefinition.getUrl(), javaProperties);
    65 	}
    66 
    67 	public void executeQuery(SQLCommand sqlCommand, Formatter formatter) throws SQLException {
    68 		formatter.writeStartBatch();
    69 		formatter.writeStartDatabase(databaseDefinition);
    70 		formatter.writeStartStatement();
    71 		formatter.writeQuery(sqlCommand.getQuery());
    72 		formatter.writeParameters(sqlCommand.getParameters());
    73 		processCommand(sqlCommand, formatter);
    74 		formatter.writeEndStatement();
    75 		formatter.writeEndDatabase();
    76 		formatter.writeEndBatch();
    77 	}
    78 
    79 	public void executeBatch(Batch batch, Formatter formatter) throws SQLException, BatchException {
    80 		formatter.writeStartBatch();
    81 		formatter.writeStartDatabase(databaseDefinition);
    82 		while (batch.hasNext()) {
    83 			SQLCommand sqlCommand = batch.next();
    84 			formatter.writeStartStatement();
    85 			formatter.writeQuery(sqlCommand.getQuery());
    86 			formatter.writeParameters(sqlCommand.getParameters());
    87 			processCommand(sqlCommand, formatter);
    88 			formatter.writeEndStatement();
    89 		}
    90 		formatter.writeEndDatabase();
    91 		formatter.writeEndBatch();
    92 	}
    93 
    94 	private void processCommand(SQLCommand sqlCommand, Formatter formatter) throws SQLException {
    95 		try (PreparedStatement ps = sqlCommand.prepareStatement(connection)) {
    96 			log.log(Level.FINE, "Statement prepared");
    97 			sqlCommand.parametrize(ps);
    98 
    99 			boolean isRS = ps.execute();
   100 			log.log(Level.FINE, "Statement executed");
   101 			if (isRS) {
   102 				try (ResultSet rs = ps.getResultSet()) {
   103 					processResultSet(rs, formatter);
   104 				}
   105 			} else {
   106 				processUpdateResult(ps, formatter);
   107 			}
   108 			logWarnings(ps);
   109 
   110 			while (ps.getMoreResults() || ps.getUpdateCount() > -1) {
   111 				ResultSet rs = ps.getResultSet();
   112 				if (rs == null) {
   113 					processUpdateResult(ps, formatter);
   114 				} else {
   115 					processResultSet(rs, formatter);
   116 					rs.close();
   117 				}
   118 				logWarnings(ps);
   119 			}
   120 		}
   121 	}
   122 
   123 	private void processUpdateResult(PreparedStatement ps, Formatter formatter) throws SQLException {
   124 		formatter.writeUpdatesResult(ps.getUpdateCount());
   125 	}
   126 
   127 	private void processResultSet(ResultSet rs, Formatter formatter) throws SQLException {
   128 		formatter.writeStartResultSet(new ColumnsHeader(rs.getMetaData()));
   129 
   130 		int columnCount = rs.getMetaData().getColumnCount();
   131 
   132 		while (rs.next()) {
   133 			formatter.writeStartRow();
   134 
   135 			for (int i = 1; i <= columnCount; i++) {
   136 				formatter.writeColumnValue(rs.getObject(i));
   137 			}
   138 
   139 			formatter.writeEndRow();
   140 		}
   141 
   142 		formatter.writeEndResultSet();
   143 	}
   144 
   145 	private void logWarnings(PreparedStatement ps) throws SQLException {
   146 		SQLWarning w = ps.getWarnings();
   147 		while (w != null) {
   148 			log.log(Level.WARNING, "SQL: {0}", w.getLocalizedMessage());
   149 			w = w.getNextWarning();
   150 		}
   151 		ps.clearWarnings();
   152 	}
   153 
   154 	/**
   155 	 * Tests if this connection is live.
   156 	 *
   157 	 * @return true if test was successful
   158 	 * @throws SQLException if test fails
   159 	 */
   160 	public boolean test() throws SQLException {
   161 		connection.getAutoCommit();
   162 		return true;
   163 	}
   164 
   165 	@Override
   166 	public void close() throws SQLException {
   167 		connection.close();
   168 	}
   169 }