001/*
002 *                    BioJava development code
003 *
004 * This code may be freely distributed and modified under the
005 * terms of the GNU Lesser General Public Licence.  This should
006 * be distributed with the code.  If you do not have a copy,
007 * see:
008 *
009 *      http://www.gnu.org/copyleft/lesser.html
010 *
011 * Copyright for this code is held jointly by the individual
012 * authors.  These should be listed in @author doc comments.
013 *
014 * For more information on the BioJava project and its aims,
015 * or to join the biojava-l mailing list, visit the home page
016 * at:
017 *
018 *      http://www.biojava.org/
019 *
020 */
021package org.biojava.utils;
022
023import java.sql.Connection;
024import java.sql.DriverManager;
025import java.sql.SQLException;
026import java.sql.Statement;
027import java.util.LinkedList;
028
029/**
030 * Really simple connection pool for JDBC databases.
031 *
032 * <h2>Use:</h2>
033 * <pre>
034 * JDBCConnectionPool pool = new JDBCConnectionPool(jdbcURL, userName, passwd);
035 * ...
036 * Connection conn = pool.takeConnection();
037 * // do stuff with conn
038 * pool.putConnection(conn);
039 * // don't use conn from here on
040 *
041 * Statement stmt = pool.takeStatement();
042 * // do stuff with stmt
043 * pool.putStatement(stmt);
044 * // don't do anything else with stmt
045 * </pre>
046 *
047 * <p>It is not a good idea to call <code>close()</code> on a connection you
048 * get from a pool. This would prevent it from being re-used. Also, we have
049 * seen some odd behavior with connections involved in transactions being
050 * re-used. We have not yet identified exactly how you can safely use a
051 * pooled connection for transaction-safe code.</p>  
052 *
053 * <p><em>Note:</em> We should probably be moving to a propper connection pool
054 * API. Let's standardise on one soon.</p>
055 *
056 * @author Thomas Down
057 * @author Matthew Pocock
058 */
059
060public class JDBCConnectionPool {
061  private final String dbURL;
062  private final String dbUser;
063  private final String dbPass;
064
065  private LinkedList connectionPool;
066
067  {
068    connectionPool = new LinkedList();
069  }
070
071  public JDBCConnectionPool(String url, String user, String pass)
072  {
073    dbURL = url;
074    dbUser = user;
075    dbPass = pass;
076  }
077
078  public JDBCConnectionPool(String url)
079  {
080    this(url, null, null);
081  }
082
083  //
084  // Manage a pool of transactions with the database.
085  //
086
087  public Connection takeConnection()
088          throws SQLException
089  {
090    Connection conn = null;
091
092    synchronized (connectionPool) {
093      if (connectionPool.size() > 0) {
094        conn = (Connection) connectionPool.removeFirst();
095      }
096    }
097
098    // We don't perform the isClosed in the synchronized block in case the
099    // network is being slow.
100
101    //if (conn != null) {
102    //    if (!conn.isClosed()) {
103    //            // hack for turfing out bad connections
104    //            Statement stmt = conn.createStatement();
105    //            stmt.execute("SELECT 1");
106    //            return conn;
107    //
108    //    } else {
109    //  // We simply drop conn on the floor.  It should be safely collected.
110    //  return takeConnection();
111    //    }
112    //}
113
114    // Statement-pool was empty -- let's create a new connection.
115
116    if(dbUser != null) {
117      conn = DriverManager.getConnection(dbURL, dbUser, dbPass);
118    } else {
119      conn = DriverManager.getConnection(dbURL);
120    }
121    Statement st = conn.createStatement();
122    // st.execute("SET OPTION SQL_BIG_SELECTS=1");
123    st.close();
124    return conn;
125  }
126
127  public void putConnection(Connection c)
128          throws SQLException
129  {
130    if(!c.isClosed()) {
131      synchronized (connectionPool) {
132        connectionPool.addLast(c);
133      }
134    }
135  }
136
137  public Statement takeStatement()
138          throws SQLException
139  {
140    return takeConnection().createStatement();
141  }
142
143  public void putStatement(Statement st)
144          throws SQLException
145  {
146    putConnection(st.getConnection());
147    st.close();
148  }
149}