MyBatis-Spring with vFabric SQLFire

MyBatis-Spring helps you integrate your MyBatis code seamlessly with Spring. Using the classes in this library, Spring will load the necessary MyBatis factory and session classes for you. This library also provides an easy way to inject MyBatis data mappers into your service beans. Finally, MyBatis-Spring will handle transactions and translate MyBatis exceptions into Spring DataAccessExceptions.

Here is a simple exampled based on the classic DEPT table.

1. We are working with a DEPT table in SQLFire defined as follows.

DEPT table

  
sqlf> describe dept;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
DEPTNO              |INTEGER  |0   |10  |10    |NULL      |NULL      |NO      
DNAME               |VARCHAR  |NULL|NULL|14    |NULL      |28        |YES     
LOC                 |VARCHAR  |NULL|NULL|13    |NULL      |26        |YES     

3 rows selected

2. Setup your pom.xml to include the following.

pom.xml

  
<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>sqlfire-mybatis</groupId>
  <artifactId>sqlfire-mybatis</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>sqlfire-mybatis</name>
  <properties>
    <spring.version>3.1.2.RELEASE</spring.version>
    <mybatis.version>3.1.1</mybatis.version>
    <mybatis.spring.version>1.1.1</mybatis.spring.version>
    <dbcp.version>1.4</dbcp.version>
    <cglib.version>2.2.2</cglib.version>
  </properties>
  <dependencies>
 <dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis</artifactId>
   <version>${mybatis.version}</version>
 </dependency>
 <dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis-spring</artifactId>
   <version>${mybatis.spring.version}</version>
 </dependency>
 <dependency>
   <groupId>commons-dbcp</groupId>
   <artifactId>commons-dbcp</artifactId>
   <version>${dbcp.version}</version>
 </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>${spring.version}</version>
    </dependency>
 <dependency>
   <groupId>cglib</groupId>
   <artifactId>cglib</artifactId>
   <version>${cglib.version}</version>
 </dependency>
  </dependencies>
  <repositories>
 <repository>
     <id>mybatis-snapshot</id>
     <name>MyBatis Snapshot Repository</name>
     <url>https://oss.sonatype.org/content/repositories/snapshots</url>
 </repository>
  </repositories>
</project>

3. Create the domain modelclass Dept.java, with the standard getter/setters for each attribute. Omitting the full class here.

Dept.java

  
package pas.au.vmware.se.mybatis.domain;

public class Dept 
{
 private int deptno;
 private String dname;
 private String loc;
 
 public Dept()
 {
  
 }
 
 public Dept(int deptno, String dname, String loc) 
 {
  super();
  this.deptno = deptno;
  this.dname = dname;
  this.loc = loc;
 }

......
 

4.Create a DeptMapper.xml file. I prefer to keep SQL out of the java class wheever I can , hence avoided using annotations here.

DeptMapper.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="pas.au.vmware.se.mybatis.mapper.DeptMapper">
 
    <resultMap id="result" type="pas.au.vmware.se.mybatis.domain.Dept">
        <result property="deptno" column="DEPTNO"/>
        <result property="dname" column="DNAME"/>
        <result property="loc" column="LOC"/>
    </resultMap>
 
    <select id="getAll" resultMap="result">
        SELECT * FROM DEPT
    </select>

 <select id="getById" parameterType="int" resultMap="result">
  SELECT * FROM DEPT WHERE DEPTNO = #{deptno}
 </select>
    
    <insert id="insertDept" parameterType="pas.au.vmware.se.mybatis.domain.Dept">
        INSERT INTO dept (deptno, dname, loc)
        VALUES (#{deptno}, #{dname}, #{loc})
    </insert>
</mapper>

5. Create a DeptMapper interface file. Notice how this is a clean interface and no annotations to specify the SQL we are using as that's in the DeptMapper.xml file above.

DeptMapper.java

  
package pas.au.vmware.se.mybatis.mapper;

import java.util.List;

import pas.au.vmware.se.mybatis.domain.Dept;

public interface DeptMapper 
{
  public List<Dept> getAll();
  public Dept getById (int deptno);
  public void insertDept (Dept dept);
}

6. Create a service class for the DEPT table as shown below.

DeptService.java

  
package pas.au.vmware.se.mybatis.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import pas.au.vmware.se.mybatis.domain.Dept;
import pas.au.vmware.se.mybatis.mapper.DeptMapper;

@Service("deptService")
public class DeptService 
{

 @Autowired
 private DeptMapper deptMapper;

 public DeptMapper getDeptMapper() {
  return deptMapper;
 }

 public void setDeptMapper(DeptMapper deptMapper) {
  this.deptMapper = deptMapper;
 }
 
 public List<Dept> getAll()
 {
  return getDeptMapper().getAll();
 }
 
 public Dept getById(int deptno)
 {
  return getDeptMapper().getById(deptno);
 }
 
 @Transactional
 public void insertDept (Dept dept)
 {
  getDeptMapper().insertDept(dept);
 }
}  

7. Create a spring application context file as shown below.

application-context.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:tx="http://www.springframework.org/schema/tx"
 xmlns:context="http://www.springframework.org/schema/context"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
     <property name="driverClassName" value="com.vmware.sqlfire.jdbc.ClientDriver" />
     <property name="url" value="jdbc:sqlfire://localhost:1527/" />
     <property name="username" value="app" />
     <property name="password" value="app" />
 </bean>

 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
 </bean>

   <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
      <property name="dataSource" ref="dataSource" />
   </bean>
  
    <tx:annotation-driven />

 <context:component-scan base-package="pas.au.vmware.se.mybatis.service" />

   <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
     <property name="basePackage" value="pas.au.vmware.se.mybatis.mapper" />
   </bean>
      
</beans> 

8. Finally create a test class to verify the setup as shown below.

SpringDeptTest.java

  
package pas.au.vmware.se.mybatis.test;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import pas.au.vmware.se.mybatis.domain.Dept;
import pas.au.vmware.se.mybatis.service.DeptService;

public class SpringDeptTest 
{

 /**
  * @param args
  */
 public static void main(String[] args) 
 {
        ApplicationContext context = new ClassPathXmlApplicationContext(
                "application-context.xml");
        
        DeptService deptService = (DeptService) context.getBean("deptService");
        
        Dept dept = new Dept(99, "MYBATIS-TEST", "BUNDOORA");
        deptService.insertDept(dept);
        System.out.println("New DEPT added to SQLFire\n");
          
        List<Dept> deps = deptService.getAll();
        
        for (Dept d: deps)
        {
         System.out.println(d);
        }
        
        System.out.println("\n" + deptService.getById(20));
        
 }

}

9. Run SpringDeptTest.java and verfy output as shown below.

Feb 20, 2013 9:14:39 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1fff7a1e: startup date [Wed Feb 20 21:14:39 EST 2013]; root of context hierarchy
Feb 20, 2013 9:14:39 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [application-context.xml]
Feb 20, 2013 9:14:40 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@57ac3379: defining beans [dataSource,sqlSessionFactory,transactionManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,deptService,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,org.mybatis.spring.mapper.MapperScannerConfigurer#0,org.springframework.context.annotation.ConfigurationClassPostProcessor$ImportAwareBeanPostProcessor#0,deptMapper]; root of factory hierarchy
New DEPT added to SQLFire

Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
Dept [deptno=30, dname=SALES, loc=CHICAGO]
Dept [deptno=40, dname=OPERATIONS, loc=BRISBANE]
Dept [deptno=50, dname=MARKETING, loc=ADELAIDE]
Dept [deptno=60, dname=DEV, loc=PERTH]
Dept [deptno=70, dname=SUPPORT, loc=SYDNEY]
Dept [deptno=99, dname=MYBATIS-TEST, loc=BUNDOORA]

Dept [deptno=20, dname=RESEARCH, loc=DALLAS]

The full project structure is as follows showing where the XML files exists in this setup.