Detailed Explanation of MyBatis Configuration in the Spring Boot Series

PS: Your state depends on your mindset. If you want to stop feeling anxious, first regulate the rhythm of your life.

The previous articles attempted interface development, Thymeleaf templates, common syntax, template layout, project internationalization, JDBC, etc. You can read the previous articles before reading this one:

MyBatis is an excellent persistence layer framework that uses XML or annotations for configuration and mapping, making it easy to map POJOs to records in the database.

MyBatis Workflow
Dependencies and Configuration
@Mapper and @MapperScan
Entity Classes
Mapper Configuration Files
Mapper Interfaces
Mapper Mapping Files
Usage of the collection Tag
Multi-Data Source Configuration
Test Results
MyBatis Annotation Configuration

MyBatis Workflow#

The MyBatis workflow is shown in the diagram below:

Read the mybatis-config.xml configuration file;
Load the Mapper mapping files or corresponding annotation content, which define the corresponding SQL statements;
Create a session factory SqlSessionFactory based on the configuration information;
Create a SqlSession from the session factory, which contains all the methods needed to execute SQL;
Create an Executor to execute SQL statements. An Executor is created when the session factory SqlSessionFactory is created, with the default executor type being ExecutorType.SIMPLE;
MappedStatement object, which is a parameter in the Executor executor method, mainly encapsulates the mapping information in the Mapper XML file;
Input parameter mapping;
Output parameter mapping.

Dependencies and Configuration#

Create a Spring Boot project and add the dependencies for MyBatis and MySQL driver in its build.gradle file as follows:

dependencies {
    // ...
    // myBaits
    // http://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/index.html
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.1'
    // MySQL driver
    runtime("mysql:mysql-connector-java")
    // ...
}

Then configure the database connection parameters and MyBatis-related configurations in the project's application.properties file as follows:

# Database username
spring.datasource.username=root
# Database password
spring.datasource.password=admin
# JDBC Driver
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# JDBC URL
spring.datasource.url=jdbc:mysql://localhost:3306/db_student?serverTimezone=Asia/Shanghai
#spring.datasource.url=jdbc:mysql://localhost:3306/db_student?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true

# Whether to perform a status check on MyBatis XML configuration files, just checks the status, default false
mybatis.check-config-location=true
# Location of mybatis-config.xml file
mybatis.config-location=classpath:mybatis/mybatis-config.xml
# Mapper corresponding XML path
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
# Set the alias path to avoid writing fully qualified class names
mybatis.type-aliases-package=com.manu.mybatisxml.model

MyBatis mainly configures the path of the configuration file mybatis-config.xml and the path of the corresponding XML files for the Mapper.

@Mapper and @MapperScan#

The @Mapper annotation is used to mark Mapper interfaces. The interfaces marked with this annotation will generate corresponding dynamic proxy classes. If there are multiple Mapper interfaces, each needs to be marked with the @Mapper annotation, as shown below:

@Mapper
public interface ClassMapper{
    ///
}

@MapperScan is annotated on the project's entry class and can configure one or more packages where the interfaces to be scanned are located. Wildcards * can also be used for configuration, as shown below:

@SpringBootApplication
// Scan interfaces in the specified package
@MapperScan("com.manu.mybatisxml.mapper")
// @MapperScan("com.manu.mybatisxml.*.mapper")
// @MapperScan({"pack1","pack2"})
public class SpringBootMybatisXmlApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringBootMybatisXmlApplication.class, args);
    }
}

Entity Classes#

The case is the relationship between classes and students, which is a one-to-many relationship. Define the class class Class as follows:

/**
 * Class class
 */
public class Class {
    private String classId;
    private String name;
    private List<Student> students;
    public Class() {
    }
    public Class(String classId, String name) {
        this.classId = classId;
        this.name = name;
    }
    // ...
    // setter, getter, toString
}

Define the student class Student as follows:

/**
 * Student class
 */
public class Student {
    private String classId;
    private String sno;
    private String name;
    private String grade;
    public Student() {
    }
    public Student(String classId, String sno, String name, String grade) {
        this.classId = classId;
        this.sno = sno;
        this.name = name;
        this.grade = grade;
    }
    // ...
    // setter, getter, toString
}

MyBatis Configuration File#

The MyBatis configuration file is mybatis-config.xml. When using MyBatis in Spring Boot, most configurations in this configuration file can be configured in the application.properties file. Therefore, in Spring Boot projects, this configuration file can help simplify fully qualified class names, as follows:

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC
    "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <!-- Define aliases to avoid writing fully qualified class names -->
        <typeAlias alias="Integer" type="java.lang.Integer" />
        <typeAlias alias="Long" type="java.lang.Long" />
        <typeAlias alias="HashMap" type="java.util.HashMap" />
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
        <typeAlias alias="ArrayList" type="java.util.ArrayList" />
        <typeAlias alias="LinkedList" type="java.util.LinkedList" />
        <typeAlias alias="Student" type="com.manu.mybatisxml.model.Student" />
        <typeAlias alias="Class" type="com.manu.mybatisxml.model.Class" />
    </typeAliases>
</configuration>

Mapper Interface#

The method names in the Mapper interface correspond to the SQL statements in the corresponding Mapper mapping file, and the method names must be the same as the id attribute in the corresponding SQL statement. The ClassMapper is as follows:

/**
 * Mapper interface corresponding to ClassMapper.xml
 */
public interface ClassMapper {
    /**
     * Insert a record
     * @param student student
     */
    void insertStudent(Student student);
    void insertClass(Class course);
    /**
     * Delete a record by sno
     * @param sno sno
     */
    void deleteStudent(String sno);
    /**
     * Update data
     * @param student student
     */
    void updateStudent(Student student);
    /**
     * Query data by name
     * @param name name
     * @return
     */
    Student findStudentByName(String name);
    /**
     * Query all data
     * @return
     */
    List<Student> findAllStudent();
    /**
     * Query collection data
     * @param name name
     * @return
     */
    Class findClassStudents(String name);
    /**
     * Nested query for collection data
     * @param classId classId
     * @return
     */
    Class findClassStudents1(String classId);
}

Mapper Mapping File#

The Mapper mapping file is based on XML and uses SQL tags corresponding to SQL statements to flexibly construct SQL statements. Some tags and their attributes are self-explanatory. Commonly used tags are as follows:

mapper: Configures the Mapper mapping file corresponding to the Mapper interface class;
resultMap: Result set of the query statement;
result: Used to define fields in the resultMap tag;
id: Used to define the primary key field in the resultMap tag;
collection: Collection data, such as List<Student>;
sql: Defines SQL statement blocks for use by other SQL statements;
insert: Insert statement;
delete: Delete statement;
update: Update statement;
select: Query statement.

Common attributes can be viewed in the relevant comments in the following example. The Mapper mapping file corresponding to the above Mapper interface ClassMapper is as follows:

<?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.manu.mybatisxml.mapper.ClassMapper">
    <!-- Student POJO mapping result set -->
    <!-- id: unique identifier -->
    <!-- type: specific POJO object type -->
    <resultMap id="StudentResultMap" type="com.manu.mybatisxml.model.Student">
        <!-- column: primary key field can also be an alias field in the query statement -->
        <!-- property: corresponding property in the POJO object -->
        <!-- jdbcType: field type -->
        <id column="classId" property="classId" jdbcType="VARCHAR" />
        <!-- column: table field -->
        <result column="userName" property="name" jdbcType="VARCHAR" />
        <result column="sno" property="sno" jdbcType="VARCHAR" />
        <result column="grade" property="grade" jdbcType="VARCHAR" />
    </resultMap>

    <!-- Student POJO mapping result set, carrying collection result set -->
    <resultMap id="ClassWithCollectionResultMap" type="com.manu.mybatisxml.model.Class">
        <id column="classId" property="classId" jdbcType="VARCHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <!-- ofType: data type in the collection -->
        <collection property="students" ofType="Student">
            <id column="sno" property="sno" jdbcType="VARCHAR" />
            <result column="userName" property="name" jdbcType="VARCHAR" />
            <result column="classId" property="classId" jdbcType="VARCHAR" />
            <result column="grade" property="grade" jdbcType="VARCHAR" />
        </collection>
    </resultMap>

    <!-- Student POJO mapping result set, carrying collection result set, nested query -->
    <resultMap id="ClassWithCollectionResultMap1" type="com.manu.mybatisxml.model.Class">
        <id column="classId" property="classId" jdbcType="VARCHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <!-- column: condition for nested query -->
        <!-- select: nested query statement -->
        <collection column="{classId = classId}" property="students" ofType="Student"
            select="getStudent" />
    </resultMap>

    <select id="getStudent" parameterType="String" resultMap="StudentResultMap">
        SELECT *
        FROM mybatis_student
        WHERE classId = #{classId}
    </select>

    <!-- Define basic fields -->
    <sql id="BaseStudentColumn">
        sno,userName,classId,grade
    </sql>

    <!-- Insert data -->
    <!-- id identifier corresponds to the method name in the Mapper interface -->
    <insert id="insertClass" parameterType="Class">
        INSERT INTO mybatis_class(classId, name)
        VALUES (#{classId}, #{name})
    </insert>
    <insert id="insertStudent" parameterType="Student">
        INSERT INTO mybatis_student(classId, userName, sno, grade)
        VALUES (#{classId}, #{name}, #{sno}, #{grade})
    </insert>

    <!-- Delete data -->
    <delete id="deleteStudent" parameterType="String">
        DELETE
        FROM mybatis_student
        WHERE sno = #{sno}
    </delete>

    <!-- Update data -->
    <update id="updateStudent" parameterType="Student">
        UPDATE mybatis_student
        SET userName = #{name},
            classId  = #{classId},
            grade    = #{grade},
            sno      = #{sno}
        WHERE sno = #{sno}
    </update>

    <!-- Query collection of data that meets the conditions -->
    <select id="findClassStudents" parameterType="String" resultMap="ClassWithCollectionResultMap">
        SELECT mybatis_class.classId,
               mybatis_class.name,
               mybatis_student.sno,
               mybatis_student.userName,
               mybatis_student.grade
        FROM mybatis_student,
             mybatis_class
        WHERE mybatis_class.classId = mybatis_student.classId
          and mybatis_class.name = #{name}
    </select>

    <!-- Query collection of data that meets the conditions -->
    <select id="findClassStudents1" parameterType="String"
        resultMap="ClassWithCollectionResultMap1">
        SELECT mybatis_class.classId,
               mybatis_class.name,
               mybatis_student.sno,
               mybatis_student.userName,
               mybatis_student.grade
        FROM mybatis_student,
             mybatis_class
        WHERE mybatis_class.classId = mybatis_student.classId
          and mybatis_class.classId = #{classId}
    </select>

    <!-- Query a single data -->
    <select id="findStudentByName" resultMap="StudentResultMap" parameterType="String">
        SELECT *
        FROM mybatis_student
        WHERE userName = #{name}
    </select>

    <!-- Query all data -->
    <select id="findAllStudent" resultMap="StudentResultMap">
        SELECT
        <include refid="BaseStudentColumn" />
        FROM mybatis_student
    </select>
</mapper>

Usage of the collection Tag#

The above text introduced some commonly used tags in the Mapper file. There is nothing special to say about the usage of other tags. Here, the usage of the <collection/> tag is explained separately. This tag is mainly used to identify result sets, such as the student collection List<Student> in the class class Class. Through this tag, you can query the student collection of the specified class. The first way is:

<!-- Student POJO mapping result set, carrying collection result set -->
<resultMap id="ClassWithCollectionResultMap" type="Class">
    <id column="classId" property="classId" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <!-- ofType: data type in the collection -->
    <collection property="students" ofType="Student">
        <id column="sno" property="sno" jdbcType="VARCHAR" />
        <result column="userName" property="name" jdbcType="VARCHAR" />
        <result column="classId" property="classId" jdbcType="VARCHAR" />
        <result column="grade" property="grade" jdbcType="VARCHAR" />
    </collection>
</resultMap>

The corresponding query SQL mapping is as follows:

<!-- Query collection of data that meets the conditions -->
<select id="findClassStudents" parameterType="String" resultMap="ClassWithCollectionResultMap">
    SELECT mybatis_class.classId,
           mybatis_class.name,
           mybatis_student.sno,
           mybatis_student.userName,
           mybatis_student.grade
    FROM mybatis_student,
         mybatis_class
    WHERE mybatis_class.classId = mybatis_student.classId
      and mybatis_class.name = #{name}
</select>

The second way is as follows:

<!-- Student POJO mapping result set, carrying collection result set, nested query -->
<resultMap id="ClassWithCollectionResultMap1" type="com.manu.mybatisxml.model.Class">
    <id column="classId" property="classId" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <!-- column: condition for nested query -->
    <!-- select: nested query statement -->
    <collection column="{classId = classId}" property="students" ofType="Student"
        select="getStudent" />
</resultMap>

<select id="getStudent" parameterType="String" resultMap="StudentResultMap">
    SELECT *
    FROM mybatis_student
    WHERE classId = #{classId}
</select>

The corresponding query SQL mapping is as follows:

<!-- Query collection of data that meets the conditions -->
<select id="findClassStudents1" parameterType="String"
    resultMap="ClassWithCollectionResultMap1">
    SELECT mybatis_class.classId,
           mybatis_class.name,
           mybatis_student.sno,
           mybatis_student.userName,
           mybatis_student.grade
    FROM mybatis_student,
         mybatis_class
    WHERE mybatis_class.classId = mybatis_student.classId
          and mybatis_class.classId = #{classId}
</select>

By defining findClassStudents in the Mapper interface, you can query the corresponding collection of Student.

Multi-Data Source Configuration#

Create multiple data source configuration files separately to generate multiple different data sources and different SqlSessionFactory, etc. The main data source configuration is as follows:

/**
 * @Primary indicates the primary data source
 * basePackages: specifies the scanned Mapper interfaces
 * sqlSessionTemplateRef: specifies the SqlSessionTemplate in the Mapper path
 */
@Configuration
@MapperScan(basePackages = "com.manu.multimybatisxml.mapper.primary",
        sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class PrimaryDataSourceConfig {

    @Primary
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/primary/*.xml"));
        return sessionFactoryBean.getObject();
    }

    @Primary
    @Bean
    public DataSourceTransactionManager primaryDataSourceTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean
    public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

The configuration for the second data source is the same as above, just without the @Primary annotation, modifying the name of the second data source and the corresponding Mapper mapping files, etc. This will not be elaborated here.

Then, configure multiple database connections in the application.properties file according to the prefixes specified in the above configuration as follows:

# dataSourceOne
spring.datasource.primary.username=root
spring.datasource.primary.password=admin
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.datasource.jdbc-url used to rewrite custom connection pool in multi-data source
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/data_source_one?serverTimezone=Asia/Shanghai

# dataSourceTwo
spring.datasource.secondary.username=root
spring.datasource.secondary.password=admin
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/data_source_two?serverTimezone=Asia/Shanghai

# Whether to perform a status check on MyBatis XML configuration files, just checks the status, default false
mybatis.check-config-location=true
# Location of mybatis-config.xml file
mybatis.config-location=classpath:mybatis/mybatis-config.xml
# Set the alias path to avoid writing fully qualified class names
mybatis.type-aliases-package=com.manu.multimybatisxml.model

For specific content, you can reply with the keyword 【Spring Boot】 to get the source code link.

Test Results#

The case is only to illustrate the usage, and readers do not need to care about its rationality. Write the test class as follows:

/**
 * MyBatisTest
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class MyBatisTest {
    @SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection")
    @Autowired
    private ClassMapper mClassMapper;

    @Test
    public void insert() {
        Class class1 = new Class("class1", "Class One");
        Class class2 = new Class("class2", "Class Two");
        mClassMapper.insertClass(class1);
        mClassMapper.insertClass(class2);

        List<Student> students = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Student student;
            if (i % 2 == 0) {
                student = new Student("class1", "sno" + i, "Student"+i, "A");
            } else {
                student = new Student("class2", "sno" + i, "Student"+i, "B");
            }
            mClassMapper.insertStudent(student);
        }
    }

    @Test
    public void deleteStudentBySno() {
        mClassMapper.deleteStudent("sno0");
    }

    @Test
    public void updateStudent() {
        Student student = new Student("class1","sno1","student1","C");
        mClassMapper.updateStudent(student);
    }

    @Test
    public void findStudentByName() {
        Student student = mClassMapper.findStudentByName("student5");
        System.out.println(student);
    }

    @Test
    public void findAllStudent() {
        List<Student> students = mClassMapper.findAllStudent();
        for (Student student : students) {
            System.out.println(student.toString());
        }
    }

    @Test
    public void findClassStudents(){
        Class clazz = mClassMapper.findClassStudents("Class One");
        System.out.println("classId:"+clazz.getClassId()+",name:"+clazz.getName());

        List<Student> students = clazz.getStudents();
        for (Student student : students) {
            System.out.println(student.toString());
        }
    }

    @Test
    public void findClassStudents1(){
        Class clazz = mClassMapper.findClassStudents1("class1");
        System.out.println("classId:"+clazz.getClassId()+",name:"+clazz.getName());

        List<Student> students = clazz.getStudents();
        for (Student student : students) {
            System.out.println(student.toString());
        }
    }
}

Here, taking the findClassStudents method as an example, the execution result is as follows:

classId:class1,name:Class One
Student{classId='class1', sno='sno1', name='student1', grade='C'}
Student{classId='class1', sno='sno2', name='Student2', grade='A'}
Student{classId='class1', sno='sno4', name='Student4', grade='A'}
Student{classId='class1', sno='sno6', name='Student6', grade='A'}
Student{classId='class1', sno='sno8', name='Student8', grade='A'}

Annotation Configuration#

MyBatis can also be configured using annotations in addition to XML configuration, as follows:

@Mapper
public interface StudentMapper {
    /**
     * The SQL statement in the annotation will automatically get the relevant properties of the student object
     */
    @Insert("INSERT INTO mybatis_student(userName,sno,grade) VALUES(#{name},#{sno},#{grade})")
    void insert(Student student);

    /**
     * The StudentFactory will automatically get the relevant properties of the student object in the SQL statement
     * The insert1 method in StudentFactory gets variable values through #{propertyName}
     */
    @InsertProvider(type = StudentFactory.class, method = "insert1")
    void insert1(Student student);

    /**
     * Directly pass parameters
     * The insert2 method in StudentFactory gets variable values through #{variableName}
     * Additionally, SQL can be concatenated using StringBuffer, as shown in the insert2 method
     */
    @InsertProvider(type = StudentFactory.class, method = "insert2")
    void insert2(String sno, String name, String grade);
}

Implementing the above methods is as follows:

public class StudentFactory {
    public String insert1(Student student) {
        String sql = new SQL() {{
            INSERT_INTO("mybatis_student");
            VALUES("sno", "#{sno}");
            VALUES("userName", "#{name}");
            VALUES("grade", "#{grade}");
        }}.toString();
        System.out.println("SQL:" + sql);
        return sql;
    }

    public String insert2(String sno,String name,String grade) {
        String sql = new SQL() {{
            INSERT_INTO("mybatis_student");
            VALUES("sno", "#{sno}");
            VALUES("userName", "#{name}");
            VALUES("grade", "#{grade}");
        }}.toString();
        System.out.println("SQL:" + sql);
        return sql;
    }
}

Finally, conduct tests as follows:

@RunWith(SpringRunner.class)
@SpringBootTest
public class MyBatisAnnotationTests {

    @SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection")
    @Autowired
    StudentMapper mStudentMapper;

    @Test
    public void insert() {
        Student student = new Student("sno0", "jzman0", "A");
        mStudentMapper.insert(student);
    }

    @Test
    public void insert1() {
        Student student = new Student("sno1", "jzman1", "A");
        mStudentMapper.insert1(student);
    }

    @Test
    public void insert2() {
        Student student = new Student("sno2", "jzman2", "A");
        mStudentMapper.insert2(student.getSno(), student.getName(), student.getGrade());
    }
}

Using annotations in MyBatis results in less code, but there are certain limitations in SQL flexibility. This will not be elaborated on here.