Java tutorial
/** * $Id $ * $License$ * * Created on Jan 20, 2008 2:30:41 PM */ package com.insprise.common.db; import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; import javax.sql.DataSource; import javax.sql.XAConnection; import javax.sql.XADataSource; import org.apache.commons.dbcp.ConnectionFactory; import org.apache.commons.dbcp.DataSourceConnectionFactory; import org.apache.commons.dbcp.DriverManagerConnectionFactory; import org.apache.commons.dbcp.PoolableConnectionFactory; import org.apache.commons.dbcp.PoolingDataSource; import org.apache.commons.pool.impl.GenericObjectPool; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * A database connection pool implemented with Apache Jakarta Commons DBCP. * The database pool will be initialized upon the first callling of {@linkplain #getConnection()}. * When it is not longer being used, always remember to {@linkplain #close()} it. * <p>Dependencies:</p> * <ol><li>commons-dbcp-1.2.2</li> * <li>commons-pool-1.3 or above [DBCP dependency]</li> * <li>commons-logging-1.0.4 or above [DBCP dependency]</li> * </ol> */ public class DefaultConnectionPool implements ConnectionPool { static Logger log = LoggerFactory.getLogger(DefaultConnectionPool.class); /** * The type of the underlying source that connections are obtained. */ public static enum SourceType { DRIVER_MANAGER, DATASOURCE, XADATASOURCE } /** The type of the underlying source that connections are obtained. */ protected SourceType sourceType; /** The actually connection pool. */ private GenericObjectPool connectionPool; /** The datasource that returns pooling connections for {@linkplain #sourceType} is {@linkplain SourceType#DRIVER_MANAGER}, or the underlying data source, applicable for {@linkplain #sourceType} is {@linkplain SourceType#DATASOURCE} or {@linkplain SourceType#XADATASOURCE}*/ protected DataSource dataSource; /** Whether the connection pool has been set up. */ private boolean initialized = false; /** The name of the database driver class. */ String driverClass; /** The max number of connections in the pool. A negative number means no limit. */ int maxActive; /** The max number of connections to retain in the pool. */ int maxIdle; /** The max time in MS to wait for a connection. Default is 10s.*/ int maxWaittime = 10 * 1000; /** Number of seconds an idle connection should be disconnected. -1 means forever. */ int connectionTimeout = -1; /** The JDBC connection properties for the database. */ Properties properties; /** The database connection URL. */ String url; /** The SQL query that will be used to validate connections from this pool before returning them to the caller */ protected String validationQuery; /** * Empty constructor - should only be called by the subclass. */ protected DefaultConnectionPool() { } /** * Constructs a connection pool with the given parameters. * @param underlyingDataSource the data source that connection should be obtained from. * @throws ClassNotFoundException If the <code>driverClass</code> is not found. */ public DefaultConnectionPool(DataSource underlyingDataSource) { if (underlyingDataSource == null) { throw new IllegalArgumentException("Underlying data source must not be null!"); } this.dataSource = underlyingDataSource; this.sourceType = (underlyingDataSource instanceof XADataSource) ? SourceType.XADATASOURCE : SourceType.DATASOURCE; } /** * Constructs a connection pool with the given parameters. * @param driverClass The name of the database driver class. * @param url The database connection URL. * @param properties the JDBC connection properties for the database. * @param maxActive The max number of connections in the pool. * @param maxIdle The max number of connections to retain in the pool. * @param maxWaittime The max time in MS to wait for a connection. Default is 10s. * @param validationQuery The SQL query that will be used to validate connections from this pool before returning them to the caller * @throws ClassNotFoundException If the <code>driverClass</code> is not found. */ public DefaultConnectionPool(String driverClass, String url, Properties properties, int maxActive, int maxIdle, int maxWaittime, String validationQuery) { this.driverClass = driverClass; this.url = url; this.properties = properties; this.maxActive = maxActive; this.maxIdle = maxIdle; this.maxWaittime = maxWaittime; this.validationQuery = validationQuery; sourceType = SourceType.DRIVER_MANAGER; } /** * Constructs a connection pool with the given parameters. * If you need provide more properties to the database, use {@linkplain DefaultConnectionPool#DefaultConnectionPool(String, String, Properties, int, int, int)}. * @param driverClass The name of the database driver class. * @param url The database connection URL. * @param username the user name used to connect to the database. * @param password the password used to connect to the database. * @param maxActive The max number of connections in the pool. * @param maxIdle The max number of connections to retain in the pool. * @param maxWaittime The max time in MS to wait for a connection. Default is 10s. * @param validationQuery The SQL query that will be used to validate connections from this pool before returning them to the caller * @throws ClassNotFoundException If the <code>driverClass</code> is not found. */ public DefaultConnectionPool(String driverClass, String url, String username, String password, int maxActive, int maxIdle, int maxWaittime, String validationQuery) { this(driverClass, url, DBUtilities.getConnectionProperty(username, password), maxActive, maxIdle, maxWaittime, validationQuery); } /** * Sets up the connection pool. Call this at most once only! * * @throws ClassNotFoundException */ private void setupPool() throws ClassNotFoundException { if (sourceType != SourceType.DRIVER_MANAGER) { throw new RuntimeException(); } connectionPool = new GenericObjectPool(null); setMaxActive(maxActive); setMaxIdle(maxIdle); setMaxWaittime(maxWaittime); // establish connection factory. ConnectionFactory connectionFactory = null; Class.forName(driverClass); connectionFactory = new DriverManagerConnectionFactory(url, properties); if (validationQuery == null) { log.warn("Validation SQL for database connection pool is null!"); } else { // log.info("Validation SQL for database connection pool is: " + validationQuery); } PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, validationQuery, false, true); // force to execute validationQuery before borrowing connection from pool. connectionPool.setTestOnBorrow(true); dataSource = new PoolingDataSource(connectionPool); log.debug("Initialized " + toString()); } /** * Gets a read and write connection. * A shortcut for: getDataSource().getConnection(). After using the connection, always remember to close it. * <p> * <code><pre> * Connection conn = null; * try { * conn = pool.getConnection(); * ... * }catch(Exception e) { * ... * }finally{ * <b>conn.close();</b> // return the connection to the pool. * } * </pre></code> * @param readOnly Whether the connection should be read only or not. * @return getDataSource().getConnection(). * @throws SQLException */ public Connection getConnection() throws SQLException { return getConnection(false); } /** * Gets a connection. * A shortcut for: getDataSource().getConnection(). After using the connection, always remember to close it. * <p> * <code><pre> * Connection conn = null; * try { * conn = pool.getConnection(); * ... * }catch(Exception e) { * ... * }finally{ * <b>conn.close();</b> // return the connection to the pool. * } * </pre></code> * @param readOnly Whether the connection should be read only or not. * @return getDataSource().getConnection(). * @throws SQLException */ public Connection getConnection(boolean readOnly) throws SQLException { if (sourceType == SourceType.DRIVER_MANAGER) { if (!initialized) { synchronized (this) { if (!initialized) { try { setupPool(); } catch (ClassNotFoundException e) { throw new SQLException(e.getMessage(), e); } } initialized = true; } } } Connection conn = dataSource.getConnection(); conn.setReadOnly(readOnly); if (readOnly) { conn.setAutoCommit(true); // auto commit read only. } return conn; } /** * Gets a read-only connection. * A shortcut for: getDataSource().getConnection(). After using the connection, always remember to close it. * <p> * <code><pre> * Connection conn = null; * try { * conn = pool.getConnection(); * ... * }catch(Exception e) { * ... * }finally{ * <b>conn.close();</b> // return the connection to the pool. * } * </pre></code> * * @return getDataSource().getConnection(). * @throws SQLException */ public Connection getConnectionReadOnly() throws SQLException { return getConnection(true); } /** * Obtains a XAConnection only applicable for {@linkplain SourceType#XADATASOURCE}. * @return * @throws Exception when source type is {@linkplain SourceType#DRIVER_MANAGER} or {@linkplain SourceType#DATASOURCE} */ public XAConnection getXAConnection() throws SQLException { if (sourceType != SourceType.XADATASOURCE) { throw new UnsupportedOperationException(); } return ((XADataSource) dataSource).getXAConnection(); } /** * Obtains a XAConnection only applicable for {@linkplain SourceType#XADATASOURCE}. * @return * @throws Exception when source type is {@linkplain SourceType#DRIVER_MANAGER} or {@linkplain SourceType#DATASOURCE} */ public XAConnection getXAConnection(String username, String password) throws SQLException { return getXAConnection(); } /** * Closes the connection pool and frees resources. */ public void close() { // if source type is another data source, we do not care. if (sourceType == SourceType.DATASOURCE || sourceType == SourceType.XADATASOURCE) { return; } try { log.debug("Trying to close - " + toString()); if (connectionPool != null) { // it could be null if not initialized yet. connectionPool.close(); log.debug("Closed - " + toString()); } } catch (Exception e) { log.warn(e.getMessage(), e); } } /** * Returns the current number of active connections. * * @return the current number of active connections or negative value if not available */ public int getActive() { return connectionPool == null ? 0 : connectionPool.getNumActive(); } /** * Returns the current number of idle connections. * @return the current number of idle connections or negative value if not available */ public int getIdle() { return connectionPool == null ? 0 : connectionPool.getNumIdle(); } /** * Gets the max number of connections in the pool. A negative number means no limit. * @return The max number of connections in the pool. A negative number means no limit. */ public int getMaxActive() { return maxActive; } /** * Gets the max number of connections in the pool. A negative number means no limit. */ public void setMaxActive(int maxActive) { this.maxActive = maxActive; if (connectionPool != null) { connectionPool.setMaxActive(maxActive); } } /** * Gets the max number of connections to retain in the pool. A negative number means no limit. * @return The max number of connections to retain in the pool. A negative number means no limit. */ public int getMaxIdle() { return maxIdle; } /** * Sets the max number of connections to retain in the pool. A negative number means no limit. */ public void setMaxIdle(int maxIdle) { this.maxIdle = maxIdle; if (connectionPool != null) { connectionPool.setMaxIdle(maxIdle); } } /** * Gets the max time in MS to wait for a connection. Default is 10s. * @return The max time in MS to wait for a connection. Default is 10s. */ public int getMaxWaittime() { return maxWaittime; } /** * Sets the max time in MS to wait for a connection. Default is 10s. * @return The max time in MS to wait for a connection. Default is 10s. */ public void setMaxWaittime(int maxWaittime) { this.maxWaittime = maxWaittime; if (connectionPool != null) { connectionPool.setMaxWait(maxWaittime); } } /** * Gets the number of seconds an idle connection should be disconnected. -1 means forever. * @return the number of seconds an idle connection should be disconnected. -1 means forever. */ public int getConnectionTimeout() { return connectionTimeout; } /** * Sets the number of seconds an idle connection should be disconnected. -1 means forever. * <p>This method will call the following methods on the connection pool: * <ul><li>connectionPool.setMinEvictableIdleTimeMillis * <li>connectionPool.setTimeBetweenEvictionRunsMillis * <li>connectionPool.setNumTestsPerEvictionRun</p> */ public void setConnectionTimeout(int connectionTimeout) { this.connectionTimeout = connectionTimeout; if (connectionPool != null) { connectionPool.setMinEvictableIdleTimeMillis(connectionTimeout); if (connectionTimeout > 0) { connectionPool.setTimeBetweenEvictionRunsMillis(Math.max(10 * 60, connectionTimeout / 5)); // 10mins or 1/5 of the connection timeout. connectionPool.setNumTestsPerEvictionRun(maxIdle); } else { connectionPool.setTimeBetweenEvictionRunsMillis(-1); // disable eviction. } } } /** * Returns the {@link DataSource} used to obtain connections. * @return the datasource used to obtain pooling connections. */ public DataSource getDataSource() { return dataSource; } /** * Gets the name of the driver class for the database. * @return */ public String getDriverClass() { return driverClass; } /** * Sets the name of the driver class for the database. * This will not have any effect if the pool has already been initialized. * @param driverClass the name of the driver class for the database. */ public void setDriverClass(String driverClass) { this.driverClass = driverClass; } /** * Sets the connection properties (usually username and password should be in the properties) if any. * This will not have any effect if the pool has already been initialized. * @param properties the connection properties if any. */ public void setProperties(Properties properties) { this.properties = properties; } /** * Sets the connection url. * This will not have any effect if the pool has already been initialized. * @param url the connenction url. */ public void setUrl(String url) { this.url = url; } /** * Gets the JDBC connection properties for the database. * @return The JDBC connection properties for the database. */ public Properties getProperties() { return properties; } /** * Gets the database connection URL. * @return The database connection URL. */ public String getUrl() { return url; } @Override public String toString() { StringBuilder sb = new StringBuilder(); sb.append("connection pool - "); if (sourceType == SourceType.DRIVER_MANAGER) { sb.append("driver class: ").append(driverClass).append(", url: ").append(url).append(", max active: ") .append(maxActive).append(", max idle: ").append(maxIdle).append(", max wait time: ") .append(maxWaittime).append(", connection timeout: ").append(connectionTimeout); } else { sb.append("delegate of datasource: ").append(dataSource); } return sb.toString(); } }