JDBCConnection.java

  1. package gov.usgs.earthquake.util;

  2. import gov.usgs.util.Config;
  3. import gov.usgs.util.DefaultConfigurable;

  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.ResultSet;
  7. import java.sql.Statement;
  8. import java.util.logging.Level;
  9. import java.util.logging.Logger;

  10. /**
  11.  * Utility class for JDBC Connection.
  12.  *
  13.  * Sub-classes must implement the connect method, and extend startup and
  14.  * shutdown methods. The {@link #verifyConnection()} method tests whether the
  15.  * connection is active, and will shutdown() and startup() to reinitialize if it
  16.  * is not active.
  17.  *
  18.  * @author jmfee
  19.  */
  20. public class JDBCConnection extends DefaultConfigurable implements AutoCloseable {

  21.     private static final Logger LOGGER = Logger.getLogger(JDBCConnection.class
  22.             .getName());

  23.     /** Connection object. */
  24.     private Connection connection;

  25.     /** JDBC driver class. */
  26.     private String driver;

  27.     /** JDBC connect url. */
  28.     private String url;

  29.     /**
  30.      * Create a new JDBCConnection object.
  31.      */
  32.     public JDBCConnection() {
  33.         this.connection = null;
  34.     }

  35.     /**
  36.      * Create a new JDBCConnection object with specific driver and URL
  37.      * @param driver String of driver
  38.      * @param url String of URL
  39.      */
  40.     public JDBCConnection(final String driver, final String url) {
  41.         this.driver = driver;
  42.         this.url = url;
  43.     }

  44.     /**
  45.      * Implement autocloseable.
  46.      *
  47.      * Calls {@link #shutdown()}.
  48.      *
  49.      * @throws Exception Exception
  50.      */
  51.     @Override
  52.     public void close() throws Exception {
  53.         shutdown();
  54.     }

  55.     /**
  56.      * Implement Configurable
  57.      * @param config Config to set driver and URL in
  58.      * @throws Exception Exception
  59.      */
  60.     @Override
  61.     public void configure(final Config config) throws Exception {
  62.         setDriver(config.getProperty("driver"));
  63.         setUrl(config.getProperty("url"));
  64.     }

  65.     /**
  66.      * Connect to the database.
  67.      *
  68.      * Sub-classes determine how connection is made.
  69.      *
  70.      * @return the connection.
  71.      * @throws Exception
  72.      *             if unable to connect.
  73.      */
  74.     protected Connection connect() throws Exception {
  75.         // load driver if needed
  76.         Class.forName(driver);
  77.         final Connection conn = DriverManager.getConnection(url);
  78.         return conn;
  79.     }

  80.     /**
  81.      * Initialize the database connection.
  82.      *
  83.      * Sub-classes should call super.startup(), before preparing any statements.
  84.      * @throws Exception if error occurs
  85.      */
  86.     @Override
  87.     public void startup() throws Exception {
  88.         this.connection = connect();
  89.     }

  90.     /**
  91.      * Shutdown the database connection.
  92.      *
  93.      * Sub-classes should close any prepared statements (catching any
  94.      * exceptions), and then call super.shutdown() to close the database
  95.      * connection.
  96.      * @throws Exception if error occurs
  97.      */
  98.     @Override
  99.     public void shutdown() throws Exception {
  100.         try {
  101.             if (connection != null) {
  102.                 connection.close();
  103.             }
  104.         } catch (Exception e) {
  105.             // log
  106.             e.printStackTrace();
  107.         } finally {
  108.             connection = null;
  109.         }
  110.     }

  111.     /**
  112.      * Open a transaction on the database connection
  113.      * @throws Exception if error occurs
  114.      */
  115.     public synchronized void beginTransaction() throws Exception {
  116.         Connection conn = this.verifyConnection();
  117.         conn.setAutoCommit(false);
  118.     }

  119.     /**
  120.      * Finalize the transaction by committing all the changes and closing the
  121.      * transaction.
  122.      * @throws Exception if error occurs
  123.      */
  124.     public synchronized void commitTransaction() throws Exception {
  125.         getConnection().setAutoCommit(true);
  126.     }

  127.     /**
  128.      * Undo all of the changes made during the current transaction
  129.      * @throws Exception if error occurs
  130.      */
  131.     public synchronized void rollbackTransaction() throws Exception {
  132.         getConnection().rollback();
  133.     }

  134.     /**
  135.      * @return current connection object, or null if not connected.
  136.      */
  137.     public Connection getConnection() {
  138.         return this.connection;
  139.     }

  140.     /**
  141.      * Check whether database connection is closed, and reconnect if needed.
  142.      *
  143.      * Executes the query "select 1" using the current database connection. If
  144.      * this doesn't succeed, reinitializes the database connection by calling
  145.      * shutdown() then startup().
  146.      *
  147.      * @return Valid connection object.
  148.      * @throws Exception
  149.      *             if unable to (re)connect.
  150.      */
  151.     public synchronized Connection verifyConnection() throws Exception {
  152.         try {
  153.             // usually throws an exception when connection is closed
  154.             if (connection.isClosed()) {
  155.                 shutdown();
  156.             }
  157.         } catch (Exception e) {
  158.             shutdown();
  159.         }

  160.         if (connection == null) {
  161.             // connection is null after shutdown()
  162.             startup();
  163.         }

  164.         // isClosed() doesn't check if we can still communicate with the server.
  165.         // current mysql driver doesn't implement isValid(), so check manually.
  166.         String query_text = "SELECT 1";

  167.         try {
  168.             Statement statement = null;
  169.             ResultSet results = null;
  170.             try {
  171.                 statement = connection.createStatement();
  172.                 results = statement.executeQuery(query_text);
  173.                 while (results.next()) {
  174.                     if (results.getInt(1) != 1) {
  175.                         throw new Exception("[" + getName()
  176.                                 + "] Problem checking database connection");
  177.                     }
  178.                 }
  179.             } finally {
  180.                 // close result and statement no matter what
  181.                 try {
  182.                     results.close();
  183.                 } catch (Exception e2) {
  184.                     // ignore
  185.                 }
  186.                 try {
  187.                     statement.close();
  188.                 } catch (Exception e2) {
  189.                     // ignore
  190.                 }
  191.             }
  192.         } catch (Exception e) {
  193.             // The connection was dead, so lets try to restart it
  194.             LOGGER.log(Level.FINE, "[" + getName()
  195.                     + "] Restarting database connection");
  196.             shutdown();
  197.             startup();
  198.         }

  199.         return this.connection;
  200.     }

  201.     /** @return driver */
  202.     public String getDriver() { return this.driver; }
  203.     /** @param driver Driver to set */
  204.     public void setDriver(final String driver) { this.driver = driver; }

  205.     /** @return URL */
  206.     public String getUrl() { return this.url; }
  207.     /** @param url URL to set */
  208.     public void setUrl(final String url) { this.url = url; }

  209. }