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/
Required Libraries
You need to download
Following jar must be in classpath
- c3p0-0.9.2.1.jar
- mchange-commons-java-0.2.3.4.jar
- 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
DataSource.java
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
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