SpringBoot and MyBatis and MySQL 2, the mapper xml example

Introduction

Some days before , I have introduced how to use springboot and mybatis to do a hello world example.This post would demo how to run a mapper xml example on springboot+MyBatis+MySQL.

Environments

  • SpringBoot 1.5.12
  • MySQL 5.1.38
  • Java 1.8
  • mybatis-spring-boot-starter 1.3.2

The Pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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">

    <artifactId>test-springboot-mybatis</artifactId>


    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
        <start-class>Main</start-class>
        <mysql.version>5.1.38</mysql.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <!-- Import dependency management from Spring Boot -->
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>1.5.12.RELEASE</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
</project>

The database table:

CREATE TABLE `tbl_student` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(100) NOT NULL,
  `BRANCH` varchar(255) NOT NULL,
  `PERCENTAGE` int(3) NOT NULL,
  `PHONE` int(10) NOT NULL,
  `EMAIL` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And insert a record named **jack** for test.

The Classes

Springboot Main entry class:

package sbm;

@SpringBootApplication
public class Main {
    public static void main(String[] args) {
        SpringApplication.run(Main.class, args);
    }
}

The domain class:

package sbm.domain;
public class Student {
    private int id;
    private String name;
    private String branch;
    private int percentage;
    private int phone;
    private String email;

    //getter and setters...

The Mapper interface:

package sbm.dao;

@Mapper
public interface StudentMapper {
    /**
     * find all the students.
     */
    List<Student> findAll();
}

Here we use a @Mapper to annotate an interface named StudentMapper, which has a method findAll, this method has a corresponding parts in the StudentMapper.xml.

the application.properties

spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = root

#mybatis entity scan packages
mybatis.type-aliases-package=sbm.domain
#Mapper.xml location
mybatis.mapper-locations=classpath*:/mybatis/*Mapper.xml

Notice the mybatis.mapper-locations is the location of the mapper xml files, the files must be in the src/main/resources

the StudentMapper.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="sbm.dao.StudentMapper" >
    <!--<cache />-->
    <resultMap id="baseResultMap" type="sbm.domain.Student" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="phone" property="phone" jdbcType="VARCHAR" />
        <result column="email" property="email" jdbcType="VARCHAR" />
    </resultMap>

    <select id="findAll" resultMap="baseResultMap">
        select id,name,phone,email from tbl_student
    </select>
</mapper>

the Directories structure

Now you should have a directory structure like this: springboot mybatis mapper structure

The testcase

Here we write a junit test case to test the dao:

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestStudentCrudDao {
    @Autowired
    private StudentMapper studentMapper;

    @Test
    public void testHello1() {
        List<Student> students = studentMapper.findAll();
        assertTrue(students!=null&&students.size()>0);
    }


}

As you can see, we just @Autowired the studentMapper, and then use the findAll method, then we run the test, we got the green bar.

It’s so easy, do you think so? Next time I would introduce more complicated springboot and mybatis example.

You can find detail documents about the springboot and unit testing here: