Mybatis integration with Spring 3.1 Framework.

Hi All,

I have configured new web project with mybatis and springframework several times. due to several project and being asked from team member to set up another project springframework skeleton. There are many advantage to start up from scratch. then you will know what it all means. But, When you need a quick start up, it would be good to have a project template.

As I have configured it several times. I improved of understanding of these framework setting then I made simple optimized web project template for share with others.

This sample will show the result of Json String.

I recommend you to look through below source roughly and download attached sample source to test running and understanding. it will run fine on tomcat I have tested. you will not need any db connection for sample code. below sample is running based on memory DB Hsql.

Sample Source is attached.
you can download from below link
Download

To integrate mybatis with Spring framework I recommend you to follow below step :

1. create java web project.
2. get all dependencies and configure classpath ( I am going to use maven )
2. configure springframework.
3. configure mybatis
4. create classes and xml ( Mapper interface, Mapper xml, Spring controller, create beans)
5. run it on tomcat or any WAS
6. check result


Directory Structure, you can refer below

Dependency Overview :

Maven Dependency : pom.xml
Springframework 3.1.3, Jackson Mapper 1.9.11, Hsql 2.2.8
I have excluded mybatis spring dependency which depends on old verion of springframework

<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.devtrigger</groupId>
 <artifactId>web-template</artifactId>
 <version>1.0-SNAPSHOT</version>
 <packaging>war</packaging>

 <name>Web Template Project, you can copy this and use for new project set up quickly</name>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <spring-version>3.1.3.RELEASE</spring-version>
 </properties>

 <dependencies>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-orm</artifactId>
   <version>${spring-version}</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-webmvc-portlet</artifactId>
   <version>${spring-version}</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-oxm</artifactId>
   <version>${spring-version}</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-test</artifactId>
   <version>${spring-version}</version>
   <scope>test</scope>
  </dependency>
  <dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis-spring</artifactId>
   <version>1.1.1</version>
   <exclusions>
    <exclusion>
     <groupId>org.springframework</groupId>
     <artifactId>spring-context</artifactId>
    </exclusion>
    <exclusion>
     <groupId>org.springframework</groupId>
     <artifactId>spring-core</artifactId>
    </exclusion>
    <exclusion>
     <groupId>org.springframework</groupId>
     <artifactId>spring-jdbc</artifactId>
    </exclusion>
    <exclusion>
     <groupId>org.springframework</groupId>
     <artifactId>spring-test</artifactId>
    </exclusion>
    <exclusion>
     <groupId>org.springframework</groupId>
     <artifactId>spring-tx</artifactId>
    </exclusion>
   </exclusions>
  </dependency>
  <dependency>
   <groupId>log4j</groupId>
   <artifactId>log4j</artifactId>
   <version>1.2.17</version>
  </dependency>
  <dependency>
   <groupId>org.codehaus.jackson</groupId>
   <artifactId>jackson-mapper-asl</artifactId>
   <version>1.9.11</version>
  </dependency>
  <dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>jstl</artifactId>
   <version>1.2</version>
  </dependency>
  <dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>servlet-api</artifactId>
   <version>2.5</version>
   <scope>provided</scope>
  </dependency>
  <dependency>
   <groupId>javax</groupId>
   <artifactId>javaee-web-api</artifactId>
   <version>6.0</version>
   <scope>provided</scope>
  </dependency>
  <dependency>
   <groupId>org.hsqldb</groupId>
   <artifactId>hsqldb</artifactId>
   <version>2.2.8</version>
  </dependency>
 </dependencies>

 <build>
  <plugins>
   <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <version>2.3.2</version>
    <configuration>
     <source>1.6</source>
     <target>1.6</target>
     <encoding>utf-8</encoding>
    </configuration>
   </plugin>
   <plugin>
    <artifactId>maven-war-plugin</artifactId>
    <version>2.2</version>
   </plugin>
   <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-eclipse-plugin</artifactId>
    <version>2.8</version>
    <configuration>
     <additionalProjectFacets>
      <jst.web>2.5</jst.web>
     </additionalProjectFacets>
     <downloadJavadocs>true</downloadJavadocs>
     <downloadSources>true</downloadSources>
     <wtpContextName>/</wtpContextName>
     <wtpdefaultserver>${eclipse.wtpdefaultserver}</wtpdefaultserver>
     <wtpversion>2.0</wtpversion>
    </configuration>
   </plugin>
  </plugins>
 </build>
</project>

Mybatis configuration : mybatis-context.xml
If you comment out or in, you can change datasource.
I have prepared sample for JDNI, JDBC and Hsql datasources which are all tested. Currently, It is running on Hsql for simple sample running on your memory based DB.

<?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:jdbc="http://www.springframework.org/schema/jdbc"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-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/jdbc
        http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

 <!-- JNDI datasource -->
 <!-- 
 <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean"> 
  <property name="jndiName" value="jdbc/oraclePool"/>
  <property name="resourceRef" value="true" />
 </bean> 
  -->
 
 <!-- Hsql datasource -->
 <!--
 -->
 <jdbc:embedded-database id="dataSource" >
  <jdbc:script location="classpath:hsql/schema.sql"/>
  <jdbc:script location="classpath:hsql/data.sql"/>
 </jdbc:embedded-database>

 <!-- JDBC datasource -->
 <!--
 <bean id="dataSource"
  class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
  <property name="driverClass" value="oracle.jdbc.OracleDriver" />
  <property name="url" value="jdbc:oracle:thin:@HOST:1111:ORA" />
  <property name="username" value="ID" />
  <property name="password" value="PASSWORD" />
 </bean>
 -->

 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="typeAliasesPackage" value="com.devtrigger.model" />
  <property name="mapperLocations" value="classpath*:dao/**/*.xml" />
 </bean>

 <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
  <constructor-arg index="0" ref="sqlSessionFactory" />
 </bean>

 <!-- scan for mapper interface files and let them be autowired -->
 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  <property name="basePackage" value="com.devtrigger.dao" />
 </bean>

 <bean id="transactionManager"
  class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource" />
 </bean>

</beans>

XML Mapper : JobMapper.xml
You need to write your actual sql queries. value will be replace by "#{property}"
"<property name="typeAliasesPackage" value="com.devtrigger.model" />" is configured on mybatis-context.xml to make it short to write of bean name. You would need to write "com.devtrigger.model.JobInfo" for using a java bean or need to be used typeAlias. Please aware that "jobInfo" is id of result map, "JobInfo" is a java bean.

<?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.devtrigger.dao.JobMapper">

 <cache />

 <resultMap type="JobInfo" id="jobInfo">
  <result property="id" column="id" />
  <result property="panNumber" column="pan_number" />
  <result property="address" column="address" />
  <result property="city" column="city" />
  <result property="state" column="state" />
  <result property="pincode" column="pincode" />
 </resultMap>

 <select id="selectAll" resultMap="jobInfo">
  <![CDATA[
  select id, pan_number, address, city, state, pincode
  from JBT_MEM
  ]]>
 </select>

 <insert id="insert" parameterType="JobInfo">
  <![CDATA[
  insert into JBT_MEM values(#{id}, #{pan_number}, #{address}, #{city}, #{state}, #{pincode})
  ]]>  
 </insert>

 <update id="update" parameterType="JobInfo">
  <![CDATA[
  ]]> 
 </update>


 <delete id="delete" parameterType="JobInfo">
  <![CDATA[
  ]]> 
 </delete>
</mapper>

Mapper Interface : JobInfo.java
you need to have interface for mapping with Mapper xml file. the method name id must mapped with ID of SQL described in the mapper xml.

package com.devtrigger.dao;

import java.util.List;

import com.devtrigger.model.JobInfo;

public interface JobMapper {

 List<JobInfo> selectAll();
 int insert(JobInfo sampleInfo);
 int update(JobInfo sampleInfo);
 int delete(JobInfo sampleInfo);
}

Spring Controller : IndexController.java

package com.devtrigger.controller;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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.ResponseBody;

import com.devtrigger.model.JobInfo;
import com.devtrigger.service.JobService;

@Controller
public class IndexController {

 private final Log log = LogFactory.getLog(this.getClass());

 @Autowired
 private JobService jobService;
 
 @RequestMapping(value = "/index")
 @ResponseBody
 public List<JobInfo> requestIndex(){
  
  log.debug("controller is called");
  return jobService.getSampleInfo();
 }
 
}

type URL "http://localhost:8080/index.do" on your browser then you will see Json String returns. The reason I show Json result instead of jsp servlet result is, because you will find many sample on internet for jsp servlet result return
BTW, you need to put your jsp file under "/WEB-INF/jsp/" if you need.

[{"id":"1","panNumber":"AABBAABB","address":"Address","city":"NY","state":"AB","pincode":23500},{"id":"2","panNumber":"BGDBCBDB","address":"Address","city":"LA","state":"St","pincode":23500}]