SivaLabs
Back to Blog

SpringBoot : Working with MyBatis

MyBatis is an SQL Mapping framework with support for custom SQL, stored procedures, and advanced mappings.

Spring Boot doesn’t provide official support for MyBatis integration, but the MyBatis community built a Spring Boot starter for MyBatis.

You can read about the Spring Boot MyBatis Starter release announcement at http://blog.mybatis.org/2015/11/mybatis-spring-boot-released.html, and you can explore the source code on GitHub at https://github.com/mybatis/mybatis-spring-boot.

Create a Spring Boot Maven project and add the following MyBatis Starter dependency.

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.0.0</version>
</dependency>

We will be reusing the User.java, schema.sql, and data.sql files created in my previous article, SpringBoot: Working with JdbcTemplate.

Create a MyBatis SQL Mapper interface, UserMapper.java, with a few database operations as follows:

package com.sivalabs.demo.domain;

public interface UserMapper
{
    void insertUser(User user);
    User findUserById(Integer id);
    List<User> findAllUsers();
}

We need to create Mapper XML files to define the queries for the mapped SQL statements for the corresponding Mapper interface methods.

Create the UserMapper.xml file in the src/main/resources/com/sivalabs/demo/mappers/ directory as follows:

<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.sivalabs.demo.mappers.UserMapper">

    <resultMap id="UserResultMap" type="User">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="email" property="email" />
    </resultMap>

    <select id="findAllUsers" resultMap="UserResultMap">
        select id, name, email from users
    </select>

    <select id="findUserById" resultMap="UserResultMap">
        select id, name, email from users WHERE id=#{id}
    </select>

    <insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        insert into users(name,email) values(#{name},#{email})
    </insert>
</mapper>

A few things to observe here are:

  • The namespace in the Mapper XML should be the same as the Fully Qualified Name (FQN) for the Mapper Interface.
  • Statement id values should be the same as the Mapper Interface method names.
  • If the query result column names are different from the bean property names, we can use the <resultMap> configuration to provide a mapping between the column names and their corresponding bean property names.

MyBatis also provides annotation-based query configurations without requiring Mapper XMLs.

We can create the UserMapper.java interface and configure the mapped SQLs using annotations as follows:

public interface UserMapper
{
    @Insert("insert into users(name,email) values(#{name},#{email})")
    @SelectKey(statement="call identity()", keyProperty="id",
    before=false, resultType=Integer.class)
    void insertUser(User user);

    @Select("select id, name, email from users WHERE id=#{id}")
    User findUserById(Integer id);

    @Select("select id, name, email from users")
    List<User> findAllUsers();

}

The Spring Boot MyBatis starter provides the following MyBatis configuration parameters, which we can use to customize MyBatis settings:

mybatis.config=mybatis config file name
mybatis.mapperLocations=mappers file locations
mybatis.typeAliasesPackage=domain object's package
mybatis.typeHandlersPackage=handler's package
mybatis.check-config-location=check that the mybatis configuration exists
mybatis.executorType=mode of execution. Default is SIMPLE

Configure the typeAliasesPackage and mapperLocations in application.properties.

mybatis.typeAliasesPackage=com.sivalabs.demo.domain
mybatis.mapperLocations=classpath*:**/mappers/*.xml

Create the entry point class SpringbootMyBatisDemoApplication.java.

@SpringBootApplication
@MapperScan("com.sivalabs.demo.mappers")
public class SpringbootMyBatisDemoApplication
{
    public static void main(String[] args)
    {
        SpringApplication.run(SpringbootMyBatisDemoApplication.class, args);
    }
}

Observe that we have used the @MapperScan(“com.sivalabs.demo.mappers”) annotation to specify where to look for Mapper interfaces.

Now create a JUnit test class and test our UserMapper methods.

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(SpringbootMyBatisDemoApplication.class)
public class SpringbootMyBatisDemoApplicationTests
{
    @Autowired
    private UserMapper userMapper;

    @Test
    public void findAllUsers() {
        List<User> users = userMapper.findAllUsers();
        assertNotNull(users);
        assertTrue(!users.isEmpty());
    }

    @Test
    public void findUserById() {
        User user = userMapper.findUserById(1);
        assertNotNull(user);
    }

    @Test
    public void createUser() {
        User user = new User(0, "Siva", "siva@gmail.com");
        userMapper.insertUser(user);
        User newUser = userMapper.findUserById(user.getId());
        assertEquals("Siva", newUser.getName());
        assertEquals("siva@gmail.com", newUser.getEmail());
    }
}

You can find the source code of the article at my GitHub repo: https://github.com/sivaprasadreddy/springboot-tutorials

You can read more about MyBatis and Spring integration at http://blog.mybatis.org/p/products.html and http://www.mybatis.org/spring/.

Tags:
comments powered by Disqus