Connecting MyBatis ORM to C3P0 Connection Pooling

In this example, im trying to connecting my MyBatis application to my database through C3P0 connection pooling. According to its website, C3P0 is an easy-to-use library for making traditional JDBC drivers “enterprise-ready” by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2.

Okay, so the first thing i do is, creating a database named “Test” and table “Testing”

CREATE DATABASE `Test`;
USE `Test`;
CREATE TABLE `testing` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `address` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ix` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

insert into `table`(`Id`,`name`,`address`) values (1,'edw','Ciledug');
insert into `table`(`Id`,`name`,`address`) values (2,'kamplenk','Cikokol');
insert into `table`(`Id`,`name`,`address`) values (3,'nugie','Pamulang');
insert into `table`(`Id`,`name`,`address`) values (4,'tebek','Karawaci');

And a java bean class to represent my sql table,

package com.edw.bean;

public class Testing {

    private int id;
    private String name;
    private String address;

    public Testing() {
    }

    // setter and getter method

    @Override
    public String toString() {
        return "Testing{" + "id=" + id + ", name=" + name + ", address=" + address + '}';
    }
}

and dont forget, a java interface class to handle my query

package com.edw.mapper;

import com.edw.bean.Testing;
import java.util.List;
import org.apache.ibatis.annotations.Select;
 
public interface TestingMapper {
    @Select("SELECT * FROM testing")
    List<Testing> selectAll();
}

Next is creating c3p0 connection configuration on context.xml file under META-INF folder

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/MyBatisC3P0">
	
    <Resource name="testjndi" 
		 auth="Container"
		 driverClass="com.mysql.jdbc.Driver"
         jdbcUrl="jdbc:mysql://localhost:3306/test"
         user="root"
         password="xxxx"					 		 
         factory="org.apache.naming.factory.BeanFactory" 
         type="com.mchange.v2.c3p0.ComboPooledDataSource" 
         maxPoolSize="30" 					 
         minPoolSize="10" 
         acquireIncrement="3" 
         acquireRetryAttempts = "0"
         acquireRetryDelay = "3000"
         breakAfterAcquireFailure = "false"
         maxConnectionAge = "20"
         maxIdleTime = "15"
         maxIdleTimeExcessConnections = "15"
         idleConnectionTestPeriod = "10"
         testConnectionOnCheckout = "true"					 
         preferredTestQuery = "SELECT 1"
         debugUnreturnedConnectionStackTraces = "true"					 
         autoCommitOnClose="true"
        />	
</Context>

next is registering your JNDI on web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
    <servlet>
        <servlet-name>myBatisServlet</servlet-name>
        <servlet-class>com.edw.servlet.MyBatisServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>myBatisServlet</servlet-name>
        <url-pattern>/myBatisServlet</url-pattern>
    </servlet-mapping>
    <welcome-file-list>
        <welcome-file>myBatisServlet</welcome-file>
    </welcome-file-list>
    <resource-ref>
        <description>My Connection Pool</description>
        <res-ref-name>testjndi</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
</web-app>

and calling your connection pooling jndi from MyBatis’ xml code

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
       <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="JNDI">
                <property name="data_source" value="java:/comp/env/testjndi"/>
            </dataSource>
        </environment>          
    </environments>
    <mappers />
</configuration>

next is creating a java class to load my MyBatis’ xml configuration

package com.edw.config;

import com.edw.mapper.TestingMapper;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisSqlSessionFactory {

    private static final SqlSessionFactory FACTORY;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/sqlmap/Configuration.xml");
            FACTORY = new SqlSessionFactoryBuilder().build(reader);
            FACTORY.getConfiguration().addMapper(TestingMapper.class);
        } catch (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return FACTORY;
    }
}

and finally, i create a servlet as my presentation layer

package com.edw.servlet;

import com.edw.bean.Testing;
import com.edw.config.MyBatisSqlSessionFactory;
import com.edw.mapper.TestingMapper;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.ibatis.session.SqlSession;

public class MyBatisServlet extends HttpServlet {

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true);
        try {
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet MyBatisServlet</title>");
            out.println("</head>");
            out.println("<body>");

            TestingMapper testingMapper = sqlSession.getMapper(TestingMapper.class);
            List<Testing> testings = testingMapper.selectAll();
            for (Testing testing : testings) {
                out.println(testing.getName()+" - "+testing.getAddress()+"<br />");
            }
            
            out.println("</body>");
            out.println("</html>");
        } finally {
            sqlSession.close();
            out.flush();            
            out.close();            
        }
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }
}

This is what the result of my servlet when im running it on my browser

This is the screenshot of my database connection, you can see that c3p0 automatically create 10 free connections.

And this is the screenshot of my netbeans project

Have fun with C3P0 and MyBatis (H)