Changeset 408 for branches/ds_pool/src/org/bridgedb/rdb/SimpleGdb.java

Timestamp:

06/11/10 19:05:19 (2 years ago)

Author:

bturner

Message:

Convert all db calls to follow canonical pattern of resource usage.

Files:

• branches/ds_pool/src/org/bridgedb/rdb/SimpleGdb.java (modified) (2 diffs)

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdb.java

@@ -18 +18 @@
 
 import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
-
+import java.sql.Statement;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
 import org.bridgedb.DataSource;
 import org.bridgedb.IDMapperException;
 
 /**
- * SimpleGdb is the main implementation of the Gdb interface,
- * for dealing with single SQL-based pgdb's.
- * It's responsible for creating and querying a single 
+ * SimpleGdb is the main implementation of the Gdb interface, for dealing with
+ * single SQL-based pgdb's. It's responsible for creating and querying a single
  * pgdb relational database through the JDBC interface.
  * <p>
- * It wraps SQL statements in methods, 
- * so the rest of the apps don't need to know the
- * details of the Database schema.
- * <p>
- * It delegates dealing with the differences between 
- * various RDBMS's (Derby, Hsqldb etc.)
- * to a DBConnector instance.
- * A correct DBConnector instance needs to be 
- * passed to the constructor of SimpleGdb. 
- * <p>
- * In the PathVisio GUI environment, use GdbManager
- * to create and connect one or two centralized Gdb's. 
- * This will also automatically
- * find the right DBConnector from the preferences.
- * <p>
- * In a head-less or test environment, you can bypass GdbManager
- * and use SimpleGdb directly 
- * to create or connect to one or more pgdb's of any type.
+ * It wraps SQL statements in methods, so the rest of the apps don't need to
+ * know the details of the Database schema.
+ * <p>
+ * It delegates dealing with the differences between various RDBMS's (Derby,
+ * Hsqldb etc.) to a DBConnector instance. A correct DBConnector instance needs
+ * to be passed to the constructor of SimpleGdb.
+ * <p>
+ * In the PathVisio GUI environment, use GdbManager to create and connect one or
+ * two centralized Gdb's. This will also automatically find the right
+ * DBConnector from the preferences.
+ * <p>
+ * In a head-less or test environment, you can bypass GdbManager and use
+ * SimpleGdb directly to create or connect to one or more pgdb's of any type.
  */
 public abstract class SimpleGdb extends IDMapperRdb
 {
     
+    private static final Log LOG = LogFactory.getLog(SimpleGdb.class);
+    
     private JdbcConnectionPool pool;
     
-    
-        private final String connectionString;
-        /**
-         * Create IDMapper based on an existing SQL connection.
-         * @param con Existing SQL Connection.
-         */
-        SimpleGdb(String dbName, String connectionString)
-        {
-                this.connectionString = connectionString;
-                this.dbName = dbName;
-                
-                try
-        {
-                    // there's something a bit weird that this class does not know
-                    // it's underlying driver...
-                    String driverClassName = null;
-            pool = new JdbcConnectionPool(dbName, driverClassName, connectionString);
+    private boolean connected;
+    
+    protected final String dbName;
+    
+    /**
+     * Create IDMapper based on an existing SQL connection.
+     * 
+     * @param data_
+     *            .con Existing SQL Connection.
+     */
+    SimpleGdb(String theDbName,
+              String connectionString)
+    {
+        
+        dbName = theDbName;
+        
+        try
+        {
+            // there's something a bit weird that this class does not know
+            // it's underlying driver...
+            String driverClassName = null;
+            pool = new JdbcConnectionPool(dbName,
+                                          driverClassName,
+                                          connectionString);
+            connected = true;
         }
         catch (IDMapperException e)
@@ -78 +83 @@
             throw new RuntimeException(e);
         }
-        }
-
-        private boolean singleConnection = true;
-        private boolean neverCloseConnection = true;
-        
-        /**
-         * helper class that handles the life cycle of a connection, query and resultset.
-         * <p>
-         * The sql for a query is passed in at construction time.
-         * Before each query, call init(). This will lead to lazy initialization of the
-         * connection and preparedstatement objects, if necessary. Set the query parameters
-         * using setString(int, String). Get the resultSet using 
-         * Do not close the resultset! This will be closed for you when you call cleanup().
-         * Always call cleanup() in a finally block.
-         * <p>
-         * The advantages of using QueryLifeCycle are:
-         * <ul>
-         * <li>guarantee to close preparedstatement, resultset and connection if necessary.
-         * <li>in case of connection pooling, preparedstatement and connection are kept together as long
-         *   as possible.
-         * <li>lazy initialization of prepared statement
-         * <li>always uses preparedstatement, so safe from SQL injection.
-         * </ul> 
-         * <p>
-         * This class is not static because it needs SimpleGdb.getConnection().
-         */
-        final class QueryLifeCycle
-        {
-                /**
-                 * Initialize with given SQL string, but don't create PreparedStatement yet.
-                 * Valid to call before database connection is created.
-                 * @param aSql SQL query
-                 */
-                public QueryLifeCycle(String aSql)
-                {
-                        sql = aSql;
-                }
-                
-                private Connection con = null;
-                private ResultSet rs = null;
-                private PreparedStatement pst = null;
-                private final String sql;
-                private boolean inited = false;
-
-                public static final int QUERY_TIMEOUT = 20; //seconds
-                public static final int NO_LIMIT = 0;
-                public static final int NO_TIMEOUT = 0;
-
-                public void init(int limit) throws SQLException
-                {
-                        init();
-                        pst.setQueryTimeout(QUERY_TIMEOUT);                     
-                        if(limit > NO_LIMIT) 
-                        {
-                                pst.setMaxRows(limit);
-                        }
-                }
-                
-                /**
-                 * Initialize connection and PreparedStatement lazily.
-                 * <p>
-                 * @throws SQLException when a PreparedStatement could not be created
-                 */
-                public void init() throws SQLException
-                {
-                        if (inited) throw new IllegalStateException("Must call cleanup() between two init() calls");
-                        try
-                        {
-                                if (con == null) con = getConnection();
-                                if (pst == null)
-                                {
-                                        pst = con.prepareStatement(sql);
-                                }
-                        }
-                        finally { inited = true; }
-                }
-                
-                public void setString (int index, String val) throws SQLException
-                {
-                        if (!inited) throw new IllegalStateException("Must call init() before setString()");
-                        pst.setString(index, val);
-                }
-                
-                public ResultSet executeQuery() throws SQLException
-                {
-                        if (!inited) throw new IllegalStateException("Must call init() before executeQuery()");
-                        rs = pst.executeQuery();
-                        return rs;
-                }
-
-                /** 
-                 * Clean up resultset. If keepConnection is false, preparedstatement
-                 * and connection are cached. If keepConnection is true, they are closed as well.
-                 * The later is useful when using connection pooling.
-                 * <p>
-                 * Always call this in a finally block! 
-                 * */
-                public void cleanup()
-                {
-                        if (!inited) throw new IllegalStateException("Must call init() before cleanup()");
-                        inited = false;
-                        if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
-                        if (neverCloseConnection) return;
-                        if (pst != null) try { pst.close(); } catch (SQLException ignore) {}
-                        pst = null;
-                        if (con != null) try { con.close(); } catch (SQLException ignore) {}
-                        con = null;
-                }
-        }
-
-        private Connection con = null;
-        
-        synchronized public Connection getConnection() throws SQLException
-        {
-            /*
-                // if singleConnection is true, each call to getConnection() will return the same object.
-                // if singleConnection is false, each call to getConneciton() will lead to a new connection object being created.
-                if (!singleConnection || con == null)
-                {
-                        con = DriverManager.getConnection(connectionString); 
-                        con.setReadOnly(true);
-                }
-                return con;
-                */
-            return pool.getConnection();
-        }
-        
-        /**
-         * The {@link Connection} to the Gene Database.
-         */
-        
-        //private Connection con = null;
-
-        /** {@inheritDoc} */
-        final public boolean isConnected() { 
-                //return con != null; 
-                return true;
-        }
-
-        protected final String dbName;
-        
-        /** {@inheritDoc} */
-        @Override final public String getDbName() { return dbName; }
-        
-        /** {@inheritDoc} */
-        final public void close() throws IDMapperException 
-        {
-//              try
-//              {
-//                      con.close();
-//              }
-//              catch (SQLException ex)
-//              {
-//                      throw new IDMapperException (ex);
-//              }
-//              con = null;
-        }
-        
-        public static final int NO_LIMIT = 0;
-        public static final int NO_TIMEOUT = 0;
-        public static final int QUERY_TIMEOUT = 5; //seconds
-
-        /**
-           @return number of rows in gene table.
-           @throws IDMapperException on failure
-         */
-        final public int getGeneCount() throws IDMapperException
-        {
-                int result = 0;
-                try
-                {
-                        ResultSet r = getConnection().createStatement().executeQuery("SELECT COUNT(*) FROM " + "datanode");
-                        r.next();
-                        result = r.getInt (1);
-                        r.close();
-                }
-                catch (SQLException e)
-                {
-                        throw new IDMapperException (e);
-                }
-                return result;
-        }
-
-        /**
-         * @param ds DataSource to count identifiers for.
-           @return number of identifiers table for the given datasource
-           @throws IDMapperException on failure
-         */
-        final public int getGeneCount(DataSource ds) throws IDMapperException
-        {
-                int result = 0;
-                try
-                {
-                        ResultSet r = getConnection().createStatement().executeQuery(
-                                        "SELECT COUNT(*) FROM datanode WHERE code = '" + ds.getSystemCode() + "'");
-                        r.next();
-                        result = r.getInt (1);
-                        r.close();
-                }
-                catch (SQLException e)
-                {
-                        throw new IDMapperException (e);
-                }
-                return result;
-        }
-        
+    }
+    
+    public Connection getConnection() throws SQLException
+    {
+        
+        return pool.getConnection();
+    }
+    
+    /** {@inheritDoc} */
+    @Override
+    final public String getDbName()
+    {
+        return dbName;
+    }
+    
+    /**
+     * @return number of rows in gene table.
+     * @throws IDMapperException
+     *             on failure
+     */
+    final public int getGeneCount() throws IDMapperException
+    {
+        int result = 0;
+        
+        ResultSet r = null;
+        Statement s = null;
+        Connection c = null;
+        try
+        {
+            c = getConnection();
+            s = c.createStatement();
+            r = s.executeQuery("SELECT COUNT(*) FROM "
+                                                       + "datanode");
+            r.next();
+            result = r.getInt(1);
+        }
+        catch (SQLException e)
+        {
+            throw new IDMapperException(e);
+        }
+        finally
+        {   
+            closeSilently(r, s, c);
+        }
+        return result;
+    }
+    
+    /**
+     * @param ds
+     *            DataSource to count identifiers for.
+     * @return number of identifiers table for the given datasource
+     * @throws IDMapperException
+     *             on failure
+     */
+    final public int getGeneCount(DataSource ds) throws IDMapperException
+    {
+        int result = 0;
+        
+        ResultSet r = null;
+        Statement s = null;
+        Connection c = null;
+        
+        try
+        {
+            c = getConnection();
+            s = c.createStatement();
+            r = s.executeQuery("SELECT COUNT(*) FROM datanode WHERE code = '"
+                               + ds.getSystemCode() + "'");
+            
+            r.next();
+            result = r.getInt(1);
+        }
+        catch (SQLException e)
+        {
+            throw new IDMapperException(e);
+        }
+        finally
+        {
+            closeSilently(r, s, c);
+        }
+        
+        return result;
+    }
+    
+    protected void closeSilently(Connection c)
+    {
+        if (c != null)
+        {
+            try
+            {
+                c.close();
+            }
+            catch (SQLException e)
+            {
+                LOG.error("Error closing result set: " + e, e);
+            }
+        }
+    }
+    
+    protected void closeSilently(Statement s)
+    {
+        if (s != null)
+        {
+            try
+            {
+                s.close();
+            }
+            catch (SQLException e)
+            {
+                LOG.error("Error closing statement: " + e, e);
+            }
+        }
+    }
+    
+    protected void closeSilently(ResultSet rs)
+    {
+        if (rs != null)
+        {
+            try
+            {
+                rs.close();
+            }
+            catch (SQLException e)
+            {
+                LOG.error("Error closing conection: " + e, e);
+            }
+        }
+    }
+    
+    public void closeSilently(ResultSet r,
+                              Statement s,
+                              Connection c)
+    {
+        
+        closeSilently(r);
+        
+        closeSilently(s);
+        
+        closeSilently(c);
+        
+    }
+
+    @Override
+    public void close() throws IDMapperException
+    {
+        if (pool != null)
+        {
+            
+            pool.shutdownDriver();
+            
+        }
+        connected = false;
+    }
+
+    @Override
+    public boolean isConnected()
+    {
+       
+        return connected;
+    }
+    
+    
 }