DataSource - Connection Pooling

Datasource is an interface provided by JDBC. Datasource is the preferred alternative to DriverManager for establishing a connection to data base.
They are similar to the extent that the DriverManager class and DataSource interface both have methods for creating a connection, methods for getting and setting a timeout limit for making a connection, and methods for getting and setting a stream for logging.The advantage of DataSource is features like connection pooling and distributed transactions. Connection pooling can increase performance dramatically by reusing connections rather than creating a new physical connection each time a connection is requested. The ability to use distributed transactions enables an application to do the heavy duty database work of large enterprises.
AS I mentioned, Datasource is an Interface, it's actual implementation depends on vendor. A common and popular Datasource implementation is Apacha DBCP.
Datasource can be configured in two ways,

• Configure With Java Class
• configure With Server

We will discuss both ways.

• Configure With Java Class

********************************************************
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory;

public class DBPool {
private static volatile DataSource dsObj;
/**
* Initialize Data Source
*/
private static void initialize() {
// Create Property 
Properties dsProperties = new Properties();
dsProperties.setProperty("url", "jdbc:oracle:thin:@localhost:1521:xe");
dsProperties.setProperty("driverClassName",
"oracle.jdbc.driver.OracleDriver");
dsProperties.setProperty("username", "scott");
dsProperties.setProperty("password", "tiger");
dsProperties.setProperty("maxActive", "100");
dsProperties.setProperty("minIdle", "0");
dsProperties.setProperty("maxIdle", "10");
dsProperties.setProperty("maxWait", "10000");
try {dsObj = BasicDataSourceFactory.createDataSource(dsProperties);} catch (Exception e) {
e.printStackTrace();
}
}

/**
* Returns DB Connection
* @return Connection
* @throws SQLException
*/
public static Connection getConnectionFromPool() throws SQLException {
Connection connection = null;
// checking for null singleton instance
if (null == dsObj) { // synchronized over class to make thread safe
synchronized (DBPool.class) {
// double checking for making singleton instance thread safe
if (null == dsObj) {
initialize();
}
}}
// getting connection from data sourceconnection = dsObj.getConnection();
return connection;
}

/**
* Method to close the connection, Statement and Result Set
* @param conection
* @param statement
* @param resultset
*/
public static void closeConnection(Connection conection,
Statement statement, ResultSet resultset) {
try {
if (null != statement)
statement.close();
if (null != resultset)
resultset.close();
if (null != conection)
conection.close();

} catch (SQLException sqle1) {
sqle1.printStackTrace();
}
}
}
********************************************************
Code Explanation:
Configuring Datasource is pretty easy with DBCP. You just need to create a property object and set some db specific values to that property object. Now just call createDataSource() of BasicDataSourceFactory and pass that property object as arugment. This method will return your data source object.Connection object is obtained by calling getConnection() of data source instance. Datasource is usually kept as singleton object so make sure this in your design. I have made it singleton in DBPool.getConnectionFromPool(), and this method also returns the connection obtained from datasource.
You can better use code snippet given in expample in your application to save time and efforts.

Connection Pooling:Connection pools are used to enhance the performance of executing commands on a database . In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time as user don't have to wait for creating connection to the database.
If you are using DBCP (or any other) data source, you don't need to do anything for connection pooling. data source makes connection pool for you. You just need to set few properties when you configure datasource and call getConnection() to get a connection from connection pool.
initialSize : The initial number of connections that are created when the pool is started.
maxActive : Number of maximum active connection in pool.
maxWait : Max time for that pool will wait before throwing exception when there is no available connection. -1 for indefinitely.
maxIdle : The maximum number of connections that can remain idle in the pool, without extra ones being released.
minIdle : The minimum number of connections that can remain idle in the pool, without extra ones being created.

That's all, And now you know how to configure datasource and connection pool so raise your confidence and enjoy working...

• Configure Datasource in Server

Configuring data source with server is much easier than configuring it with Java class. You just need to copy some small XML blow in you server's(say Tomcat) conf/context.xml file.

********************************************************

< Resource auth="Container" driverClassName="net.sourceforge.jtds.jdbc.Driver" logAbandoned="true" maxActive="100" maxIdle="10" maxWait="10000" name="jdbc/MyDataSource" password="dbpassword" removeAbandoned="true" removeAbandonedTimeout="60" type="javax.sql.DataSource" url="jdbc:jtds:sqlserver:dbhost:11521/dbname" username="dbuser" validationQuery="Select 1" />

*******************************************************
If you look carefully, we are declaring same attribute names in xml as we were doing in property file, like driverClassName, url, username, password, maxActive, maxIdle, maxWait etc...

Above xml is configured for MS-SQL DB . You just need specify your db details in these attributes. Container or Server will create a datasource and bind it with JNDI with name provided in "name" attribute. We can lookup for this name in our java class to get datasource instance,
****************************************************************
try
{
InitialContext ictx = new InitialContext();
DataSource dsObj = (DataSource) ictx.lookup("java:comp/env/jdbc/MyDatasource");
}
catch (Exception e) {
e.printStackTrace();
}
}

*****************************************************

Here, we are looking for datasource name ie jdbc/MyDatasource. You might be thinking why we placed "java:comp/env" before "jdbc/MyDatasource". Well, this is Sun's standard notation for JNDI names to avoid conflicts, but this is not our point of discussion here. you just make a not that we need to use "java:comp/env" prefix with datasource lookup name.

So lookup method will return datasource instance bind with jndi name and we can use this datasource instance in our application.

There are several advantage of configuring datasource in server like you don't need to change your Java code when there is a change in datasource or database, just change context.xml,

Another advantage is, you don't need to manually make datasource instance singleton as only one instance is managed by server. You can lookup() this instance whenever needed in your application.

Thats all about Datasource...Enjoy happy programming,

In case of any query, feel free to contact me mohit.amour@gmail.com

Thanks,
The Interpreter