Integrating MyBatis with Spring Boot: Annotation and XML Configuration Guide

MyBatis is a powerful semi‑automatic ORM framework widely used in Spring Boot applications. It simplifies JDBC operations by allowing custom SQL, stored procedures, and advanced mappings through XML or annotations, converting Java POJOs to database records.

1. What is MyBatis

Supports customized SQL, stored procedures, and advanced mapping.

Eliminates most JDBC boilerplate code.

Allows simple XML or annotation configuration.

Maps interfaces and POJOs to database tables.

Explains the relationship between databases, data sources, connection pools, and JDBC implementations.

2. Integrating MyBatis

Based on the previously created spring-boot-06-data-druid project, a new module spring-boot-07-data-mybatis is created. The required Maven dependencies are added:

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

<dependencies>
  <!-- web -->
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <!-- mysql -->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
  </dependency>

  <!-- swagger -->
  <dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger2</artifactId>
    <version>2.9.2</version>
  </dependency>
  <dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger-ui</artifactId>
    <version>2.9.2</version>
  </dependency>

  <!-- Druid -->
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
  </dependency>
</dependencies>

The dependency graph is shown in the original article (image omitted).

3. Using Annotation‑Based MyBatis

SQL scripts for the department table are prepared, and application.yml is configured to execute the script on startup:

initialization-mode: always
schema:
  - classpath:department.sql

After the first run, the initialization-mode line is commented out.

Entity class Department:

package com.jackson0714.springboot.entity;

public class Department {
    private Long id;
    private String departmentName;

    public void setId(Long id) { this.id = id; }
    public Long getId() { return id; }
    public void setDepartmentName(String departmentName) { this.departmentName = departmentName; }
    public String getDepartmentName() { return departmentName; }
}

Mapper interface with annotated SQL statements:

@Mapper
public interface DepartmentMapper {
    @Select("select * from department")
    List<Map<String, Object>> getAllDepartment();

    @Select("select * from department where id=#{id}")
    Department getDepartmentById(Long id);

    @Delete("delete from department where id=#{id}")
    int deleteDepartment(Long id);

    @Insert("insert into department(department_name) values(#{departmentName})")
    int createDepartment(String departmentName);

    @Update("update department set department_name=#{departmentName} where id=#{id}")
    int updateDepartmentById(Long id, String departmentName);
}

Custom MyBatis configuration to map underscore column names to camel‑case properties:

@org.springframework.context.annotation.Configuration
public class MyBatisConfig {
    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

REST controller exposing CRUD endpoints (Swagger annotations included):

@Api(value = "DepartmentController", description = "部门controller")
@RequestMapping("/v1/client")
@RestController
public class DepartmentController {
    @Autowired
    DepartmentMapper departmentMapper;

    @ApiOperation(value = "1.查询所有部门")
    @GetMapping("/dept/getAllDepartment")
    public List<Map<String, Object>> getAllDepartment() {
        return departmentMapper.getAllDepartment();
    }

    @ApiOperation(value = "2.根据id查询某个部门")
    @ApiImplicitParams({
        @ApiImplicitParam(name = "id", value = "需要查询的部门id")
    })
    @GetMapping("/dept/{id}")
    public Department getDepartmentById(@PathVariable Long id) {
        return departmentMapper.getDepartmentById(id);
    }

    @ApiOperation(value = "3.新增部门")
    @ApiImplicitParams({
        @ApiImplicitParam(name = "name", value = "部门名称")
    })
    @PostMapping("/dept/create")
    public int createDepartment(@RequestParam String name) {
        return departmentMapper.createDepartment(name);
    }

    @ApiOperation(value = "4.根据id删除部门")
    @ApiImplicitParams({
        @ApiImplicitParam(name = "id", value = "需要删除的部门id")
    })
    @PostMapping("/dept/delete")
    public int deleteDepartment(@RequestParam Long id) {
        return departmentMapper.deleteDepartment(id);
    }

    @ApiOperation(value = "5.根据id更新部门名称")
    @ApiImplicitParams({
        @ApiImplicitParam(name = "id", value = "需要更新的部门id"),
        @ApiImplicitParam(name = "name", value = "需要更新的部门名称")
    })
    @PostMapping("/dept/update")
    public int updateDepartmentById(@RequestParam Long id, @RequestParam String name) {
        return departmentMapper.updateDepartmentById(id, name);
    }
}

Swagger UI can be used to test the endpoints (screenshot omitted).

4. Using XML Configuration for MyBatis

Project file structure is shown (image omitted). The user table creation script is provided:

SET FOREIGN_KEY_CHECKS=0;
-- Table structure for user
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
  `password` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `salt` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '随机盐',
  `nickName` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
  `phone` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '手机号',
  `avatar` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '头像',
  `mini_openId` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '小程序OpenId',
  `lock_flag` char(1) COLLATE utf8mb4_bin DEFAULT '0' COMMENT '0-正常，9-锁定',
  `del_flag` char(1) COLLATE utf8mb4_bin DEFAULT '0' COMMENT '0-正常，1-删除',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`user_id`),
  KEY `user_wx_openid` (`mini_openId`),
  KEY `user_idx1_username` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='用户表';

Sample data insertion:

INSERT INTO user(user_name, password, nick_name, phone) values ("jackson0714", "123", "悟空聊架构", "123456");

Entity class using Lombok:

package com.jackson0714.springboot.entity;

import lombok.Data;
import java.sql.Timestamp;

@Data
public class User {
    private Long userId;
    private String userName;
    private String password;
    private String salt;
    private String nickName;
    private String phone;
    private String avatar;
    private String miniOpenId;
    private String openId;
    private Boolean lockFlag;
    private Boolean delFlag;
    private Timestamp createTime;
    private Timestamp updateTime;
}

Lombok dependency to be added to pom.xml:

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <version>1.18.12</version>
  <scope>provided</scope>
</dependency>

Mapper interface for User:

// @Mapper or MapperScan will register the interface
public interface UserMapper {
    User getUserById(Long userId);
}

Corresponding XML mapping file ( UserMapper.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="com.jackson0714.springboot.mapper.UserMapper">
    <select id="getUserById" resultType="com.jackson0714.springboot.entity.User">
        SELECT * FROM user WHERE user_id=#{userId}
    </select>
</mapper>

REST controller for user operations:

@Api(value = "UserController", description = "用户controller")
@RequestMapping("/v1/client")
@RestController
public class UserController {
    @Autowired
    UserMapper userMapper;

    @ApiOperation(value = "1.根据id查询某个用户")
    @ApiImplicitParams({
        @ApiImplicitParam(name = "需要查询的用户userId", value = "需要查询的用户userId")
    })
    @GetMapping("/emp/{userId}")
    public User getUser(@PathVariable("userId") Long userId) {
        return userMapper.getUserById(userId);
    }
}

The main application class is annotated with @MapperScan to scan the mapper package:

@MapperScan(value = "com.jackson0714.springboot.mapper")
@SpringBootApplication
public class Springboot07DataMybatisApplication {
    public static void main(String[] args) {
        SpringApplication.run(Springboot07DataMybatisApplication.class, args);
    }
}

Swagger UI can be used to test the user API, and Druid monitoring pages are available (screenshots omitted).

All source code can be downloaded from https://github.com/Jackson0714/study-spring-boot.git . The article is part of the "深入浅出Spring Boot" series.