This post shows how to provide JDBC connection pooling using C3P0 data source in Spring framework. DB used in this example is MySQL.
Maven dependency for C3P0
<dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.2</version> </dependency>Alternatively you can download the following jars and put them in the application’s classpath.
c3p0-0.9.5.2.jar mchange-commons-java-0.2.11.jar
C3P0 Connection pooling Spring example
For configuring datasource you need to set up some properties. It is better to use a properties file for storing those properties and refer that properties file while configuring datasource.
db.properties
db.driverClassName=com.mysql.jdbc.Driver db.url=jdbc:mysql://localhost:3306/netjs db.username=user db.password=password pool.initialSize=5
Description for the properties used here is as-
driver class name is the JDBC driver for the DB used. Since MYSQL is used here so the jdbc driver for the same (com.mysql.jdbc.Driver) is provided.
Url – You need to provide url to access your DB server. I have created a schema called netjs and DB is running on the same system so url is- jdbc:mysql://localhost:3306/netjs.
Username and password for the DB.
IntialSize is the initial size of the connection pool. It is given as 5 so initially 5 connections will be created and stored in the pool.
In this example Spring JDBCTemplate is used to query the DB. If you are using Spring XML configuration then configuration for DataSource and JDBCTemplate is as follows.
Spring XML Configuration (appContext.xml)
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<context:component-scan base-package="org.netjs.daoimpl" />
<!-- For reading properties files -->
<context:property-placeholder location="classpath:config/db.properties" />
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value = "${db.driverClassName}" />
<property name="jdbcUrl" value = "${db.url}" />
<property name="user" value = "${db.username}" />
<property name="password" value = "${db.password}" />
<property name="initialPoolSize" value = "${pool.initialSize}" />
</bean>
</beans>
In this XML configuration, <context:property-placeholder> tag is used to give the path to db.properties file.
For C3P0, datasource implementing class is “com.mchange.v2.c3p0.ComboPooledDataSource”.
DataSource bean has to be provided as a reference in JDBCTemplate.
DBTable
Database table used in this example.
CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(35) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
Java classes
EmployeeDAO interface
public interface EmployeeDAO {
public List<Employee> findAllEmployees();
}
EmployeeDAOImpl.java
@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public List<Employee> findAllEmployees() {
final String SELECT_ALL_QUERY = "SELECT * from EMPLOYEE";
return this.jdbcTemplate.query(SELECT_ALL_QUERY, new EmployeeMapper());
}
private static final class EmployeeMapper implements RowMapper<Employee> {
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee emp = new Employee();
emp.setEmpId(rs.getInt("id"));
emp.setEmpName(rs.getString("name"));
emp.setAge(rs.getInt("age"));
return emp;
}
}
}
Employee.java
public class Employee {
private int empId;
private String empName;
private int age;
public int getEmpId() {
return empId;
}
public void setEmpId(int empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
You can run this example using the following code.
public class App {
public static void main(String[] args) {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext
("appcontext.xml");
EmployeeDAO dao = (EmployeeDAO)context.getBean("employeeDAOImpl");
List<Employee> empList = dao.findAllEmployees();
System.out.println("Name - "+ empList.get(0).getEmpName() +
" Age - " + empList.get(0).getAge());
context.registerShutdownHook();
}
}
C3P0 datasource with Spring Java config
If you want to use Spring Java Configuration then you can create an object of ComboPooledDataSource and set the properties.
@Bean
public ComboPooledDataSource comboPooledDataSource(){
ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/netjs");
..
..
return ds;
}
That's all for this topic Connection Pooling Using C3P0 Spring Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Spring Tutorial Page
Related Topics
You may also like-