C3P0 Connection Pooling Example

C3P0 Connection Pooling Example exaplains about how to create and configure a Connection pool using C3P0 Datasource

Creating and establishing a database connections are relatively very expensive because of establishing a network connection, initializng database session, authorization in the back end database etc.

Due to this issues, it is good practice to use a connection pool in your applications in order to increase the performance and scalability. By using Connection Pool, you can re-use already existing connections and prepared statements, so that you can avoid the cost of establishing the connection.

C3P0 is an easy-to-use library for augmenting traditional (DriverManager-based) JDBC drivers with JNDI-bindable DataSources, including DataSources that implement Connection and Statement Pooling, as described by the jdbc3 spec and jdbc2 std extension.

Reference -> http://sourceforge.net/projects/c3p0/

Note

Different Connection Pool Implementation examples

1)    DBCP Connection Pooling Example
2)    C3P0 Connection Pooling Example
3)    BoneCP Connection Pooling Example
4)    H2 Database Connection Pool Example
Required Libraries

You need to download

  1. C3P0
  2. mchange-commons-java-0.2.3.4.jar

Following jar must be in classpath

  1. c3p0-0.9.2.1.jar
  2. mchange-commons-java-0.2.3.4.jar
  3. mysql-connector-java-5.1.28-bin.jar

Create Table Structure

CREATE TABLE `employee` (
  `EMPLOYEEID` bigint(20) NOT NULL AUTO_INCREMENT,
  `EMPLOYEENAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEEID`)
);

Project Structure

C3P0 Connection Pooling Example

DataSource.java

package com.dataSource.c3p0;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSource {

   
private static DataSource     datasource;
   
private ComboPooledDataSource cpds;

   
private DataSource() throws IOException, SQLException, PropertyVetoException {
       
cpds = new ComboPooledDataSource();
        cpds.setDriverClass
("com.mysql.jdbc.Driver"); //loads the jdbc driver
       
cpds.setJdbcUrl("jdbc:mysql://localhost/test");
        cpds.setUser
("root");
        cpds.setPassword
("root");

       
// the settings below are optional -- c3p0 can work with defaults
       
cpds.setMinPoolSize(5);
        cpds.setAcquireIncrement
(5);
        cpds.setMaxPoolSize
(20);
        cpds.setMaxStatements
(180);

   
}

   
public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
       
if (datasource == null) {
           
datasource = new DataSource();
           
return datasource;
       
} else {
           
return datasource;
       
}
    }

   
public Connection getConnection() throws SQLException {
       
return this.cpds.getConnection();
   
}

}

C3P0DataSourceExample.java

package com.dataSource.c3p0;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class C3P0DataSourceExample {

   
public static void main(String[] args) throws PropertyVetoException, SQLException, IOException {
       
Connection connection = null;
        Statement statement =
null;
        ResultSet resultSet =
null;
       
try {
           
connection = DataSource.getInstance().getConnection();
            statement = connection.createStatement
();
            resultSet = statement.executeQuery
("select * from employee");
             
while (resultSet.next()) {
                 
System.out.println("employeeid: " + resultSet.getString("employeeid"));
                  System.out.println
("employeename: " + resultSet.getString("employeename"));
             
}
        }
catch (SQLException e) {
           
e.printStackTrace();
       
} finally {
           
if (resultSet != null) try { resultSet.close(); } catch (SQLException e) {e.printStackTrace();}
           
if (statement != null) try { statement.close(); } catch (SQLException e) {e.printStackTrace();}
           
if (connection != null) try { connection.close(); } catch (SQLException e) {e.printStackTrace();}
        }
    }
}
Output
employeeId: 1
employeename: Rockey
employeeId: 2
employeename: Jose

 











5 Responses to "C3P0 Connection Pooling Example"
  1. felansu 2013-12-21 09:59:12.0
  1. Max 2013-12-22 09:59:12.0
  1. Minh 2013-12-23 09:59:12.0
  1. Leo 2013-12-24 09:59:12.0
  1. Sergio 2018-03-19 05:03:05.0

Your email address will not be published. Required fields are marked *