How to enable caching in iBatis using OSCache

In previous post, we developed our first iBatis hello world application. Now lets make some additions to make it more advanced. I will start by adding caching capability.

Caching allows to retrieve data returned for specific query(s) to be stored in temporary memory, from where it can be fetched again for exactly same query. (Provided cache has not been refreshed from last fetch). If cache is refreshed, all queries will again go to database and store their data in cache.

iBatis can use multiple supported cache solution, and i am using here one of them i.e. OSCache.

Sections in this post:
Adding maven dependency
Update sql-map-config.xml
Update sql data mapping file
Test the code

Adding maven dependency

Update the pom.xml with following dependency for adding OSCache support to project.

        <!-- This excludes the transitive dependency on JMS -->

OSCache is dependent on JMS also, which is not required for this demo… so exclude it. If you do not exclude it, you may find an error while updating the dependency using :

mvn eclipse:eclipse

Update sql-map-config.xml

To add caching support, update the settings tag in sql-map-config.xml file with cacheModelsEnabled=”true”.

The updated config file will look like this:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
        PUBLIC "-// SQL Map Config 2.0//EN"
    <settings useStatementNamespaces="true" cacheModelsEnabled="true"/>
    <transactionManager type="JDBC">
        <dataSource type="SIMPLE">
          <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
          <property name="JDBC.ConnectionURL"  value="jdbc:mysql://localhost:3306/demoDB"/>
          <property name="JDBC.Username" value="root"/>
          <property name="JDBC.Password" value="lg225295"/>
    <sqlMap resource="user.xml"/>


Update sql data mapping file

To enable caching for sql queries, two things needs to be done:

A) Define cache model like this:

<cacheModel id="cache-user" type="OSCACHE" readOnly="true">
        <flushInterval hours="24"></flushInterval>
        <property name="cache-size" value="50"></property>
  • id: Name of cache where all execution results will be stored for a query to which this model will be specified.
  • type: This attribute defines the cache model implementation used.
  • readOnly: This attribute defines that if cache will be used for reading only.
  • flushInterval: This tag defines the number of hours after which cache will be refreshed automatically.
  • cache-size: It defines that this cache will store maximum 50 statement’s results after which it will start deleting old cache and store new one.

B) Add cache model to SQL statements

Add cacheModel=”cache-user” to all select statements for which you want to store result in cache.

<select id="getUserById" parameterClass="java.lang.Integer" resultMap="userResultMap" cacheModel="cache-user">

Test the code

Lets test the code.

package com.howtodoinjava.ibatis.demo;


import com.howtodoinjava.ibatis.demo.dao.UserDao;
import com.howtodoinjava.ibatis.demo.dao.UserDaoIbatis;
import com.howtodoinjava.ibatis.demo.dto.UserTEO;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class TestMain {
	public static void main(String[] args) throws Exception
		UserDao manager = new UserDaoIbatis();

		Reader reader = Resources.getResourceAsReader("sql-maps-config.xml");
		SqlMapClient sqlmapClient = SqlMapClientBuilder.buildSqlMapClient (reader);

		/*UserTEO user = new UserTEO();
		user.setName("Demo User");

		UserTEO createdUser = manager.getUserById(1, sqlmapClient);

		createdUser = manager.getUserById(1, sqlmapClient);

		createdUser = manager.getUserById(1, sqlmapClient);

		createdUser = manager.getUserById(1, sqlmapClient);


DEBUG [main] - Cache 'user.cache-user': cache miss
DEBUG [main] - Created connection 32604499.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {conn-100000} Preparing Statement:      SELECT * FROM USERINFO WHERE ID = ?
DEBUG [main] - {pstm-100001} Executing Statement:      SELECT * FROM USERINFO WHERE ID = ?
DEBUG [main] - {pstm-100001} Parameters: [1]
DEBUG [main] - {pstm-100001} Types: 1
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - {rset-100002} Header: [ID, NAME, EMAIL, PASSWORD, STATUS]
DEBUG [main] - {rset-100002} Result: [1, Demo User,, password, 1]
DEBUG [main] - Cache 'user.cache-user': stored object 'com.howtodoinjava.ibatis.demo.dto.UserTEO@13917ef'
DEBUG [main] - Returned connection 32604499 to pool.
DEBUG [main] - Cache 'user.cache-user': retrieved object 'com.howtodoinjava.ibatis.demo.dto.UserTEO@13917ef'
DEBUG [main] - Cache 'user.cache-user': retrieved object 'com.howtodoinjava.ibatis.demo.dto.UserTEO@13917ef'
DEBUG [main] - Cache 'user.cache-user': retrieved object 'com.howtodoinjava.ibatis.demo.dto.UserTEO@13917ef'

As you can see, first user object is fetched from database, but last 3 calls get the user object from cache and database if not touch again.

Download source code

Happy Learning !!

Note:- In comment box, please put your code inside [java] ... [/java] OR [xml] ... [/xml] tags otherwise it may not appear as intended.

Leave a Reply

Your email address will not be published. Required fields are marked *