MyBatis用法教程

一、Spring Boot 集成 MyBatis

1. 添加依赖

<!-- pom.xml -->
<dependencies>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>3.0.3</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2. 配置文件

# application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  mapper-locations: classpath:mapper/*.xml  # 指定 XML 映射文件路径
  configuration:
    map-underscore-to-camel-case: true      # 开启驼峰命名映射

3. 实体类

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

二、增删改查示例

1. Mapper 接口定义

@Mapper
public interface UserMapper {
    // 插入用户（返回自增主键）
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("INSERT INTO user(name, age, email) VALUES(#{name}, #{age}, #{email})")
    int insert(User user);

    // 根据 ID 删除用户
    @Delete("DELETE FROM user WHERE id = #{id}")
    int deleteById(Long id);

    // 更新用户（动态 SQL 示例）
    @UpdateProvider(type = UserSqlBuilder.class, method = "buildUpdateSql")
    int update(User user);

    // 根据 ID 查询用户
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectById(Long id);

    // 分页查询用户（结合 PageHelper）
    @Select("SELECT * FROM user")
    List<User> selectAll();
}

2. XML 映射文件示例

<!-- resources/mapper/UserMapper.xml -->
<mapper namespace="com.example.mapper.UserMapper">
    <!-- 动态更新 SQL -->
    <update id="update" parameterType="User">
        UPDATE user
        <set>
            <if test="name != null">name = #{name},</if>
            <if test="age != null">age = #{age},</if>
            <if test="email != null">email = #{email},</if>
        </set>
        WHERE id = #{id}
    </update>
</mapper>

3. Service 层调用

@Service
@RequiredArgsConstructor
public class UserService {
    private final UserMapper userMapper;

    public void addUser(User user) {
        userMapper.insert(user);
    }

    public void deleteUser(Long id) {
        userMapper.deleteById(id);
    }

    public void updateUser(User user) {
        userMapper.update(user);
    }

    public User getUserById(Long id) {
        return userMapper.selectById(id);
    }

    public List<User> getAllUsers() {
        return userMapper.selectAll();
    }
}

三、动态 SQL 实战

1. 使用 <if> 和 <where>

<!-- 动态条件查询 -->
<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    <where>
        <if test="name != null">AND name LIKE CONCAT('%', #{name}, '%')</if>
        <if test="age != null">AND age = #{age}</if>
    </where>
</select>

2. 批量插入

<insert id="batchInsert">
    INSERT INTO user(name, age, email)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.name}, #{user.age}, #{user.email})
    </foreach>
</insert>

3. 注解方式动态 SQL

// 使用 @SelectProvider 动态生成 SQL
public class UserSqlBuilder {
    public String buildUpdateSql(User user) {
        return new SQL()
            .UPDATE("user")
            .SET("name = #{name}")
            .SET("age = #{age}")
            .WHERE("id = #{id}")
            .toString();
    }
}

四、高级功能整合

1. 分页插件（PageHelper）

# application.yml
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true

// Service 层分页查询
public PageInfo<User> getUsersByPage(int pageNum, int pageSize) {
    PageHelper.startPage(pageNum, pageSize);
    List<User> users = userMapper.selectAll();
    return new PageInfo<>(users);
}

2. 事务管理

@Service
@Transactional  // 声明式事务
public class UserService {
    public void transferMoney(Long fromId, Long toId, BigDecimal amount) {
        userMapper.deductBalance(fromId, amount);
        userMapper.addBalance(toId, amount);
    }
}

3. 多数据源配置

# application.yml
spring:
  datasource:
    primary:
      url: jdbc:mysql://localhost:3306/db1
      username: root
      password: 123456
    secondary:
      url: jdbc:mysql://localhost:3306/db2
      username: root
      password: 123456

@Configuration
@MapperScan(basePackages = "com.example.mapper.primary", sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfig {
    // 配置 primary 数据源和 SqlSessionFactory
}

@Configuration
@MapperScan(basePackages = "com.example.mapper.secondary", sqlSessionFactoryRef = "secondarySqlSessionFactory")
public class SecondaryDataSourceConfig {
    // 配置 secondary 数据源和 SqlSessionFactory
}

五、常见问题与解决

1. Mapper 接口未被扫描

• 确保主类添加 @MapperScan("com.example.mapper")。

2. 参数绑定错误

• 多参数时使用 @Param 注解：

@Select("SELECT * FROM user WHERE name = #{name} AND age = #{age}")
User findByNameAndAge(@Param("name") String name, @Param("age") Integer age);

3. 事务失效

• 确保 @Transactional 注解在 public 方法上，且未被同类方法内部调用。

六、完整代码结构示例

src/main/java
├── com.example
│   ├── Application.java          # Spring Boot 主类
│   ├── entity
│   │   └── User.java            # 实体类
│   ├── mapper
│   │   ├── UserMapper.java       # Mapper 接口
│   │   └── primary              # 多数据源示例
│   ├── service
│   │   └── UserService.java      # Service 层
│   └── config                   # 配置类（多数据源等）
resources
├── application.yml              # 配置文件
└── mapper
    └── UserMapper.xml            # XML 映射文件