ibatis Posts

A Simple Spring 4 and MyBatis Transaction Example

According to wikipedia, database transaction should provide an “all-or-nothing” proposition, stating that each work-unit performed in a database must either complete in its entirety or have no effect whatsoever. Further, the system must isolate each transaction from other transactions, results must conform to existing constraints in the database, and transactions that complete successfully must get written to durable storage.

So on this example, im trying to create a simple MyBatis database transaction, managed by Spring Framework. Okay, so this is my pom file, as you can see, im using Spring version 4.1.2.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.edw.springmybatis</groupId>
  <artifactId>SpringMyBatisIntegration</artifactId>
  <packaging>war</packaging>
  <version>1.0</version>

  <name>SpringMyBatisIntegration</name>
  <url>http://maven.apache.org</url>

  <dependencies>
        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-web-api</artifactId>
            <version>6.0</version>
            <scope>provided</scope>
        </dependency>
        <!-- Spring 4 dependencies -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency> 
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>2.2</version>
        </dependency>
        
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.1.0</version>
        </dependency>
        
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.2.2</version>
        </dependency>
        
        <!-- json request -->        
        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-mapper-asl</artifactId>
            <version>1.9.11</version>
        </dependency>
        
    </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>2.0.2</version>
        <configuration>
          <source>1.5</source>
          <target>1.5</target>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

This is my database structure,

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`)
) 

Im mapping my “testing” table into an xml and java file,

<?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.springmybatis.mapper.TestingMapper" >
  
    <select id="insert" parameterType="com.edw.springmybatis.bean.Testing" >
        insert into testing (name, address)
        values ( #{name,jdbcType=VARCHAR}, #{address,jdbcType=VARCHAR} )
    </select> 
  
</mapper>
package com.edw.springmybatis.bean;

import java.io.Serializable;

public class Testing implements Serializable  {

    private Integer id;
    private String name;
    private String address;

	public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }
	
    // other setter and getter 

    public Testing() {
    }

    public Testing(String name, String address) {
        this.name = name;
        this.address = address;
    }
}

This is my configuration.xml file for handling all mybatis’ configuration file,

<?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>
  <mappers>
  	<mapper resource="TestingMapper.xml"/>		
  </mappers>
</configuration>

And my spring xml files, applicationContext.xml and dispatcher-servlet.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:p="http://www.springframework.org/schema/p"
       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-3.0.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

    <context:annotation-config/>
    
    <tx:annotation-driven transaction-manager="transactionManager"/>
    
    <context:component-scan base-package="com.edw.springmybatis.service"/>
    
    <!-- middleware datasource  -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
          p:driverClassName="com.mysql.jdbc.Driver" p:url="jdbc:mysql://localhost/test"
          p:username="root" p:password=""
          p:initialSize="2"
          p:maxActive="30"
          p:maxIdle="10"
          p:minIdle="3"
          p:maxWait="30000"
          p:removeAbandoned="true"
          p:removeAbandonedTimeout="30"
          p:validationQuery="SELECT 1" />

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="/WEB-INF/configuration.xml" />
    </bean>
    
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.edw.springmybatis.mapper" />
    </bean>
    
    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>    
</beans>
<?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:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">


    <context:component-scan base-package="com.edw.springmybatis.controller" />
	
    <mvc:annotation-driven />
    <mvc:default-servlet-handler />
</beans>

And finally, my web.xml file to hold all my spring xml files,

<?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">
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/applicationContext.xml</param-value>
    </context-param>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>2</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
</web-app>

My log4j file,

# Global logging configuration
log4j.rootLogger=WARN,stdout
log4j.logger.com.edw=DEBUG

log4j.rootLogger=WARN,stdout

# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d [%c{1}] %-5p %c:%L - %m%n

Okay, now is for my java class. First is MyBatis mapper file,

package com.edw.springmybatis.mapper;

import com.edw.springmybatis.bean.Testing;

public interface TestingMapper {
    public void insert(Testing testing);    
}

Next is my Service class,

package com.edw.springmybatis.service;

import com.edw.springmybatis.bean.Testing;
import com.edw.springmybatis.mapper.TestingMapper;
import java.util.Random;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public class TestingService {

    @Autowired
    private TestingMapper testingMapper;

    public void insert() throws Exception {
        testingMapper.insert(new Testing("" + new Random().nextInt(), "" + new Random().nextInt()));
        testingMapper.insert(new Testing()); // this will throw an exception
    }
}

And finally, my controller file

package com.edw.springmybatis.controller;

import com.edw.springmybatis.service.TestingService;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
public class IndexController {

    @Autowired
    private TestingService testingService;
    
    private final Logger logger = Logger.getLogger(this.getClass());
    
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public @ResponseBody Integer index() {
        try {
            testingService.insert();
			return 1;
        } catch (Exception e) {
            logger.error(e);
        }
        return 0;
    }    
}

This is what happen when im accessing my application directly,
mybatisspring1

And this what is written on my netbeans console,
mybatisspring2

As you can see, my previous insert is rolledback so there is no new record on my “testing” table.
mybatisspring3

And this is my netbeans project structure,
mybatisspring4

You can find the sourcecode needed on my Github page.

Google+

Set Isolation Level on MyBatis

On this example, im trying to set mybatis default isolation level, but before i go further let me explain a little why we need to setup an isolation level.

In a typical application, multiple transactions run concurrently, often working with the same data to get their job done. Concurrency, while necessary, can lead to the following problems:

Dirty read—Dirty reads occur when one transaction reads data that has been written but not yet committed by another transaction. If the changes are later rolled back, the data obtained by the first transaction will be invalid.

Nonrepeatable read—Nonrepeatable reads happen when a transaction performs the same query two or more times and each time the data is different. This is usually due to another concurrent transaction updating the data between the queries.

Phantom reads—Phantom reads are similar to nonrepeatable reads. These occur when a transaction (T1) reads several rows, and then a concurrent transaction (T2) inserts rows. Upon subsequent queries, the first transaction (T1) finds additional rows that were not there before.

That’s why on some mission critical application, i always set Isolation Level on Serializable. Why i use Serializable because this fully ACID-compliant isolation level ensures that dirty reads, nonrepeatable reads, and phantom reads are all prevented. This is the slowest of all isolation levels because it is typically accomplished by doing full table locks on the tables involved in the transaction.

Okay, here is how to achieve Serializable Isolation Level using MyBatis,

import com.edw.mybatis.bean.Testing;
import com.edw.mybatis.config.MyBatisSqlSessionFactory;
import com.edw.mybatis.mapper.TestingMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.TransactionIsolationLevel;
import org.apache.log4j.Logger;

public class Main {

    private static Logger logger = Logger.getLogger(Main.class);

    public static void main(String[] args) {

        SqlSessionFactory sqlSessionFactory = MyBatisSqlSessionFactory.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession(TransactionIsolationLevel.SERIALIZABLE);
        TestingMapper testingMapper = sqlSession.getMapper(TestingMapper.class);

        try {
            Testing testing = new Testing();
            testing.setName("name 1");
            testing.setAddress("address 1");

            int success = testingMapper.insert(testing);
            logger.debug(success);
            
            sqlSession.commit();
        } catch (Exception e) {
            logger.error(e, e);
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
    }
}

Hope this piece of code could help others, have fun with other TransactionIsolationLevel parameters, such as READ_COMMITTED or REPEATABLE_READ. ;-)

Google+

How To Encrypt Properties File

Sometime i had to put sensitive informations such as database’s username and password on a properties file. In order make it safer, i had to encrypted all the information i had on these properties files. In this simple example, im trying to encrypt database username, password and connection url for iBatis connection, and i use Jasypt library for encryption.

As usual, a simple database,

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', '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 POJO

package com.edw.bean;

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

    // other setter and getter

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

A simple xml query, 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" >

    <resultMap id="result" class="com.edw.bean.Contoh">
        <result property="nama" column="NAMA" columnIndex="1"/>
        <result property="alamat" column="ALAMAT" columnIndex="2"/>
    </resultMap>
   
    <select id="selectAllContoh" resultMap="result">
        select nama, alamat
        from contoh
    </select>

</sqlMap>

and a simple xml to load all my queries, i named it sqlmapconfig.xml. Please take a look at line 21 to 23, these are parameters from properties file.

<?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"
        maxSessions="20"
        />

    <transactionManager type="JDBC" commitRequired="false">
        <dataSource type="SIMPLE">

            <property name="SetAutoCommitAllowed" value="false"/>
            <property name="DefaultAutoCommit" value="false"/>

            <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
            <property name="JDBC.ConnectionURL" value="${JDBC.ConnectionURL}"/>
            <property name="JDBC.Username" value="${JDBC.Username}"/>
            <property name="JDBC.Password" value="${JDBC.Password}"/>
   
        </dataSource>
    </transactionManager>

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

this is my db.properties file, always make sure you had this “ENC( )” on your encrypted values.

JDBC.ConnectionURL=ENC(Q29EXRzvWOsaXiT3YcYjlEN7W4OBU2YulDTnfrHq8LtVL3nyr7ATpQ==)
JDBC.Username=ENC(sM7XAh9750ZUzguilF6kmw==)
JDBC.Password=ENC(GTr5QT97wgVcLNclT8NjClNYKOLeP6c1)

This is my java file, i use it to load db.properties and connecting it to fit parameters on sqlmapconfig.xml.

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.File;
import java.io.FileInputStream;
import java.io.Reader;
import java.util.Properties;
import org.jasypt.encryption.pbe.StandardPBEStringEncryptor;
import org.jasypt.properties.EncryptableProperties;


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

    protected static final SqlMapClient sqlMap;

    private static final String PWD = "SDHLKSHUWEHDKSLKLJKSALJDLKA00IUAY98273492JLKASJDLKASJDKLAJSD";
    

    static {
        try {
            StandardPBEStringEncryptor  encryptor = new StandardPBEStringEncryptor();
            encryptor.setAlgorithm("PBEWithMD5AndDES");
            encryptor.setPassword(PWD);
            Properties properties = new EncryptableProperties(encryptor);
            properties.load(new FileInputStream(new File("db.properties")));

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

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

this is my Main class,

package com.edw.main;

import com.edw.bean.Contoh;
import com.edw.config.SqlMapConfig;
import java.util.List;
import org.apache.log4j.Logger;

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

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

    public Main() {        
    }

    private void execute(){
        try {
            List<Contoh> contohs = SqlMapConfig.getSqlMap().queryForList("contoh.selectAllContoh");
            for (Contoh contoh : contohs) {
                logger.debug(contoh);
            }            
        } catch (Exception ex) {
            logger.error(ex.getMessage(),ex);
        }

    }

    public static void main(String[] args) {
        Main main = new Main();
        main.execute();
    }

}

this is what happen on my log window when im running this app

run:
2011-02-06 00:10:27,808 [SimpleDataSource] DEBUG com.ibatis.common.jdbc.SimpleDataSource:26 - Created connection 29524641.
2011-02-06 00:10:27,812 [Connection] DEBUG java.sql.Connection:26 - {conn-100000} Connection
2011-02-06 00:10:27,815 [Connection] DEBUG java.sql.Connection:26 - {conn-100000} Preparing Statement:          select nama, alamat         from contoh     
2011-02-06 00:10:27,855 [PreparedStatement] DEBUG java.sql.PreparedStatement:26 - {pstm-100001} Executing Statement:          select nama, alamat         from contoh     
2011-02-06 00:10:27,855 [PreparedStatement] DEBUG java.sql.PreparedStatement:26 - {pstm-100001} Parameters: []
2011-02-06 00:10:27,855 [PreparedStatement] DEBUG java.sql.PreparedStatement:26 - {pstm-100001} Types: []
2011-02-06 00:10:27,857 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} ResultSet
2011-02-06 00:10:27,873 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Header: [NAMA, ALAMAT]
2011-02-06 00:10:27,874 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [edwin, singapore]
2011-02-06 00:10:27,874 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [kamplenk, ciledug]
2011-02-06 00:10:27,874 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [nugie, pamulang]
2011-02-06 00:10:27,874 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [samsu, dago]
2011-02-06 00:10:27,875 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [tebek, karawaci]
2011-02-06 00:10:27,876 [SimpleDataSource] DEBUG com.ibatis.common.jdbc.SimpleDataSource:26 - Returned connection 29524641 to pool.
2011-02-06 00:10:27,876 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=edwin, alamat=singapore}
2011-02-06 00:10:27,876 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=kamplenk, alamat=ciledug}
2011-02-06 00:10:27,876 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=nugie, alamat=pamulang}
2011-02-06 00:10:27,876 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=samsu, alamat=dago}
2011-02-06 00:10:27,878 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=tebek, alamat=karawaci}
BUILD SUCCESSFUL (total time: 0 seconds)

Btw, im using this test class to generate my encrypted values,

package com.edw.test;

import org.jasypt.encryption.pbe.StandardPBEStringEncryptor;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

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

    private static final String PWD = "SDHLKSHUWEHDKSLKLJKSALJDLKA00IUAY98273492JLKASJDLKASJDKLAJSD";

    public TestEncryption() {
    }

    @BeforeClass
    public static void setUpClass() throws Exception {
    }

    @AfterClass
    public static void tearDownClass() throws Exception {
    }

    @Test
    public void test() {

        StandardPBEStringEncryptor encryptor = new StandardPBEStringEncryptor();
        encryptor.setAlgorithm("PBEWithMD5AndDES");
        encryptor.setPassword(PWD);

        System.out.println(encryptor.encrypt("jdbc:mysql://localhost/test"));
        System.out.println(encryptor.encrypt("root"));
        System.out.println(encryptor.encrypt("password"));

    }
}

and this is my NetBeans project structure

Well, i hope it can help others. Cheers (B)

Google+

a Simple iBatis Cache using OSCache

Some values in a database are know to change slower than others. To improve performance, many developers like to cache often-used data to avoid making unnecessary trips back to the database. iBATIS provides its own caching system, that you configure through a <cacheModel> element.

The results from a query Mapped Statement can be cached simply by specifying the cacheModel parameter. A cache model is a configured cache that is defined within your DataMapper configuration file. IBATIS cache focuses on caching results within the persistence layer. As such, it is independent of the service or presentation layers, and is not based on object identity.

In this simple example, i’m using OSCache. OSCache is a robust caching framework that is able to perform many of the same type of caching strategies that iBATIS provides in its other cache models. When using the OSCACHE model, you create a dependency on the OSCache JARs. You will need to include the OSCache JAR in your project when you use this setting. This cache is configured in the same manner as a standard OSCache install. This means that you will need to have an oscache.properties file available on the root of your classpath for OSCache to read.

Okay, here’s my code,
a simple “test” database, and a “contoh” 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');

a contoh POJO

package com.edw.bean;

/**
 *
 * @author edw
 */
public class Contoh {
    private String nama;
    private String alamat;

    // other setter and getter
}

and my sqlmapconfig.xml as my ibatis xml configuration

<?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"
        maxSessions="20"
        />

    <transactionManager type="JDBC" commitRequired="false">
        <dataSource type="SIMPLE">

            <property name="SetAutoCommitAllowed" value="false"/>
            <property name="DefaultAutoCommit" value="false"/>
            
            <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
            <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost/test"/>
            <property name="JDBC.Username" value="root"/>
            <property name="JDBC.Password" value="xxx"/>
   
        </dataSource>
    </transactionManager>

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

and my xml query, at line 5 to 9 is my OSCache caching, and at line 21 is cacheModel implementation on query “selectAllContoh”. It means that i’m storing “selectAllContoh” resultSet on a cache, so the next time query “selectAllContoh” is called, iBatis will get resultSet from my cache instead of hitting my database.

<?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" >

    <cacheModel id="contohCache" type="OSCACHE">
        <flushInterval hours="24"/>
        <flushOnExecute statement="contoh.insertContoh"/>
        <property name="reference-type" value="SOFT"/>
    </cacheModel>

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

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

    <select id="selectAllContoh" resultMap="result" cacheModel="contohCache">
    select nama, alamat
    from contoh
    </select>

</sqlMap>

a class to load my ibatis xml configuration

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.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 (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

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

and my log4j configuration, i named it log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG,stdout

# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d [%c{1}] %-5p %c:%L - %m%n

and my oscache.properties

cache.capacity=1000

i create a simple JUnit class to test my class

package com.edw.test;

import com.edw.bean.Contoh;
import com.edw.config.SqlMapConfig;
import java.util.List;
import org.apache.log4j.Logger;
import org.junit.Test;
import static org.junit.Assert.*;

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

    @Test
    public void testIbatisCaching() {
        Logger logger = Logger.getLogger(this.getClass());
        try {
            // repeated queries
            for (int i = 0; i < 5; i++) {
                List<Contoh> contohs = SqlMapConfig.getSqlMap().queryForList("contoh.selectAllContoh");

                // check if it's null
                assertNotNull(contohs);

                //sleep
                Object o = new Object();
                synchronized(o){
                    o.wait(2000);
                }

                for (Contoh contoh : contohs) {
                    logger.debug(contoh.getNama());
                }

                logger.debug("================================");
            }
        } catch (Exception ex) {
            logger.error(ex);
        }
    }
}

this is what happen on my console,
on line 24-25 shows how OSCache creating a cache from a query “contoh.selectAllContoh”,
and on line 37-38 shows how query “contoh.selectAllContoh” hitting Ibatis’ cache instead of the database.

2010-10-09 18:49:24,961 [Config] DEBUG com.opensymphony.oscache.base.Config:61 - OSCache: Config called
2010-10-09 18:49:24,968 [Config] INFO  com.opensymphony.oscache.base.Config:147 - OSCache: Properties read {cache.capacity=1000}
2010-10-09 18:49:24,969 [AbstractCacheAdministrator] DEBUG com.opensymphony.oscache.base.AbstractCacheAdministrator:160 - Constructed AbstractCacheAdministrator()
2010-10-09 18:49:24,993 [GeneralCacheAdministrator] INFO  com.opensymphony.oscache.general.GeneralCacheAdministrator:98 - Constructed GeneralCacheAdministrator()
2010-10-09 18:49:24,993 [GeneralCacheAdministrator] INFO  com.opensymphony.oscache.general.GeneralCacheAdministrator:301 - Creating new cache
2010-10-09 18:49:25,028 [AbstractConcurrentReadCache] DEBUG com.opensymphony.oscache.base.algorithm.AbstractConcurrentReadCache:694 - get called (key=1542486422|5257123254|contoh.selectAllContoh|1339823102|     select nama, alamat     from contoh     |executeQueryForList|0|-999999)
2010-10-09 18:49:25,028 [AbstractConcurrentReadCache] DEBUG com.opensymphony.oscache.base.algorithm.AbstractConcurrentReadCache:1061 - persistRetrieve called (key=1542486422|5257123254|contoh.selectAllContoh|1339823102|     select nama, alamat     from contoh     |executeQueryForList|0|-999999)
2010-10-09 18:49:25,099 [Cache] DEBUG com.opensymphony.oscache.base.Cache:697 - No cache entry exists for key='1542486422|5257123254|contoh.selectAllContoh|1339823102|     select nama, alamat     from contoh     |executeQueryForList|0|-999999', creating
2010-10-09 18:49:25,106 [CacheModel] DEBUG com.ibatis.sqlmap.engine.cache.CacheModel:27 - Cache 'contoh.contohCache': cache miss
2010-10-09 18:49:25,331 [SimpleDataSource] DEBUG com.ibatis.common.jdbc.SimpleDataSource:27 - Created connection 5230193.
2010-10-09 18:49:25,334 [Connection] DEBUG java.sql.Connection:27 - {conn-100000} Connection
2010-10-09 18:49:25,336 [Connection] DEBUG java.sql.Connection:27 - {conn-100000} Preparing Statement:      select nama, alamat     from contoh     
2010-10-09 18:49:25,381 [PreparedStatement] DEBUG java.sql.PreparedStatement:27 - {pstm-100001} Executing Statement:      select nama, alamat     from contoh     
2010-10-09 18:49:25,405 [PreparedStatement] DEBUG java.sql.PreparedStatement:27 - {pstm-100001} Parameters: []
2010-10-09 18:49:25,406 [PreparedStatement] DEBUG java.sql.PreparedStatement:27 - {pstm-100001} Types: []
2010-10-09 18:49:25,411 [ResultSet] DEBUG java.sql.ResultSet:27 - {rset-100002} ResultSet
2010-10-09 18:49:25,429 [ResultSet] DEBUG java.sql.ResultSet:27 - {rset-100002} Header: [NAMA, ALAMAT]
2010-10-09 18:49:25,429 [ResultSet] DEBUG java.sql.ResultSet:27 - {rset-100002} Result: [edwin, singapore]
2010-10-09 18:49:25,430 [ResultSet] DEBUG java.sql.ResultSet:27 - {rset-100002} Result: [kamplenk, ciledug]
2010-10-09 18:49:25,430 [ResultSet] DEBUG java.sql.ResultSet:27 - {rset-100002} Result: [nugie, pamulang]
2010-10-09 18:49:25,511 [ResultSet] DEBUG java.sql.ResultSet:27 - {rset-100002} Result: [samsu, dago]
2010-10-09 18:49:25,513 [ResultSet] DEBUG java.sql.ResultSet:27 - {rset-100002} Result: [tebek, jayapura]
2010-10-09 18:49:25,514 [AbstractConcurrentReadCache] DEBUG com.opensymphony.oscache.base.algorithm.AbstractConcurrentReadCache:694 - get called (key=1542486422|5257123254|contoh.selectAllContoh|1339823102|     select nama, alamat     from contoh     |executeQueryForList|0|-999999)
2010-10-09 18:49:25,515 [AbstractConcurrentReadCache] DEBUG com.opensymphony.oscache.base.algorithm.AbstractConcurrentReadCache:1061 - persistRetrieve called (key=1542486422|5257123254|contoh.selectAllContoh|1339823102|     select nama, alamat     from contoh     |executeQueryForList|0|-999999)
2010-10-09 18:49:25,519 [Cache] DEBUG com.opensymphony.oscache.base.Cache:697 - No cache entry exists for key='1542486422|5257123254|contoh.selectAllContoh|1339823102|     select nama, alamat     from contoh     |executeQueryForList|0|-999999', creating
2010-10-09 18:49:25,520 [AbstractConcurrentReadCache] DEBUG com.opensymphony.oscache.base.algorithm.AbstractConcurrentReadCache:1108 - persistStore called (key=1542486422|5257123254|contoh.selectAllContoh|1339823102|     select nama, alamat     from contoh     |executeQueryForList|0|-999999)
2010-10-09 18:49:25,521 [AbstractConcurrentReadCache] DEBUG com.opensymphony.oscache.base.algorithm.AbstractConcurrentReadCache:1087 - persistRetrieveGroup called (groupName=contoh.contohCache)
2010-10-09 18:49:25,521 [AbstractConcurrentReadCache] DEBUG com.opensymphony.oscache.base.algorithm.AbstractConcurrentReadCache:1127 - persistStoreGroup called (groupName=contoh.contohCache)
2010-10-09 18:49:25,523 [CacheModel] DEBUG com.ibatis.sqlmap.engine.cache.CacheModel:27 - Cache 'contoh.contohCache': stored object '[com.edw.bean.Contoh@12bb7e0, com.edw.bean.Contoh@134f69a, com.edw.bean.Contoh@2a15cd, com.edw.bean.Contoh@fd68b1, com.edw.bean.Contoh@e45076]'
2010-10-09 18:49:25,525 [SimpleDataSource] DEBUG com.ibatis.common.jdbc.SimpleDataSource:27 - Returned connection 5230193 to pool.
2010-10-09 18:49:27,526 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - edwin
2010-10-09 18:49:27,526 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - kamplenk
2010-10-09 18:49:27,526 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - nugie
2010-10-09 18:49:27,526 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - samsu
2010-10-09 18:49:27,526 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - tebek
2010-10-09 18:49:27,526 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:37 - ================================
2010-10-09 18:49:27,536 [AbstractConcurrentReadCache] DEBUG com.opensymphony.oscache.base.algorithm.AbstractConcurrentReadCache:694 - get called (key=1542486422|5257123254|contoh.selectAllContoh|1339823102|     select nama, alamat     from contoh     |executeQueryForList|0|-999999)
2010-10-09 18:49:27,536 [CacheModel] DEBUG com.ibatis.sqlmap.engine.cache.CacheModel:27 - Cache 'contoh.contohCache': retrieved object '[com.edw.bean.Contoh@12bb7e0, com.edw.bean.Contoh@134f69a, com.edw.bean.Contoh@2a15cd, com.edw.bean.Contoh@fd68b1, com.edw.bean.Contoh@e45076]'
2010-10-09 18:49:29,536 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - edwin
2010-10-09 18:49:29,536 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - kamplenk
2010-10-09 18:49:29,536 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - nugie
2010-10-09 18:49:29,536 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - samsu
2010-10-09 18:49:29,536 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:34 - tebek
2010-10-09 18:49:29,536 [TestIbatisCaching] DEBUG com.edw.test.TestIbatisCaching:37 - ================================

this is my Netbeans project structure

Well, i guess it can help others. Thank you and see you again. (H)

Google+

Connecting a Servlet, iBatis and Glassfish 3

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.

Google+