How to integrate MyBatis with Spring Boot for database operations?

Spring Boot Integration with MyBatis

Why MyBatis

SQL Control: Write SQL manually for complex query optimization
Lightweight: Lower learning curve compared to JPA
Flexible: Supports dynamic SQL and stored procedures
Seamless Spring Boot Integration: mybatis-spring-boot-starter provides auto-configuration

Environment Setup

1. Add Dependencies

xml
<dependencies>
    <!-- MyBatis Spring Boot Starter -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>3.0.3</version>
    </dependency>
    
    <!-- Database Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    
    <!-- Pagination Plugin (Optional) -->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.4.6</version>
    </dependency>
</dependencies>

2. Configuration

yaml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.entity
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

Basic Usage

Approach 1: Annotation-based (Simple Scenarios)

Entity Class

java
@Data
public class User {
    private Long id;
    private String username;
    private String email;
    private Integer age;
    private LocalDateTime createTime;
}

Mapper Interface

java
@Mapper
public interface UserMapper {
    
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectById(Long id);
    
    @Select("SELECT * FROM user")
    List<User> selectAll();
    
    @Insert("INSERT INTO user(username, email, age) VALUES(#{username}, #{email}, #{age})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
    
    @Update("UPDATE user SET username=#{username}, email=#{email}, age=#{age} WHERE id=#{id}")
    int update(User user);
    
    @Delete("DELETE FROM user WHERE id = #{id}")
    int deleteById(Long id);
    
    // Dynamic SQL example
    @Select("<script>" +
            "SELECT * FROM user " +
            "<where>" +
            "  <if test='username != null'>AND username LIKE CONCAT('%', #{username}, '%')</if>" +
            "  <if test='age != null'>AND age = #{age}</if>" +
            "</where>" +
            "</script>")
    List<User> selectByCondition(@Param("username") String username, @Param("age") Integer age);
}

Approach 2: XML-based (Complex Scenarios)

Mapper Interface

java
@Mapper
public interface UserMapper {
    User selectById(Long id);
    List<User> selectAll();
    int insert(User user);
    int update(User user);
    int deleteById(Long id);
}

XML Mapping File

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.example.mapper.UserMapper">
    
    <resultMap id="BaseResultMap" type="com.example.entity.User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="email" property="email"/>
        <result column="age" property="age"/>
        <result column="create_time" property="createTime"/>
    </resultMap>
    
    <select id="selectById" resultMap="BaseResultMap">
        SELECT * FROM user WHERE id = #{id}
    </select>
    
    <select id="selectAll" resultMap="BaseResultMap">
        SELECT * FROM user
    </select>
    
    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user(username, email, age, create_time)
        VALUES(#{username}, #{email}, #{age}, NOW())
    </insert>
    
    <update id="update">
        UPDATE user
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="email != null">email = #{email},</if>
            <if test="age != null">age = #{age}</if>
        </set>
        WHERE id = #{id}
    </update>
    
    <delete id="deleteById">
        DELETE FROM user WHERE id = #{id}
    </delete>
    
</mapper>

Advanced Features

1. One-to-One Association

xml
<resultMap id="UserWithOrderMap" type="com.example.entity.User">
    <id column="user_id" property="id"/>
    <result column="username" property="username"/>
    <association property="order" javaType="com.example.entity.Order">
        <id column="order_id" property="id"/>
        <result column="order_no" property="orderNo"/>
        <result column="amount" property="amount"/>
    </association>
</resultMap>

2. One-to-Many Association

xml
<resultMap id="UserWithOrdersMap" type="com.example.entity.User">
    <id column="user_id" property="id"/>
    <result column="username" property="username"/>
    <collection property="orders" ofType="com.example.entity.Order">
        <id column="order_id" property="id"/>
        <result column="order_no" property="orderNo"/>
        <result column="amount" property="amount"/>
    </collection>
</resultMap>

Using PageHelper:

java
@Service
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    public PageInfo<User> getUserPage(int pageNum, int pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        List<User> list = userMapper.selectAll();
        return new PageInfo<>(list);
    }
}

Transaction Management

java
@Service
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    @Autowired
    private OrderMapper orderMapper;
    
    @Transactional
    public void createUserWithOrder(User user, Order order) {
        userMapper.insert(user);
        order.setUserId(user.getId());
        orderMapper.insert(order);
        
        if (user.getAge() < 0) {
            throw new IllegalArgumentException("Invalid age");
        }
    }
    
    @Transactional(readOnly = true)
    public User getUserById(Long id) {
        return userMapper.selectById(id);
    }
}

Best Practices

1. Project Structure

shell
src/main/java/com/example/
├── entity/          # Entity classes
├── mapper/          # Mapper interfaces
├── service/         # Service layer
├── controller/      # Controller layer
└── dto/             # DTOs

src/main/resources/
├── mapper/          # XML mapping files
└── application.yml  # Configuration

2. Mapper Scanning

Option 1: Add @Mapper to each interface

Option 2: Unified scanning at startup class (recommended)

java
@SpringBootApplication
@MapperScan("com.example.mapper")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

3. SQL Writing Guidelines

Use XML for complex SQL, annotations for simple SQL
Use #{} to prevent SQL injection, avoid ${}
Use lazy loading for large fields
Always add ORDER BY for pagination queries

Summary

Feature	Annotation	XML
Simple SQL	✅ Recommended	Available
Complex SQL	Verbose	✅ Recommended
Dynamic SQL	Supported	✅ More Powerful
Maintainability	Average	✅ Good

Spring Boot + MyBatis provides flexible data access capabilities, suitable for scenarios requiring fine-grained SQL control.