Mybatis + Spring (XML | Annotation) Mapper

Here we will see what we need to do to connect to a relational database “MySql, Oracle, H2..etc” using myBatis with Spring framework & how to implement mappers using xml or annotation such as @Select, @Insert,….etc. For the sake of simplicity I have used H2 embedded database so you can run the sample code. However, you can use this sample code to run with MySql or any other relational database.

Source Code @ github

Note: If you have Maven you can run this sample code directly using 
../spring-mybatis>mvn exec:java

mybatis-spring

Objectives:

  • How to use MyBatis with Spring framework?
  • How to implement mapper using xml or annotation @Select, @Insert…?

Environment:

  • Eclipse
  • Maven (optional to get need jar files)
  • H2 embedded database (MySql or Oralce)

Libraries:

refer to pom.xml or .classes for the complete list of dependencies/jar files.

To use mybatis + Spring you need two things;

  1. Spring xml config files to create “datasoruce, sqlSessionFactory & Mapper”
  2. Java interface or “Mapper” + (if needed) an xml file “contains sql statements”

( 1 ) Java Project Structure

The structure is following Maven default structure, however you can change it.

  • Database table structure for this example is as following
CREATE TABLE person (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255));

( 2 ) Spring-config.xml

Here we need to define 3 beans

  1. datasource
  2. sqlSessionFactory
  3. Mapper
<?xml version="1.0" encoding="UTF-8"?>
<beans.....>

  <context:annotation-config />
  <tx:annotation-driven />

      <!-- step 1 datasource -->
    <jdbc:embedded-database id="dataSource" type="H2" >
        <jdbc:script location="classpath:schema.sql"/>
        <jdbc:script location="classpath:test-data.sql"/>
    </jdbc:embedded-database>

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

     <!-- step 3 mapper -->
  	<bean id="mapper" class="org.mybatis.spring.mapper.MapperFactoryBean" >
    	<property name="mapperInterface" value="com.hmkcode.spring.mybatis.mapper.Mapper" />
    	<property name="sqlSessionFactory" ref="sqlSessionFactory" />
  	</bean>

  <bean id="service" class="com.hmkcode.spring.mybatis.service.Service" />

</beans>

For step 1:

It is the only place you need to change if you would like to use another database. For example if we want to use MySql we will change the datasource part as following:

Note: If you want to use MySql do not forget to add the mysql jdbc driver “jar” to the classpath or dependency to pom.xml.

    <!-- bean id="dataSource">
       <property name="driverClassName" value="com.mysql.jdbc.Driver" />  
       <property name="url" value="jdbc:mysql://localhost:3306/database" />  
       <property name="username" value="root" />  
       <property name="password" value ="" /> 
    </bean -->

For step 2:

We are passing datasource created in step 1 as property of SqlSessionFactoryBean class. Nothing else need to be done! really easy

For step 3:

Here we will pass two things the sqlSessionFactory from step 2 + java interface “Mapper.java” which we are going to code!

( 3 ) Mapper.java (Java Interface) + XML file (if needed)

  • Mapper is the place where we are going to link to our SQL statement written within xml file or in annotations.
  •  If you need to write SQL statement in XML, then you need to add the xml file in the same class path as the java interface.

Mapper.java

package com.hmkcode.spring.mybatis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.hmkcode.spring.mybatis.vo.Person;


public interface Mapper {

	  
	/******************************************
	* Retrieves the List of person 
	* @return person list
	*/
	//SQL query in xml "Mapper.xml"
	public List<Person> selectAllPerson();
	/******************************************
    * Retrieves a person 
    * @param person id
    * @return person
    */
    //SQL query using annotation
    @Select("SELECT * FROM person")
    public List<Person> selectAllPerson2();
  
    /******************************************
    * Retrieves a person 
    * @param person id
    * @return person
    */
    //SQL query using annotation
    @Select("SELECT * FROM person WHERE id = #{id}")
    public Person selectPerson(@Param("id") int id);
  
    /******************************************
    * Insert a person 
    * @param person
    * @return int
    */
    @Insert("INSERT INTO person (name) VALUES (#{name})")
    public int insertPerson(Person person);
}

  • For the method selectAllPerson() the SQL statement is going to be in the xml file.

Mapper.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.java namespace -->
<mapper namespace="com.hmkcode.spring.mybatis.mapper.Mapper">
<!-- Person.java namespace -->
    <resultMap type="com.hmkcode.spring.mybatis.vo.Person" id="Person">
        <!-- map table "person"column to class "Person" property -->
        <id column="ID" property="id"  />
        <result property="NAME" column="name"/>   
    </resultMap>
    
    <select id="selectAllPerson" resultMap="Person">
    	SELECT * FROM person
    </select>
</mapper>

( 4 ) Other .java & .sql files

  • Service.java: is just a helper java class wrapping Mapper.java methods.
package com.hmkcode.spring.mybatis.service;

import ...

public class Service {
  
  @Autowired 
  private Mapper mapper;
 
  public List<Person> selectAllPerson() {
  	return mapper.selectAllPerson();
  }
  public Person selectPerson(int id) {
  	return mapper.selectPerson(id);
  }
  public int insertPerson(Person person){
	return mapper.insertPerson(person);
  }
}
  • Person.java: is POJO plain java object to map data from/to it.
package com.hmkcode.spring.mybatis.vo;


public class Person 
{
  
  private int id;
  private String name;
  
 //getters & setters
}
  • App.java: runs the application, load spring-config.xml and perform some db operations.
package com.hmkcode.spring.mybatis;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.hmkcode.spring.mybatis.service.Service;
import com.hmkcode.spring.mybatis.vo.Person;


public class App 
{
  private static Log log = LogFactory.getLog(App.class);

    public static void main( String[] args )
    {

      ApplicationContext cxt = new ClassPathXmlApplicationContext("spring-config.xml");
      Service service = (Service) cxt.getBean("service");
  	
      log.info("Running App...");

        System.out.println("List<Person> persons = service.selectAllPerson()");
        List<Person> persons = service.selectAllPerson();
        System.out.println("-> "+persons+"\n");
                          
        System.out.println("Person person = service.selectPerson(2)");
        Person person = service.selectPerson(2);
        System.out.println("-> "+person+"\n");
                                                  
        System.out.println("service.insertPerson(person)");
        person.setName("Inserted person");
        service.insertPerson(person);
        System.out.println("-> inserted..."+"\n");
                                                                                
        System.out.println("List<Person> persons = service.selectAllPerson()");
        persons = service.selectAllPerson();
        System.out.println("-> "+persons+"\n");

    }
}
  • schema.sql & test-data.sql: used by H2 database to build data temporarily in the memory.
schema.sql
CREATE TABLE person (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255));

test-data.sql
INSERT INTO person (id,name) VALUES (1, 'person-1 name')
INSERT INTO person (id,name) VALUES (2, 'person-2 name')
INSERT INTO person (id,name) VALUES (3, 'person-3 name')
INSERT INTO person (id,name) VALUES (4, 'person-4 name')
INSERT INTO person (id,name) VALUES (5, 'person-5 name')
INSERT INTO person (id,name) VALUES (6, 'person-6 name')

Note: If you have Maven you can run this sample code directly using Maven command below.

../spring-mybatis>mvn exec:java

Source Code @ github