Programming

In this tutorial, im trying to connect a servlet to mysql database using Apache DBCP. Im using mybatis and accessing Apache DBCP’s datasource via JNDI. Btw, im using Netbeans 6.9 and Apache Tomcat 6.

First as always, a table named “contoh” on a database “test”

CREATE
    TABLE contoh
    (
        nama VARCHAR(30) NOT NULL,
        alamat VARCHAR(100),
        PRIMARY KEY (nama)
    )

insert into contoh (nama, alamat) values ('edwin', 'singapore');
insert into contoh (nama, alamat) values ('kamplenk', 'ciledug');
insert into contoh (nama, alamat) values ('nugie', 'pamulang');
insert into contoh (nama, alamat) values ('samsu', 'dago');
insert into contoh (nama, alamat) values ('tebek', 'karawaci');	

and a simple java bean

package com.edw.mybatis.bean;

import java.io.Serializable;

public class Contoh implements Serializable {
    private String nama;
    private String alamat;

	// other setter and getter
	
    @Override
    public String toString(){
        return nama+" : "+alamat;
    }
}

next is creating a simple xml query, ContohMapper.xml.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.edw.mybatis.mapper.ContohMapper" >

    <resultMap id="ContohMap" type="com.edw.mybatis.bean.Contoh" >
        <id column="nama" property="nama" jdbcType="VARCHAR" />
        <result column="alamat" property="alamat" jdbcType="VARCHAR" />
    </resultMap>
    

    <select id="selectAll" resultMap="ContohMap">
        SELECT * FROM contoh
    </select>

</mapper>

and an interface class to map my queries

package com.edw.mybatis.mapper;

import com.edw.mybatis.bean.Contoh;
import java.util.List;

public interface ContohMapper {    
    List<Contoh> selectAll();
}

Dont forget to create a connection pooling configuration on context.xml, which located under META-INF folder.

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/MyBatisDBCP">
    
    <Resource name="jdbc/test" auth="Container"
            type="javax.sql.DataSource"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/test"
            username="root"
            password="xxxx"
            maxActive="100" maxIdle="30" maxWait="10000"
            removeAbandoned="true"
            removeAbandonedTimeout="60"
            logAbandoned="true"
            />
            
</Context>

an xml configuration, Configuration.xml. This is where i put my connection properties.

<?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/jdbc/test"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/edw/mybatis/xml/ContohMapper.xml" />
    </mappers>
</configuration>

and a singleton class to load my configuration class,

package com.edw.mybatis.config;

import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;


/**
 *
 * @author edw
 */
public class MyBatisSqlSessionFactory {

    protected static final SqlSessionFactory FACTORY;

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

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

and a simple servlet to call mybatis query

package com.edw.servlet;

import com.edw.mybatis.bean.Contoh;
import com.edw.mybatis.config.MyBatisSqlSessionFactory;
import com.edw.mybatis.mapper.ContohMapper;
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;
import org.apache.log4j.Logger;

/**
 *
 * @author edw
 */
public class MainServlet extends HttpServlet {

    private Logger logger = Logger.getLogger(MainServlet.class);

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {

            SqlSession session = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession();

            ContohMapper contohMapper = session.getMapper(ContohMapper.class);
            List<Contoh> contohs = contohMapper.selectAll();

            out.println("<html>");
            out.println("<head>");
            out.println("<title>DBCP Example MainServlet</title>");
            out.println("</head>");
            out.println("<body>");
            
            for (Contoh contoh : contohs) {
                out.println(contoh.getNama()+" "+contoh.getAlamat()+"<br />");
                logger.debug(contoh.getNama()+" "+contoh.getAlamat());
            }

            out.println("</body>");
            out.println("</html>");

        }catch(Exception ex){
            logger.error(ex.getMessage(), ex);
            out.println(ex.getMessage());
        } finally {
            out.close();
        }
    } 

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

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

and dont forget to register your jndi and servlet to web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" 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_2_5.xsd">

    <!-- Session -->
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>

    <!-- Welcome file -->
    <welcome-file-list>
        <welcome-file>mainServlet</welcome-file>
    </welcome-file-list>

    <!--  Servlet -->
    <servlet>
        <servlet-name>mainServlet</servlet-name>
        <servlet-class>com.edw.servlet.MainServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>mainServlet</servlet-name>
        <url-pattern>/mainServlet</url-pattern>
    </servlet-mapping>

    <!-- JNDI -->
    <resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/test</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
    
</web-app>

And also, dont forget to put your mysql-connector.jar to tomcat lib. And if you see this error, Cannot create JDBC driver of class '' for connect URL 'null', it means there’s something wrong with your JNDI configuration on context.xml.

This is my netbeans project screenshot

and this is what the result will look like

Have fun, cheers. (B)