I was inspecting a customer web application when I came across a class that tried to implement simple connection pooling using DataSources. "That's not required", I thought, "You can use a DataSource and get the wonderful connection pooling along the way".
Wrong! Using a JDBC DataSource does not mean that you get connection pooling.
After reading the f...ing manuals of the Oracle JDBC drivers and the Java Doc I have learned that
- DataSource implementors can provide connection pooling
- Oracle provides connection pooling on JDBC level
- You have to turn on connection pooling
The following code explains how to turn on connection pooling:
package tvd.jdbc.tests;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import oracle.jdbc.pool.OracleDataSource;
public class DataSourcePoolingTest {
private static final String host = "localhost";
private static final String port = "1521";
private static final String sid = "ora10g";
private static final String user = "SCOTT";
private static final String pwd = "TIGER";
private static DataSource createDS() {
OracleDataSource ods = null;
try {
ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@//" + host + ":" + port + "/" + sid);
ods.setUser(user);
ods.setPassword(pwd);
ods.setConnectionCachingEnabled(true); // turns on connection pooling
Properties prop = new Properties();
prop.setProperty("InitialLimit", "2");
ods.setConnectionCacheProperties(prop);
ods.setConnectionCacheName("ImplicitCache01");
} catch (SQLException e) {
e.printStackTrace();
}
return ods;
}
public static void main(String[] args) {
DataSource ds = createDS();
Connection c1 = null;
Connection c2 = null;
Connection c3 = null;
try {
System.out.println("Open connections...");
c1 = ds.getConnection();
System.out.println("pause...");
System.in.read();
c2 = ds.getConnection();
c3 = ds.getConnection();
System.out.println("pause...");
System.in.read();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
System.out.println("Closing connections.");
c1.close();
c2.close();
c3.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Well, how do you know that your connection pool is working? You can count the number of open connections in the Oracle database, provided that there are no other clients.
SQL> select count(*) from v$session where username like 'SCOTT';
COUNT(*)
----------
3
Issue the statement during the first pause and the result is 2 because the connection pool is created with the first connection and the number of initially created connections is set with the property InitialLimit.
BTW, the web application is deployed on an Oracle App Server 10.1.3.3. The OAS has a great feature: Oracle DataSources uses connection pooling by default. At the end of the day, my first guess about connection pooling was not completely wrong. :-)