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 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.Loader;

    26 import info.globalcode.sql.dk.configuration.Properties;

    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.PreparedStatement;

    33 import java.sql.ResultSet;

    34 import java.sql.SQLException;

    35 import java.sql.SQLWarning;

    36 import java.util.logging.Level;

    37 import java.util.logging.Logger;

    38 

    39 /**

    40  * Represents connected database. Is derived from {@linkplain DatabaseDefinition}.

    41  * Wraps {@linkplain Connection}.

    42  *

    43  * Is responsible for executing {@linkplain SQLCommand} and passing results to the

    44  * {@linkplain Formatter}.

    45  *

    46  * @author Ing. František Kučera (frantovo.cz)

    47  */

    48 public class DatabaseConnection implements AutoCloseable {

    49 

    50 	private static final Logger log = Logger.getLogger(DatabaseConnection.class.getName());

    51 	public static final String JDBC_PROPERTY_USER = "user";

    52 	public static final String JDBC_PROPERTY_PASSWORD = "password";

    53 	private final DatabaseDefinition databaseDefinition;

    54 	private final Connection connection;

    55 	private final Properties properties;

    56 	/**

    57 	 * Could be null = JMX is disabled → must check, see functions in

    58 	 * {@linkplain ConnectionManagement}

    59 	 */

    60 	private final ConnectionManagement connectionMBean;

    61 

    62 	/**

    63 	 *

    64 	 * @param databaseDefinition DB url, name, password etc.

    65 	 * @param properties additional properties from CLI

    66 	 * @param connectionMBean JMX management bean | null = disabled JMX reporting

    67 	 * @throws SQLException

    68 	 */

    69 	public DatabaseConnection(DatabaseDefinition databaseDefinition, Properties properties, ConnectionManagement connectionMBean) throws SQLException {

    70 		this.databaseDefinition = databaseDefinition;

    71 		this.properties = properties;

    72 		this.connectionMBean = connectionMBean;

    73 		this.connection = Loader.jdbcConnect(databaseDefinition, properties);

    74 	}

    75 

    76 	public void executeQuery(SQLCommand sqlCommand, Formatter formatter) throws SQLException {

    77 		formatter.writeStartBatch();

    78 		formatter.writeStartDatabase(databaseDefinition);

    79 		formatter.writeStartStatement();

    80 		formatter.writeQuery(sqlCommand.getQuery());

    81 		formatter.writeParameters(sqlCommand.getParameters());

    82 		processCommand(sqlCommand, formatter);

    83 		formatter.writeEndStatement();

    84 		formatter.writeEndDatabase();

    85 		formatter.writeEndBatch();

    86 	}

    87 

    88 	public void executeBatch(Batch batch, Formatter formatter) throws SQLException, BatchException {

    89 		formatter.writeStartBatch();

    90 		formatter.writeStartDatabase(databaseDefinition);

    91 		while (batch.hasNext()) {

    92 			SQLCommand sqlCommand = batch.next();

    93 			formatter.writeStartStatement();

    94 			formatter.writeQuery(sqlCommand.getQuery());

    95 			formatter.writeParameters(sqlCommand.getParameters());

    96 			processCommand(sqlCommand, formatter);

    97 			formatter.writeEndStatement();

    98 		}

    99 		formatter.writeEndDatabase();

   100 		formatter.writeEndBatch();

   101 	}

   102 

   103 	private void processCommand(SQLCommand sqlCommand, Formatter formatter) throws SQLException {

   104 		incrementCounter(connectionMBean, COUNTER.COMMAND);

   105 		resetCounter(connectionMBean, COUNTER.RECORD_CURRENT);

   106 

   107 		try (PreparedStatement ps = sqlCommand.prepareStatement(connection)) {

   108 			log.log(Level.FINE, "Statement prepared");

   109 			sqlCommand.parametrize(ps);

   110 

   111 			boolean isRS = ps.execute();

   112 			log.log(Level.FINE, "Statement executed");

   113 			if (isRS) {

   114 				try (ResultSet rs = ps.getResultSet()) {

   115 					processResultSet(rs, formatter);

   116 				}

   117 			} else {

   118 				processUpdateResult(ps, formatter);

   119 			}

   120 			logWarnings(ps);

   121 

   122 			while (ps.getMoreResults() || ps.getUpdateCount() > -1) {

   123 				ResultSet rs = ps.getResultSet();

   124 				if (rs == null) {

   125 					processUpdateResult(ps, formatter);

   126 				} else {

   127 					processResultSet(rs, formatter);

   128 					rs.close();

   129 				}

   130 				logWarnings(ps);

   131 			}

   132 		}

   133 	}

   134 

   135 	private void processUpdateResult(PreparedStatement ps, Formatter formatter) throws SQLException {

   136 		formatter.writeUpdatesResult(ps.getUpdateCount());

   137 	}

   138 

   139 	private void processResultSet(ResultSet rs, Formatter formatter) throws SQLException {

   140 		formatter.writeStartResultSet(new ColumnsHeader(rs.getMetaData()));

   141 

   142 		int columnCount = rs.getMetaData().getColumnCount();

   143 

   144 		while (rs.next()) {

   145 			incrementCounter(connectionMBean, COUNTER.RECORD_CURRENT);

   146 			incrementCounter(connectionMBean, COUNTER.RECORD_TOTAL);

   147 

   148 			formatter.writeStartRow();

   149 

   150 			for (int i = 1; i <= columnCount; i++) {

   151 				formatter.writeColumnValue(rs.getObject(i));

   152 			}

   153 

   154 			formatter.writeEndRow();

   155 		}

   156 

   157 		formatter.writeEndResultSet();

   158 	}

   159 

   160 	private void logWarnings(PreparedStatement ps) throws SQLException {

   161 		SQLWarning w = ps.getWarnings();

   162 		while (w != null) {

   163 			log.log(Level.WARNING, "SQL: {0}", w.getLocalizedMessage());

   164 			w = w.getNextWarning();

   165 		}

   166 		ps.clearWarnings();

   167 	}

   168 

   169 	/**

   170 	 * Tests if this connection is live.

   171 	 *

   172 	 * @return true if test was successful

   173 	 * @throws SQLException if test fails

   174 	 */

   175 	public boolean test() throws SQLException {

   176 		connection.getAutoCommit();

   177 		return true;

   178 	}

   179 

   180 	public String getProductName() throws SQLException {

   181 		return connection.getMetaData().getDatabaseProductName();

   182 	}

   183 

   184 	public String getProductVersion() throws SQLException {

   185 		return connection.getMetaData().getDatabaseProductVersion();

   186 	}

   187 

   188 	@Override

   189 	public void close() throws SQLException {

   190 		connection.close();

   191 	}

   192 }