Spring Boot 中如何整合 MyBatis 进行数据库操作？ - 面试题

Spring Boot 整合 MyBatis 详解

为什么要用 MyBatis

SQL 可控：手写 SQL，便于优化复杂查询
轻量级：相比 JPA，学习成本低
灵活：支持动态 SQL、存储过程
与 Spring Boot 无缝集成：mybatis-spring-boot-starter 提供自动配置

环境准备

1. 添加依赖

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>
    
    <!-- 数据库驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    
    <!-- 连接池（可选，Spring Boot 默认使用 HikariCP） -->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
    
    <!-- 分页插件（可选） -->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.4.6</version>
    </dependency>
</dependencies>

2. 配置文件

yaml
# application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      idle-timeout: 300000
      connection-timeout: 20000

# MyBatis 配置
mybatis:
  # Mapper XML 文件位置
  mapper-locations: classpath:mapper/*.xml
  # 实体类包路径（配置别名）
  type-aliases-package: com.example.entity
  # 驼峰命名自动映射
  configuration:
    map-underscore-to-camel-case: true
    # 打印 SQL
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 全局配置文件（可选，与 configuration 互斥）
  # config-location: classpath:mybatis-config.xml

基础使用方式

方式一：注解方式（推荐简单场景）

实体类

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

Mapper 接口

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);
    
    // 动态 SQL 示例
    @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);
}

方式二：XML 方式（推荐复杂场景）

Mapper 接口

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

XML 映射文件（resources/mapper/UserMapper.xml）

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>
    
    <!-- 动态条件查询 -->
    <select id="selectByCondition" resultMap="BaseResultMap">
        SELECT * FROM user
        <where>
            <if test="username != null and username != ''">
                AND username LIKE CONCAT('%', #{username}, '%')
            </if>
            <if test="age != null">
                AND age = #{age}
            </if>
            <if test="email != null and email != ''">
                AND email = #{email}
            </if>
        </where>
        ORDER BY create_time DESC
    </select>
    
    <!-- 批量插入 -->
    <insert id="batchInsert">
        INSERT INTO user(username, email, age, create_time) VALUES
        <foreach collection="list" item="user" separator=",">
            (#{user.username}, #{user.email}, #{user.age}, NOW())
        </foreach>
    </insert>
    
    <!-- 批量删除 -->
    <delete id="batchDelete">
        DELETE FROM user WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    
</mapper>

高级特性

1. 一对一关联查询

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>

<select id="selectUserWithOrder" resultMap="UserWithOrderMap">
    SELECT 
        u.id as user_id,
        u.username,
        o.id as order_id,
        o.order_no,
        o.amount
    FROM user u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{userId}
</select>

2. 一对多关联查询

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>

<select id="selectUserWithOrders" resultMap="UserWithOrdersMap">
    SELECT 
        u.id as user_id,
        u.username,
        o.id as order_id,
        o.order_no,
        o.amount
    FROM user u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{userId}
</select>

3. 分页查询

使用 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);
    }
    
    public PageInfo<User> searchUsers(UserQueryDTO query, int pageNum, int pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        List<User> list = userMapper.selectByCondition(query);
        return new PageInfo<>(list);
    }
}

4. 多数据源配置

yaml
spring:
  datasource:
    primary:
      jdbc-url: jdbc:mysql://localhost:3306/db1
      username: root
      password: pass1
    secondary:
      jdbc-url: jdbc:mysql://localhost:3306/db2
      username: root
      password: pass2

# MyBatis 配置
mybatis:
  mapper-locations: classpath:mapper/**/*.xml

java
@Configuration
@MapperScan(basePackages = "com.example.mapper.primary", 
             sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfig {
    
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean(name = "primarySqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(
            @Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/primary/*.xml"));
        return bean.getObject();
    }
    
    @Bean(name = "primarySqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(
            @Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

事务管理

java
@Service
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    @Autowired
    private OrderMapper orderMapper;
    
    @Transactional
    public void createUserWithOrder(User user, Order order) {
        // 插入用户
        userMapper.insert(user);
        
        // 设置订单用户ID
        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);
    }
    
    @Transactional(rollbackFor = Exception.class)
    public void updateUser(User user) {
        userMapper.update(user);
    }
}

代码生成器

使用 MyBatis Generator 自动生成代码：

xml
<!-- pom.xml -->
<plugin>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-maven-plugin</artifactId>
    <version>1.4.2</version>
</plugin>

xml
<!-- generatorConfig.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration 
    PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
    "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <context id="default" targetRuntime="MyBatis3">
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/mydb"
                        userId="root"
                        password="password"/>
        
        <javaModelGenerator targetPackage="com.example.entity" 
                           targetProject="src/main/java"/>
        
        <sqlMapGenerator targetPackage="mapper" 
                        targetProject="src/main/resources"/>
        
        <javaClientGenerator type="XMLMAPPER" 
                            targetPackage="com.example.mapper"
                            targetProject="src/main/java"/>
        
        <table tableName="user" domainObjectName="User"/>
    </context>
</generatorConfiguration>

最佳实践

1. 项目结构

shell
src/main/java/com/example/
├── entity/          # 实体类
├── mapper/          # Mapper 接口
├── service/         # 业务层
├── controller/      # 控制层
└── dto/             # 数据传输对象

src/main/resources/
├── mapper/          # XML 映射文件
├── application.yml  # 配置文件
└── mybatis-config.xml  # MyBatis 全局配置（可选）

2. Mapper 扫描方式

方式1：每个 Mapper 加 @Mapper 注解

方式2：启动类统一扫描（推荐）

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

3. SQL 编写规范

复杂 SQL 使用 XML，简单 SQL 使用注解
使用 #{} 防止 SQL 注入，避免使用 ${}
大字段使用延迟加载
分页查询必须加 ORDER BY

4. 性能优化

yaml
mybatis:
  configuration:
    # 开启二级缓存
    cache-enabled: true
    # 延迟加载
    lazy-loading-enabled: true
    # 积极加载
    aggressive-lazy-loading: false
    # 默认执行器
    default-executor-type: reuse

常见问题

Q1: Invalid bound statement (not found)

检查 Mapper 接口和 XML 的 namespace 是否一致
检查方法名是否匹配
确认 XML 文件在编译后的 target 目录中

Q2: 如何打印 SQL 日志？

yaml
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

# 或使用日志框架
logging:
  level:
    com.example.mapper: debug

Q3: 如何处理枚举类型？

java
public enum Status {
    ACTIVE, INACTIVE;
}

// 自定义 TypeHandler
@MappedTypes(Status.class)
public class StatusTypeHandler extends BaseTypeHandler<Status> {
    // 实现方法
}

总结

特性	注解方式	XML 方式
简单 SQL	✅ 推荐	可用
复杂 SQL	繁琐	✅ 推荐
动态 SQL	支持（@SelectProvider）	✅ 更强大
维护性	一般	✅ 好

Spring Boot + MyBatis 的组合提供了灵活的数据访问能力，适合需要精细控制 SQL 的场景。