Connection Pool
Attempting to connect databases in a Java program (getting a Connection instance) is time-consuming. If you can create multiple Connections in advance and store them in the repository, you can save time to get Connection instances. This programming technique is called connection pooling.
When using connection pooling, you have to return the Connection instance to the repository instead of using the Connection's close() method.
Summary of Connection Pooling Classes
Log outputs log messages to a file.
DBConnectionPool puts database connections into a pool and manages it.
DBConnectionPoolManager manages DBConnectionPool instances.
ConnectionManager is an abstract class that asks the DBConnectionPoolManager for a connection. The reason for the abstract class is that it considers multiple databases. Depending on the database you are using, create a class that inherits this class.
OracleConnectionManager inherits the ConnectionManager to manage connections for Oracle.
The oracle.properties file is for the connection pool configuration for Oracle. In oracle.properties, the string "oracle" is the distinguished name of the connection pool instance for Oracle. Managing the configuration as a file is better for reuse and maintenance than implementing it as Java code.
Connection Pooling Sources
Log.java
package net.java_school.util;
import java.io.*;
import java.util.Date;
public class Log {
public String logFile = "C:/Users/java/jdbc/connection-pool.log";
FileWriter fw = null;
public static final String LINE_SEPARATOR = System.getProperty("line.separator");
public Log() {
try {
fw = new FileWriter(logFile, true);
} catch (IOException e){}
}
public void close() {
try {
fw.close();
} catch (IOException e){}
}
public void close(FileWriter fw) {
try {
fw.close();
} catch (IOException e){}
}
public void debug(String msg) {
try {
fw.write(new java.util.Date()+ " : ");
fw.write(msg + LINE_SEPARATOR);
fw.flush();
} catch (IOException e) {
System.err.println("IOException.......!!");
}
}
public static void out(String msg) {
System.out.println(new Date() + ": " + msg);
}
public static void err(String msg) {
System.out.println(new Date() + ": " + msg);
}
public static void err(Throwable e, String msg) {
System.err.println(new Date() + ": " + msg);
e.printStackTrace(System.out);
}
}
DBConnectionPool.java
package net.java_school.db.dbpool;
import java.util.*;
import java.sql.*;
import java.util.Date;
import net.java_school.util.Log;
class DBConnectionPool {
//Number of connections currently in use
private int checkedOut;
// Free Connection List
private Vector<Connection> freeConnections = new Vector<Connection>();
//Maximum number of connections
private int maxConn;
//Initial number of connections
private int initConn;
//Waiting time (maximum time to wait when there is no connection in the pool)
private int maxWait;
//Connection Pool Name
private String name;
//DB Password
private String password;
//DB URL
private String URL;
//DB UserID
private String user;
//Constructor
public DBConnectionPool(String name,
String URL,
String user,
String password,
int maxConn,
int initConn,
int waitTime) {
this.name = name;
this.URL = URL;
this.user = user;
this.password = password;
this.maxConn = maxConn;
this.maxWait = waitTime;
for (int i = 0; i < initConn; i++) {
freeConnections.addElement(newConnection());
}
}
//Returning Connection
//@param con : Connection to return
public synchronized void freeConnection(Connection con) {
freeConnections.addElement(con);
checkedOut--;
//Notify thread waiting to get Connection
notifyAll();
}
//Get Connection
public synchronized Connection getConnection() {
Connection con = null;
//If Connection is in Free List, get the first of List
if (freeConnections.size() > 0) {
con = (Connection) freeConnections.firstElement();
freeConnections.removeElementAt(0);
try {
//If the connection is closed by the DBMS, request again
if (con.isClosed()) {
Log.err("Removed bad connection from " + name);
con = getConnection();
}
} //If strange connection occurs, request again
catch (SQLException e) {
Log.err(e, "Removed bad connection from " + name);
con = getConnection();
}
} //If Connection is not in Free List, create new
else if (maxConn == 0 || checkedOut < maxConn) {
con = newConnection();
}
if (con != null) {
checkedOut++;
}
return con;
}
//Get Connection
//@param timeout : Maximum Wait Time to Obtain a Connection
public synchronized Connection getConnection(long timeout) {
long startTime = new Date().getTime();
Connection con;
while ((con = getConnection()) == null) {
try {
wait(timeout * maxWait);
} catch (InterruptedException e) {}
if ((new Date().getTime() - startTime) >= timeout) {
//Wait timeout
return null;
}
}
return con;
}
//Get Connection
private Connection newConnection() {
Connection con = null;
try {
if (user == null) {
con = DriverManager.getConnection(URL);
} else {
con = DriverManager.getConnection(URL, user, password);
}
Log.out("Created a new connection in pool " + name);
} catch (SQLException e) {
StringBuffer sb = new StringBuffer();
sb.append("Can't create a new connection for ");
sb.append(URL);
sb.append(" user: ");
sb.append(user);
sb.append(" passwd: ");
sb.append(password);
Log.err(e, sb.toString());
return null;
}
return con;
}
}
DBConnectionPoolManager.java
package net.java_school.db.dbpool;
import java.sql.*;
import java.util.*;
import net.java_school.util.Log;
public class DBConnectionPoolManager {
//To apply the singleton pattern to the DBConnectionPoolManager (keep only one instance), declare it as static
static private DBConnectionPoolManager instance;
private Vector<String> drivers = new Vector<String>();
private Hashtable<String, DBConnectionPool> pools = new Hashtable<String, DBConnectionPool>();
//Obtaining instance of DBConnectionPoolManager
// @return DBConnectionManger
static synchronized public DBConnectionPoolManager getInstance() {
if (instance == null) {
instance = new DBConnectionPoolManager();
}
return instance;
}
// Default Constructor
private DBConnectionPoolManager() {}
//Send current Connection to Free Connection List
//@param name : Pool Name
//@param con : Connection
public void freeConnection(String name, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
pool.freeConnection(con);
}
Log.out("One Connection of " + name + " was freed");
}
//Obtain Open Connection. Creates a new connection if there are no open connections and the maximum number of connections has not been reached.
//Waits for the default wait time when there are no open connections currently and the maximum number of connections is in use.
//@param name : Pool Name
//@return Connection : The connection or null
public Connection getConnection(String name) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection(10);
}
return null;
}
// Create a Connection Pool
// @param poolName : Name of Pool to create
// @param url : DB URL
// @param user : DB UserID
// @param password : DB Password
private void createPools(String poolName,
String url,
String user,
String password,
int maxConn,
int initConn,
int maxWait) {
DBConnectionPool pool = new DBConnectionPool(poolName, url, user, password, maxConn, initConn, maxWait);
pools.put(poolName, pool);
Log.out("Initialized pool " + poolName);
}
//Initialization
public void init(String poolName,
String driver,
String url,
String user,
String passwd,
int maxConn,
int initConn,
int maxWait) {
loadDrivers(driver);
createPools(poolName, url, user, passwd, maxConn, initConn, maxWait);
}
//JDBC Driver Loading
//@param driverClassName : The JDBC driver for the DB you want to use.
private void loadDrivers(String driverClassName) {
try {
Class.forName(driverClassName);
drivers.addElement(driverClassName);
Log.out("Registered JDBC driver " + driverClassName);
} catch (Exception e) {
Log.err(e, "Can't register JDBC driver: " + driverClassName);
}
}
public Hashtable<String,DBConnectionPool> getPools() {
return pools;
}
public int getDriverNumber() {
return drivers.size();
}
}
ConnectionManager.java
package net.java_school.db.dbpool;
import java.sql.*;
import java.io.*;
import java.util.*;
import net.java_school.util.Log;
public abstract class ConnectionManager {
protected DBConnectionPoolManager connMgr;
protected String poolName, dbServer, dbName, port, userID, passwd;
protected int maxConn,initConn, maxWait;
private Properties dbProperties;
private String configFile;
public ConnectionManager(String pool) {
poolName = pool;
//Specifying the directory for the Property file
configFile = "C:/jdbc/"+poolName+".properties";
try {
dbProperties = readProperties();
dbServer = getProperty("dbServer");
port = getProperty("port");
dbName = getProperty("dbName");
userID = getProperty("userID");
passwd = getProperty("passwd");
maxConn = Integer.parseInt(getProperty("maxConn"));
initConn = Integer.parseInt(getProperty("initConn"));
maxWait = Integer.parseInt(getProperty("maxWait"));
} catch (IOException ioe) {
Log.err("Error reading properties of " + configFile);
}
}
public Connection getConnection() {
return (connMgr.getConnection(poolName));
}
public void freeConnection(Connection conn) {
connMgr.freeConnection(poolName, conn);
}
private String getProperty(String prop) throws IOException {
return (dbProperties.getProperty(prop));
}
protected synchronized Properties readProperties() throws IOException {
Properties tempProperties = new Properties();
FileInputStream in = new FileInputStream(configFile);
tempProperties.load(in);
return tempProperties;
}
public int getDriverNumber() {
return connMgr.getDriverNumber();
}
}
OracleConnectionManager.java
package net.java_school.db.dbpool;
public class OracleConnectionManager extends ConnectionManager {
public OracleConnectionManager() {
super("oracle");
String JDBCDriver = "oracle.jdbc.driver.OracleDriver";
//Oracle JDBC thin driver
String JDBCDriverType = "jdbc:oracle:thin";
String url = JDBCDriverType + ":@" + dbServer + ":" + port + ":" + dbName;
connMgr = DBConnectionPoolManager.getInstance();
connMgr.init(poolName, JDBCDriver, url, userID, passwd, maxConn, initConn, maxWait);
}
}
oracle.properties
############################################ # Database Connection Properties for Oracle ############################################ # Database Server Name OR IP address dbServer = 127.0.0.1 # The port number your DB server listents to. port = 1521 # Database Name dbName = XE # Database User userID = scott # Database Password passwd = tiger # Maximum Connection Number maxConn = 20 # Inital Connection Number initConn = 5 # Maximum Wait Time maxWait = 5
How to use
Create an empty file named connection-pool.log in C:/Users/java/jdbc according to the following code in Log.java:
public String logFile = "C:/Users/java/jdbc/connection-pool.log"
Create a file named oracle.properties in C:/Users/java/jdbc according to the following code in ConnectionManager.java:
configFile = "C:/Users/java/jdbc/" + poolName + ".properties";
If no compile error occurs, modify GetEmp class to use the connection pool.
GetEmp.java
package net.java_school.jdbc;
import java.sql.*;
import net.java_school.util.Log;
import net.java_school.db.dbpool.*;
public class GetEmp {
public static void main(String[] args) {
ConnectionManager conMgr = new OracleConnectionManager();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String query = "SELECT * FROM emp";
try {
conn = conMgr.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
String empno = rs.getString(1);
String ename = rs.getString(2);
String job = rs.getString(3);
String mgr = rs.getString(4);
String hiredate = rs.getString(5);
String sal = rs.getString(6);
String comm = rs.getString(7);
String depno = rs.getString(8);
System.out.println(empno + " : " + ename + " : " + job + " : " + mgr
+ " : " + hiredate + " : " + sal + " : " + comm + " : " + depno);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
stmt.close();
conMgr.freeConnection(conn);
} catch (SQLException e) {}
}
}
}
DBCP
DBCP is Apache's open-source connection pool library.
More information is available at:
http://commons.apache.org/proper/commons-dbcp/
An example we will practice is BasicDataSourceExample.java at the following address: http://svn.apache.org/viewvc/commons/proper/dbcp/trunk/doc/ We will test BasicDataSourceExample.java with a little modification. Add the commons-dbcp, commons-pool, and commons-logging libraries to the Build Path to practice the example.
Note that DBCP 2 runs on JDK 7 environment and higher. You have to use DBCP 1.4 on JDK 6 and DBCP 1.3 on JDK 6 and below.
- commons-dbcp2-2.0.1-bin.zip http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi
- commons-pool2-2.2-bin.zip http://commons.apache.org/proper/commons-pool/download_pool.cgi
- commons-logging-1.2-bin.zip http://commons.apache.org/proper/commons-logging/download_logging.cgi
BasicDataSourceExample.java
package net.java_school.dbcp;
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
//
// Here are the dbcp-specific classes.
// Note that they are only used in the setupDataSource
// method. In normal use, your classes interact
// only with the standard JDBC API
//
import org.apache.commons.dbcp2.BasicDataSource;
//
// Here's a simple example of how to use the BasicDataSource.
//
//
// Note that this example is very similiar to the PoolingDriver
// example.
//
// To compile this example, you'll want:
// * commons-pool-1.5.6.jar
// * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+)
// * j2ee.jar (for the javax.sql classes)
// in your classpath.
//
// To run this example, you'll want:
// * commons-pool-1.5.6.jar
// * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+)
// * j2ee.jar (for the javax.sql classes)
// * the classes for your (underlying) JDBC driver
// in your classpath.
//
// Invoke the class using two arguments:
// * the connect string for your underlying JDBC driver
// * the query you'd like to execute
// You'll also want to ensure your underlying JDBC driver
// is registered. You can use the "jdbc.drivers"
// property to do this.
//
// For example:
// java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver \
// -classpath commons-pool-1.5.6.jar:commons-dbcp-1.4.jar:j2ee.jar:oracle-jdbc.jar:. \
// PoolingDataSourceExample
// "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid"
// "SELECT * FROM DUAL"
//
public class BasicDataSourceExample {
public static void main(String[] args) {
// First we set up the BasicDataSource.
// Normally this would be handled auto-magically by
// an external configuration, but in this example we'll
// do it manually.
//
System.out.println("Setting up data source.");
DataSource dataSource = setupDataSource("jdbc:oracle:thin:@127.0.0.1:1521:XE");
System.out.println("Done.");
//
// Now, we can use JDBC DataSource as we normally would.
//
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try {
System.out.println("Creating connection.");
conn = dataSource.getConnection();
System.out.println("Creating statement.");
stmt = conn.createStatement();
System.out.println("Executing statement.");
rset = stmt.executeQuery("SELECT * FROM dept");
System.out.println("Results:");
int numcols = rset.getMetaData().getColumnCount();
while(rset.next()) {
for(int i=1;i<=numcols;i++) {
System.out.print("\t" + rset.getString(i));
}
System.out.println("");
}
} catch(SQLException e) {
e.printStackTrace();
} finally {
try { if (rset != null) rset.close(); } catch(Exception e) { }
try { if (stmt != null) stmt.close(); } catch(Exception e) { }
try { if (conn != null) conn.close(); } catch(Exception e) { }
}
}
public static DataSource setupDataSource(String connectURI) {
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUsername("scott");
ds.setPassword("tiger");
ds.setUrl(connectURI);
return ds;
}
public static void printDataSourceStats(DataSource ds) {
BasicDataSource bds = (BasicDataSource) ds;
System.out.println("NumActive: " + bds.getNumActive());
System.out.println("NumIdle: " + bds.getNumIdle());
}
public static void shutdownDataSource(DataSource ds) throws SQLException {
BasicDataSource bds = (BasicDataSource) ds;
bds.close();
}
}
Let's create a class that gets a connection from the connection pool by referring to the example above.
Test.java
package net.java_school.dbcp;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class Test {
private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
private static final String USER = "scott";
private static final String PASS = "tiger";
private DataSource dataSource;
public Test() {
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUsername(USER);
ds.setPassword(PASS);
ds.setUrl(URL);
this.dataSource = ds;
}
public Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
}
Modify GetEmp class to use the Test class.
GetEmp.java
package net.java_school.jdbc;
import java.sql.*;
import net.java_school.dbcp.*;
public class GetEmp {
public static void main(String[] args) {
Test test = new Test();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String query = "SELECT * FROM emp";
try {
conn = test.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
String empno = rs.getString(1);
String ename = rs.getString(2);
String job = rs.getString(3);
String mgr = rs.getString(4);
String hiredate = rs.getString(5);
String sal = rs.getString(6);
String comm = rs.getString(7);
String depno = rs.getString(8);
System.out.println(empno + " : " + ename + " : " + job + " : " + mgr
+ " : " + hiredate + " : " + sal + " : " + comm + " : " + depno);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {}
}
}
}
Note that the connection is returned by the conn.close();. Would not it be a problem to return resources like this when using a connection pool? To solve this question, you have to look at the DataSource interface. The DataSource is the connection factory. There are three kinds of implementations of DataSource.
- Base implementation: Produces standard connection objects.
- Connection pooling implementation: Automatically creates a connection object that participates in the connection pool. This implementation works with the middle-tier connection pooling manager.
- Distributed Transaction Implementation: Produces a connection object that participates in a connection pool for distributed transactions. This implementation works with the middle-tier transaction manager and the connection pooling manager.
DBCP implements the javax.sql.DataSource interface. The connections we have used so far are different from those for connection pooling. The close() method of the connection object for connection pooling returns itself to the pool.
We will create an example similar to the connection pool we already used. First, create a ConnectionManager abstract class. The constructor of this abstract class reads the configuration file and sets the object's members. This abstract class has a method for getting a Connection instance.
ConnectionManager.java
package net.java_school.dbcp;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
public abstract class ConnectionManager {
protected String poolName, dbServer, dbName, port, userID, passwd;
protected int maxConn, initConn, maxWait;
protected DataSource dataSource;
public ConnectionManager(String pool) {
String configFile = pool + ".properties";
Properties properties = readProperties(configFile);
dbServer = properties.getProperty("dbServer");
port = properties.getProperty("port");
dbName = properties.getProperty("dbName");
userID = properties.getProperty("userID");
passwd = properties.getProperty("passwd");
maxConn = Integer.parseInt(properties.getProperty("maxConn"));
initConn = Integer.parseInt(properties.getProperty("initConn"));
maxWait = Integer.parseInt(properties.getProperty("maxWait"));
}
protected synchronized Properties readProperties(String configFile) {
Properties properties = new Properties();
FileInputStream in = null;
try {
in = new FileInputStream(configFile);
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
return null;
}
return properties;
}
public Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
}
Next, create OracleConnectionManager class that inherits ConnectionManager.
OracleConnectionManager.java
package net.java_school.dbcp;
import org.apache.commons.dbcp2.BasicDataSource;
public class OracleConnectionManager extends ConnectionManager {
public OracleConnectionManager() {
super("oracle");
String driverClassName = "oracle.jdbc.driver.OracleDriver";
String driverType = "jdbc:oracle:thin";
String url = driverType + ":@" + dbServer + ":" + port + ":" + dbName;
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName(driverClassName);
ds.setUsername("scott");
ds.setPassword("tiger");
ds.setUrl(url);
this.dataSource = ds;
}
}
MaxConn, initConn, maxWait If not set, the default values work. Once you've set these up in your properties file, add the following to your code:
ds.setInitialSize(initConn); ds.setMaxTotal(maxConn); ds.setMaxWaitMillis(maxWait);
For instructions on setting these in BasicDataSource, see the official site documentation.
Copy the oracle.properties file to the root directory of your Eclipse project.
oracle.properties
############################################ # Database Connection Properties for Oracle ############################################ # Database Server Name OR IP address dbServer = 127.0.0.1 # The port number your DB server listents to. port = 1521 # Database Name dbName = XE # Database User userID = scott # Database Password passwd = tiger # Maximum Connection Number maxConn = 20 # Inital Connection Number initConn = 5 # Maximum Wait Time maxWait = 5
Modify GetEmp class to use BasicDataSource.
GetEmp.java
package net.java_school.jdbc;
import java.sql.*;
import net.java_school.dbcp.*;
public class GetEmp {
public static void main(String[] args) {
ConnectionManager conMgr = new OracleConnectionManager();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String query = "SELECT * FROM emp";
try {
conn = conMgr.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
String empno = rs.getString(1);
String ename = rs.getString(2);
String job = rs.getString(3);
String mgr = rs.getString(4);
String hiredate = rs.getString(5);
String sal = rs.getString(6);
String comm = rs.getString(7);
String depno = rs.getString(8);
System.out.println(empno + " : " + ename + " : " + job + " : " + mgr
+ " : " + hiredate + " : " + sal + " : " + comm + " : " + depno);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {}
}
}
}
References