In the examples given in the previous post Java JDBC Steps to Connect to DB we explored how to establish a connection using DriverManager class. That’s ok for sample code where you just need to test using a connection and close it. But in real‑world applications, creating a new connection object for every database interaction is resource‑intensive and slows down performance.
For production‑grade applications, the recommended approach is to use connection pooling. Instead of repeatedly opening and closing connections, a pool maintains a set of pre‑initialized connections that can be reused whenever needed. This dramatically improves scalability and response times.
That’s where DataSource in Java comes in. A DataSource object not only supports connection pooling but also provides a cleaner, more flexible way to manage database connectivity. There are other advantages of using DataSource in JDBC too.
Advantages of using DataSource in Java
- Layer of abstraction– In an enterprise application you can configure JDBC DataSource in the application server and register it with JNDI. Developers then only need the logical JNDI name, while DB specific details remain hidden.
- Connection pooling- If the pool has an available connection, that connection is given to the
application. If all connections with in the pool are in use, a new connection is created and returned to the application.
Once the application finishes its work, the connection is returned to the pool for reuse. The connection is then available for the next connection request.
By pooling the connection you can reuse the connection objects rather then creating it every time. This reduces the overhead of repeatedly creating connections which is costly in terms of both time and system resources.
- Performance Boost – By reusing connections, database‑intensive applications achieve faster throughput and better resource utilization.
- Distributed transaction– DataSource implementations can also provide a connection object that can be used in distributed transactions. A distributed transaction allows a single transaction to span multiple DBMS servers.
DataSource interface in Java
The JDBC API provides an interface DataSource that has to be implemented by vendor specific DataSource implementations. DataSource interface is part of javax.sql package. The interface exposes two key methods:
- getConnection()
- getConnection(String username, String password)
Both of these methods return a Connection object.
DataSource Implementations
A JDBC driver should include at least a basic DataSource implementation. For example MySQL DB JDBC driver includes the implementation com.mysql.jdbc.jdbc2.optional.MysqlDataSource and Oracle DB’s implementation is oracle.jdbc.pool.OracleDataSource.
JDBC DataSource Examples
Let’s see some examples of DataSource in use. First let us see one example with MySQL DataSource. Though people prefer to use one of the connection pooling library Apache DBCP or mchange c3p0 atleast with stand alone Java programs, we’ll see example of these too.
MySQL DataSource Example
Here we have a DataSource class that is a singleton class giving the instance of MysqlDataSource. Also the schema in MySQL is netjs and table is Employee. You should have the mysql-connector jar in your class path.
There is another class DSConnection where we get the instance of MysqlDataSource and use it to get the Connection object.
Then a PreparedStatement object is created with a Select SQL statement to get Employee data having the passed ID.
DataSource.java
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
public class DataSource {
private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
private static final String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/netjs";
private static final String DB_USER = "root";
private static final String DB_PWD = "admin";
private static DataSource ds;
private MysqlDataSource mySqlDS = new MysqlDataSource();
//private constructor
private DataSource(){
//mySqlDS.setDriverClassName(DRIVER_CLASS);
mySqlDS.setUrl(DB_CONNECTION_URL);
mySqlDS.setUser(DB_USER);
mySqlDS.setPassword(DB_PWD);
}
/**
*static method for getting instance.
*/
public static DataSource getInstance(){
if(ds == null){
ds = new DataSource();
}
return ds;
}
public MysqlDataSource getMySqlDS() {
return mySqlDS;
}
public void setMySqlDS(MysqlDataSource mySqlDS) {
this.mySqlDS = mySqlDS;
}
}
DSConnection.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
public class DSConnection {
public static void main(String[] args) {
DSConnection dsCon = new DSConnection();
try {
dsCon.displayEmployee(6);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @param connection
* @param id
* @throws SQLException
*/
private void displayEmployee(int id) throws SQLException{
Connection connection = null;
String selectSQL = "Select * from employee where id = ?";
PreparedStatement prepStmt = null;
try {
MysqlDataSource basicDS = DataSource.getInstance().getMySqlDS();
connection = basicDS.getConnection();
prepStmt = connection.prepareStatement(selectSQL);
prepStmt.setInt(1, id);
ResultSet rs = prepStmt.executeQuery();
while(rs.next()){
System.out.println("id : " + rs.getInt("id") + " Name : "
+ rs.getString("name") + " Age : " + rs.getInt("age"));
}
}finally{
if(prepStmt != null){
prepStmt.close();
}
if(connection != null){
connection.close();
}
}
}
}
DataSource example Using Apache DBCP
With stand alone Java programs where data source is needed it is more convenient to use connection pooling library like DBCP.
You need the following jars in your project’s classpath (Download path- https://commons.apache.org/proper/commons-dbcp/), check the versions as per your Java and DB versions.
commons-dbcp2-2.1.1.jar commons-pool2-2.5.0.jar commons-logging-1.2.jar
and the JDBC driver for the DB used. In this example MySQL is used so mysql-connector-java-5.1.39 jar is used.
Here we have a DataSource class that is a singleton class creating and returning the instance of dbcp2 BasicDataSource.
There is another class DSConnection where we get the instance of dbcp2 BasicDataSource and use it to get the Connection object.
Then a PreparedStatement object is created with a Select SQL statement to get Employee data having the passed ID.
DB schema in MySQL is netjs and table is Employee.
DataSource.java
import org.apache.commons.dbcp2.BasicDataSource;
public class DataSource {
private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
private static final String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/netjs";
private static final String DB_USER = "root";
private static final String DB_PWD = "admin";
private static DataSource ds;
private BasicDataSource basicDS = new BasicDataSource();
//private constructor
private DataSource(){
//BasicDataSource basicDS = new BasicDataSource();
basicDS.setDriverClassName(DRIVER_CLASS);
basicDS.setUrl(DB_CONNECTION_URL);
basicDS.setUsername(DB_USER);
basicDS.setPassword(DB_PWD);
// Parameters for connection pooling
basicDS.setInitialSize(10);
basicDS.setMaxTotal(10);
}
/**
*static method for getting instance.
*/
public static DataSource getInstance(){
if(ds == null){
ds = new DataSource();
}
return ds;
}
public BasicDataSource getBasicDS() {
return basicDS;
}
public void setBasicDS(BasicDataSource basicDS) {
this.basicDS = basicDS;
}
}
DSConnection.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbcp2.BasicDataSource;
public class DSConnection {
public static void main(String[] args) {
DSConnection dsCon = new DSConnection();
try {
dsCon.displayEmployee(6);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @param connection
* @param id
* @throws SQLException
*/
private void displayEmployee(int id) throws SQLException{
Connection connection = null;
String selectSQL = "Select * from employee where id = ?";
PreparedStatement prepStmt = null;
try {
BasicDataSource basicDS = DataSource.getInstance().getBasicDS();
connection = basicDS.getConnection();
prepStmt = connection.prepareStatement(selectSQL);
prepStmt.setInt(1, id);
ResultSet rs = prepStmt.executeQuery();
while(rs.next()){
System.out.println("id : " + rs.getInt("id") + " Name : "
+ rs.getString("name") + " Age : " + rs.getInt("age"));
}
}finally{
if(prepStmt != null){
prepStmt.close();
}
if(connection != null){
connection.close();
}
}
}
}
DataSource example Using c3p0
Another way of getting DataSource object is using c3p0 library. With stand alone Java program you can create an instance of ComboPooledDataSource.
Jars used
c3p0-0.9.5.2.jar mchange-commons-java-0.2.11.jar
If we retain the same class structure as explained above. Now the classes would look like -
DataSource.java
import java.beans.PropertyVetoException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DataSource {
private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
private static final String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/netjs";
private static final String DB_USER = "root";
private static final String DB_PWD = "admin";
private static DataSource ds;
private ComboPooledDataSource cpds = new ComboPooledDataSource();
//private constructor
private DataSource() throws PropertyVetoException{
cpds.setDriverClass(DRIVER_CLASS); //loads the jdbc driver
cpds.setJdbcUrl(DB_CONNECTION_URL);
cpds.setUser(DB_USER);
cpds.setPassword(DB_PWD);
// the settings below are optional
// c3p0 can work with defaults
cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);
}
/**
*Static method for getting instance.
* @throws PropertyVetoException
*/
public static DataSource getInstance() throws PropertyVetoException{
if(ds == null){
ds = new DataSource();
}
return ds;
}
public ComboPooledDataSource getCpds() {
return cpds;
}
public void setCpds(ComboPooledDataSource cpds) {
this.cpds = cpds;
}
}
DSConnection.java
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DSConnection {
public static void main(String[] args) throws PropertyVetoException {
DSConnection dsCon = new DSConnection();
try {
dsCon.displayEmployee(6);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @param connection
* @param id
* @throws SQLException
* @throws PropertyVetoException
*/
private void displayEmployee(int id) throws SQLException, PropertyVetoException{
Connection connection = null;
String selectSQL = "Select * from employee where id = ?";
PreparedStatement prepStmt = null;
try {
ComboPooledDataSource basicDS = DataSource.getInstance().getCpds();
connection = basicDS.getConnection();
prepStmt = connection.prepareStatement(selectSQL);
prepStmt.setInt(1, id);
ResultSet rs = prepStmt.executeQuery();
while(rs.next()){
System.out.println("id : " + rs.getInt("id") + " Name : "
+ rs.getString("name") + " Age : " + rs.getInt("age"));
}
}finally{
if(prepStmt != null){
prepStmt.close();
}
if(connection != null){
connection.close();
}
}
}
}
That's all for this topic DataSource in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Java Advanced Tutorial Page
Related Topics
You may also like-