JDBCConnection.java
- package gov.usgs.earthquake.util;
- import gov.usgs.util.Config;
- import gov.usgs.util.DefaultConfigurable;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.Statement;
- import java.util.logging.Level;
- import java.util.logging.Logger;
- /**
- * Utility class for JDBC Connection.
- *
- * Sub-classes must implement the connect method, and extend startup and
- * shutdown methods. The {@link #verifyConnection()} method tests whether the
- * connection is active, and will shutdown() and startup() to reinitialize if it
- * is not active.
- *
- * @author jmfee
- */
- public class JDBCConnection extends DefaultConfigurable implements AutoCloseable {
- private static final Logger LOGGER = Logger.getLogger(JDBCConnection.class
- .getName());
- /** Connection object. */
- private Connection connection;
- /** JDBC driver class. */
- private String driver;
- /** JDBC connect url. */
- private String url;
- /**
- * Create a new JDBCConnection object.
- */
- public JDBCConnection() {
- this.connection = null;
- }
- /**
- * Create a new JDBCConnection object with specific driver and URL
- * @param driver String of driver
- * @param url String of URL
- */
- public JDBCConnection(final String driver, final String url) {
- this.driver = driver;
- this.url = url;
- }
- /**
- * Implement autocloseable.
- *
- * Calls {@link #shutdown()}.
- *
- * @throws Exception Exception
- */
- @Override
- public void close() throws Exception {
- shutdown();
- }
- /**
- * Implement Configurable
- * @param config Config to set driver and URL in
- * @throws Exception Exception
- */
- @Override
- public void configure(final Config config) throws Exception {
- setDriver(config.getProperty("driver"));
- setUrl(config.getProperty("url"));
- }
- /**
- * Connect to the database.
- *
- * Sub-classes determine how connection is made.
- *
- * @return the connection.
- * @throws Exception
- * if unable to connect.
- */
- protected Connection connect() throws Exception {
- // load driver if needed
- Class.forName(driver);
- final Connection conn = DriverManager.getConnection(url);
- return conn;
- }
- /**
- * Initialize the database connection.
- *
- * Sub-classes should call super.startup(), before preparing any statements.
- * @throws Exception if error occurs
- */
- @Override
- public void startup() throws Exception {
- this.connection = connect();
- }
- /**
- * Shutdown the database connection.
- *
- * Sub-classes should close any prepared statements (catching any
- * exceptions), and then call super.shutdown() to close the database
- * connection.
- * @throws Exception if error occurs
- */
- @Override
- public void shutdown() throws Exception {
- try {
- if (connection != null) {
- connection.close();
- }
- } catch (Exception e) {
- // log
- e.printStackTrace();
- } finally {
- connection = null;
- }
- }
- /**
- * Open a transaction on the database connection
- * @throws Exception if error occurs
- */
- public synchronized void beginTransaction() throws Exception {
- Connection conn = this.verifyConnection();
- conn.setAutoCommit(false);
- }
- /**
- * Finalize the transaction by committing all the changes and closing the
- * transaction.
- * @throws Exception if error occurs
- */
- public synchronized void commitTransaction() throws Exception {
- getConnection().setAutoCommit(true);
- }
- /**
- * Undo all of the changes made during the current transaction
- * @throws Exception if error occurs
- */
- public synchronized void rollbackTransaction() throws Exception {
- getConnection().rollback();
- }
- /**
- * @return current connection object, or null if not connected.
- */
- public Connection getConnection() {
- return this.connection;
- }
- /**
- * Check whether database connection is closed, and reconnect if needed.
- *
- * Executes the query "select 1" using the current database connection. If
- * this doesn't succeed, reinitializes the database connection by calling
- * shutdown() then startup().
- *
- * @return Valid connection object.
- * @throws Exception
- * if unable to (re)connect.
- */
- public synchronized Connection verifyConnection() throws Exception {
- try {
- // usually throws an exception when connection is closed
- if (connection.isClosed()) {
- shutdown();
- }
- } catch (Exception e) {
- shutdown();
- }
- if (connection == null) {
- // connection is null after shutdown()
- startup();
- }
- // isClosed() doesn't check if we can still communicate with the server.
- // current mysql driver doesn't implement isValid(), so check manually.
- String query_text = "SELECT 1";
- try {
- Statement statement = null;
- ResultSet results = null;
- try {
- statement = connection.createStatement();
- results = statement.executeQuery(query_text);
- while (results.next()) {
- if (results.getInt(1) != 1) {
- throw new Exception("[" + getName()
- + "] Problem checking database connection");
- }
- }
- } finally {
- // close result and statement no matter what
- try {
- results.close();
- } catch (Exception e2) {
- // ignore
- }
- try {
- statement.close();
- } catch (Exception e2) {
- // ignore
- }
- }
- } catch (Exception e) {
- // The connection was dead, so lets try to restart it
- LOGGER.log(Level.FINE, "[" + getName()
- + "] Restarting database connection");
- shutdown();
- startup();
- }
- return this.connection;
- }
- /** @return driver */
- public String getDriver() { return this.driver; }
- /** @param driver Driver to set */
- public void setDriver(final String driver) { this.driver = driver; }
- /** @return URL */
- public String getUrl() { return this.url; }
- /** @param url URL to set */
- public void setUrl(final String url) { this.url = url; }
- }