Java

This time im trying to change my programming style, from a simple JDBC connection to a bit more complicated JDBC Connection Pool so i could increase my application’s response time. Btw, what is a Connection Pools? Basically, it’s a cached connections that are kept in a runtime object pool and can be used and reused as needed by the application. For a more complete explanation, you can check here.

Im planning to deploy my application on a Glassfish v3 and change my iBatis’ setting from simple JDBC to JNDI connection, but first i have create a MySql DataSource on m Glassfish. In order to create a connection pool, i put my MySQL JDBC Jar in <glassfish installation folder>/lib.

From my Glassfish console (http://localhost:4848/), i create a new Connection pools,

Start by giving its name, and selecting resource and database vendor type,

Connection type,

Dont forget adding these properties, so you can connect to your MySql Database,

you can test your connection by pinging it,

Btw, if you cant find the which resource type you need, here are some hints

1. javax.sql.DataSource: is a non-pooled direct connection
2. javax.sql.ConnectionPoolDataSource: connection is coming from pool
3. javax.sql.XADataSource: is for distributed transactions

Create a new JDBC Resources using your new Connection Pool,

Next step is creating a simple web application using NetBeans 6.9. As usual, i create a simple table

CREATE DATABASE 'test'
USE 'test'

CREATE TABLE `contoh` (
  `nama` varchar(30) NOT NULL DEFAULT '',
  `alamat` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`nama`)
)

insert into contoh (nama, alamat) values ('edwin', 'jakarta');
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', 'jayapura');

next step is creating a java bean for database mapping

package com.edw.bean;

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

    private String nama;
    private String alamat;

    public Contoh(String nama, String alamat) {
        this.nama = nama;
        this.alamat = alamat;
    }

    public Contoh() {
    }

    public String getAlamat() {
        return alamat;
    }

    public void setAlamat(String alamat) {
        this.alamat = alamat;
    }

    public String getNama() {
        return nama;
    }

    public void setNama(String nama) {
        this.nama = nama;
    }

    @Override
    public String toString() {
        return "Contoh{" + "nama=" + nama + " and alamat=" + alamat + '}';
    }        
}

and an xml mapping for database queries, i named it contoh.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="contoh" >

    <!--alias-->
    <typeAlias alias="Contoh" type="com.edw.bean.Contoh" />

    <resultMap id="result" class="Contoh">
        <result property="nama" column="NAMA" columnIndex="1"/>
        <result property="alamat" column="ALAMAT" columnIndex="2"/>
    </resultMap>

    <insert id="insertContoh" parameterClass="Contoh" >
    insert into contoh (nama, alamat)
    values (#nama:VARCHAR#, #alamat:VARCHAR#)
    </insert>

    <!--query select all-->
    <select id="selectAllContoh" resultMap="result">
    select nama, alamat
    from contoh
    </select>

    <!--query with Bean parameter-->
    <select id="selectContohWithPKfromBean" resultClass="Contoh" parameterClass="Contoh">
    select nama, alamat
    from contoh
    where nama like #nama:VARCHAR#
    </select>

    <!--query with String parameter-->
    <select id="selectContohWithPKfromString" resultClass="Contoh" parameterClass="java.lang.String">
    select nama, alamat
    from contoh
    where nama like #nama#
    </select>

    <!--query with Map parameter-->
    <select id="selectContohWithPKfromMap" resultClass="Contoh" parameterClass="java.util.Map">
    select nama, alamat
    from contoh
    where nama like #nama#
    </select>

</sqlMap>

and ibatis’ main xml configuration, sqlmapconfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <settings
        useStatementNamespaces="true"
        lazyLoadingEnabled="true"
        enhancementEnabled="true"
        />

    <transactionManager type="JDBC" commitRequired="false">
        <dataSource type="JNDI">
            <property name="DataSource" value="mysqljndi"/>
        </dataSource>
    </transactionManager>

    <sqlMap resource="com/edw/sqlmap/contoh.xml"/>
</sqlMapConfig>

next step is creating a simple java class to load all iBatis’ configuration file.

package com.edw.config;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.IOException;
import java.io.Reader;


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

    protected static final SqlMapClient sqlMap;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/sqlmap/sqlmapconfig.xml");
            sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
        } catch (IOException e) {
            throw new RuntimeException("Fatal Error, ga dapet sqlmapconfignya.  Cause: " + e, e);
        } catch (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

    public static SqlMapClient getSqlMap() {
        return sqlMap;
    }
}

a simple Servlet

package com.edw.servlet;

import com.edw.bean.Contoh;
import com.edw.config.SqlMapConfig;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;

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

    private Logger logger = Logger.getLogger(this.getClass());

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
          
            out.println("<html>");
            out.println("<head>");
            out.println("<title>iBatis Test</title>");
            out.println("</head>");
            out.println("<body>");

            out.println("-----------------------");
            out.println("select all <br />");

            List<Contoh> contohs = SqlMapConfig.getSqlMap().queryForList("contoh.selectAllContoh");
            for (Contoh contoh : contohs) {
                out.println(contoh+"<br />");
            }

            out.println("<br />");
            out.println("<br />");
            
            out.println("-----------------------");
            out.println("select with Parameter Bean");
            out.println("<br />"+SqlMapConfig.getSqlMap().queryForObject("contoh.selectContohWithPKfromBean", new Contoh("samsu", null))+"<br /><br />");

            out.println("-----------------------");
            out.println("select with Parameter String");
            out.println("<br />"+SqlMapConfig.getSqlMap().queryForObject("contoh.selectContohWithPKfromString", "kamplenk")+"<br /><br />");

            out.println("-----------------------");
            out.println("select with Parameter Map");
            Map<String, String> map = new HashMap<String, String>();
            map.put("nama", "tebek");
            out.println("<br />"+SqlMapConfig.getSqlMap().queryForObject("contoh.selectContohWithPKfromMap", map)+"<br /><br />");
            
            out.println("</body>");
            out.println("</html>");

        } catch(Exception ex){
            logger.error(ex.getMessage(),ex);
        } 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);
    }
  
    @Override
    public String getServletInfo() {
        return "My Main Servlet";
    }
}

and my web.xml configuration

<?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">
    <servlet>
        <servlet-name>Main</servlet-name>
        <servlet-class>com.edw.servlet.Main</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>Main</servlet-name>
        <url-pattern>/Main</url-pattern>
    </servlet-mapping>
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    <welcome-file-list>
        <welcome-file>Main</welcome-file>
    </welcome-file-list>
</web-app>

this is the result on my browser when i run my application

this is my netbeans project structure

Again, i hope this simple tutorial could be useful for others. Cheers (B)
nb. im using Netbeans 6.9, iBatis 2.3.4 and Glassfish 3.