In this post we’ll see how to configure connection pooling using C3P0 datasource in your Java application. The DB we are connecting to is MySQL.
Jars needed for C3P0
You need the following jars in your project’s classpath, check the versions as per your Java and DB versions.
lib/c3p0-0.9.5.5.jar lib/mchange-commons-java-0.2.19.jar
Download path- https://sourceforge.net/projects/c3p0/
If you are using Maven then you can add the following dependency.
<dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.5</version> </dependency>
Connection pooling using C3P0 - Java Example
Properties file that is used to read DB configuration
resources/db.properties
DRIVER_CLASS=com.mysql.jdbc.Driver DB_CONNECTION_URL=jdbc:mysql://localhost:3306/netjs DB_USER=root DB_PWD=admin
In the Java example code for connection pooling using C3P0 there are two Java classes. We have a PooledDataSource class with a static block to create an instance of C3P0's ComboPooledDataSource.
There is another class DSConnection where we get the instance of ComboPooledDataSource and use it to get the Connection object.
PooledDataSource.java
import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class PooledDataSource {
private static ComboPooledDataSource cpds;
static {
try {
cpds = new ComboPooledDataSource();
Properties properties = new Properties();
// Loading properties file
InputStream inputStream = new FileInputStream("resources/db.properties");
properties.load(inputStream);
cpds.setDriverClass(properties.getProperty("DRIVER_CLASS")); //loads the jdbc driver
cpds.setJdbcUrl(properties.getProperty("DB_CONNECTION_URL"));
cpds.setUser(properties.getProperty("DB_USER"));
cpds.setPassword(properties.getProperty("DB_PWD"));
// the settings below are optional
// c3p0 can work with defaults
cpds.setInitialPoolSize(5);
cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);
}catch(IOException | PropertyVetoException e) {
e.printStackTrace();
}
}
public static javax.sql.DataSource getDataSource() {
return cpds;
}
}
In this class, apart from setting the DB properties, we have set some of the parameters for the connection pool like setMinPoolSize() that sets the initial size of the connection pool. These many connection will immediately be created and put to connection pool, setMaxPoolSize() to set the maximum limit on the connection pool.
DSConnection.java
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class DSConnection {
public static void main(String[] args) throws PropertyVetoException {
DSConnection dsCon = new DSConnection();
try {
dsCon.displayEmployee(37);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void displayEmployee(int id) throws SQLException{
Connection connection = null;
String selectSQL = "Select * from employee where id = ?";
PreparedStatement prepStmt = null;
try {
DataSource ds = PooledDataSource.getDataSource();
connection = ds.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 Connection Pooling Using C3P0 in Java. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Java Programs Page
Related Topics
You may also like-