MyBatis

MyBatis 是一个半自动 ORM 框架：SQL 由开发者编写，MyBatis 负责参数绑定、结果映射和会话管理。相比 JPA，MyBatis 对复杂 SQL 和存储过程有更强的控制力。

MySQL 原生 SQL / 客户端命令速查：MySQL 基础命令。

---

依赖配置

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

# application.yml
mybatis:
  mapper-locations: classpath:mapper/**/*.xml   # XML 文件路径
  type-aliases-package: com.example.domain      # 实体类包（XML 中可用简名）
  configuration:
    map-underscore-to-camel-case: true           # user_name → userName
    default-fetch-size: 100
    default-statement-timeout: 30
    lazy-loading-enabled: true
    cache-enabled: false                         # 关闭二级缓存（生产慎用）
  type-handlers-package: com.example.handler    # 自定义 TypeHandler 包

---

一、Mapper 接口

@Mapper   // 或在启动类加 @MapperScan("com.example.mapper")
public interface OrderMapper {
 
    Order findById(Long id);
 
    List<Order> findByUserId(Long userId);
 
    // 直接返回 Map（适合临时聚合查询）
    List<Map<String, Object>> findOrderStats(@Param("startDate") LocalDate startDate);
 
    int insert(Order order);
 
    int updateStatus(@Param("id") Long id, @Param("status") OrderStatus status);
 
    int deleteById(Long id);
 
    // 批量插入
    int batchInsert(@Param("list") List<OrderItem> items);
}

---

二、注解方式（简单 SQL）

@Mapper
public interface UserMapper {
 
    @Select("SELECT * FROM users WHERE id = #{id}")
    @Results(id = "userResultMap", value = {
        @Result(property = "id",        column = "id"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "orders",
                column = "id",
                many = @Many(select = "com.example.mapper.OrderMapper.findByUserId",
                             fetchType = FetchType.LAZY))
    })
    User findById(Long id);
 
    @Insert("INSERT INTO users(name, email, created_at) VALUES(#{name}, #{email}, NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")   // 回填自增主键
    int insert(User user);
 
    @Update("UPDATE users SET name=#{name}, email=#{email} WHERE id=#{id}")
    int update(User user);
 
    @Delete("DELETE FROM users WHERE id = #{id}")
    int deleteById(Long id);
 
    // 动态 SQL（注解内使用 script 标签）
    @Select("""
        <script>
        SELECT * FROM users
        WHERE 1=1
        <if test="name != null">AND name LIKE CONCAT('%', #{name}, '%')</if>
        <if test="status != null">AND status = #{status}</if>
        ORDER BY created_at DESC
        </script>
        """)
    List<User> search(@Param("name") String name, @Param("status") String status);
}

---

三、XML Mapper（复杂 SQL）

<!-- resources/mapper/OrderMapper.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.OrderMapper">
 
    <!-- ResultMap：处理列名与字段名不一致、关联查询 -->
    <resultMap id="OrderResultMap" type="Order">
        <id     property="id"          column="id"/>
        <result property="orderNo"     column="order_no"/>
        <result property="totalAmount" column="total_amount"/>
        <result property="status"      column="status"
                typeHandler="com.example.handler.OrderStatusTypeHandler"/>
        <result property="createdAt"   column="created_at"/>
 
        <!-- 关联单个对象（N+1 风险，小数据量场景） -->
        <association property="user" columnPrefix="u_">
            <id     property="id"    column="id"/>
            <result property="name"  column="name"/>
            <result property="email" column="email"/>
        </association>
 
        <!-- 关联集合（JOIN 查询） -->
        <collection property="items" ofType="OrderItem">
            <id     property="id"          column="item_id"/>
            <result property="productName" column="product_name"/>
            <result property="quantity"    column="quantity"/>
            <result property="unitPrice"   column="unit_price"/>
        </collection>
    </resultMap>
 
    <!-- JOIN 查询，一次加载订单及其用户和明细 -->
    <select id="findById" resultMap="OrderResultMap">
        SELECT
            o.id, o.order_no, o.total_amount, o.status, o.created_at,
            u.id   AS u_id,
            u.name AS u_name,
            u.email AS u_email,
            i.id           AS item_id,
            i.product_name,
            i.quantity,
            i.unit_price
        FROM orders o
        LEFT JOIN users u ON o.user_id = u.id
        LEFT JOIN order_items i ON i.order_id = o.id
        WHERE o.id = #{id}
    </select>
 
    <!-- 动态 SQL -->
    <select id="findByCondition" resultMap="OrderResultMap">
        SELECT o.* FROM orders o
        <where>
            <if test="userId != null">
                AND o.user_id = #{userId}
            </if>
            <if test="status != null">
                AND o.status = #{status}
            </if>
            <if test="startDate != null">
                AND o.created_at &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                AND o.created_at &lt;= #{endDate}
            </if>
        </where>
        ORDER BY o.created_at DESC
        <if test="limit != null">
            LIMIT #{limit} OFFSET #{offset}
        </if>
    </select>
 
    <!-- 批量插入 -->
    <insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO order_items(order_id, product_name, quantity, unit_price)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.orderId}, #{item.productName}, #{item.quantity}, #{item.unitPrice})
        </foreach>
    </insert>
 
    <!-- CHOOSE（类似 switch-case） -->
    <select id="findOrderStats" resultType="map">
        SELECT
            DATE_TRUNC(
                <choose>
                    <when test="groupBy == 'day'">  'day'  </when>
                    <when test="groupBy == 'month'">'month'</when>
                    <otherwise>                     'year' </otherwise>
                </choose>
            , created_at) AS period,
            COUNT(*) AS order_count,
            SUM(total_amount) AS total_revenue
        FROM orders
        WHERE created_at &gt;= #{startDate}
        GROUP BY period
        ORDER BY period
    </select>
 
    <!-- SET 标签：更新时忽略 null 字段 -->
    <update id="updateSelective">
        UPDATE orders
        <set>
            <if test="status != null">status = #{status},</if>
            <if test="totalAmount != null">total_amount = #{totalAmount},</if>
            <if test="remark != null">remark = #{remark},</if>
            updated_at = NOW()
        </set>
        WHERE id = #{id}
    </update>
 
</mapper>

---

四、SQL 片段复用

<mapper namespace="com.example.mapper.OrderMapper">
 
    <!-- 可复用的列列表 -->
    <sql id="orderColumns">
        o.id, o.order_no, o.total_amount, o.status, o.created_at, o.user_id
    </sql>
 
    <!-- 可复用的 WHERE 条件 -->
    <sql id="orderWhere">
        <where>
            <if test="userId != null">AND o.user_id = #{userId}</if>
            <if test="status != null">AND o.status = #{status}</if>
        </where>
    </sql>
 
    <select id="findList" resultMap="OrderResultMap">
        SELECT <include refid="orderColumns"/>
        FROM orders o
        <include refid="orderWhere"/>
        ORDER BY o.created_at DESC
    </select>
 
</mapper>

---

五、TypeHandler（自定义类型转换）

// 枚举 ↔ 数据库字符串的双向转换
@MappedTypes(OrderStatus.class)
@MappedJdbcTypes(JdbcType.VARCHAR)
public class OrderStatusTypeHandler extends BaseTypeHandler<OrderStatus> {
 
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                    OrderStatus param, JdbcType jdbcType)
            throws SQLException {
        ps.setString(i, param.getCode());   // 存枚举的 code 字段
    }
 
    @Override
    public OrderStatus getNullableResult(ResultSet rs, String columnName)
            throws SQLException {
        return OrderStatus.fromCode(rs.getString(columnName));
    }
 
    @Override
    public OrderStatus getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return OrderStatus.fromCode(rs.getString(columnIndex));
    }
 
    @Override
    public OrderStatus getNullableResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        return OrderStatus.fromCode(cs.getString(columnIndex));
    }
}

JSON 字段处理（JSONB 列 ↔ Java 对象）：

@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes(Object.class)
public class JsonTypeHandler<T> extends BaseTypeHandler<T> {
 
    private final Class<T> type;
    private static final ObjectMapper MAPPER = new ObjectMapper();
 
    public JsonTypeHandler(Class<T> type) {
        this.type = type;
    }
 
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                    T param, JdbcType jdbcType) throws SQLException {
        PGobject obj = new PGobject();
        obj.setType("jsonb");
        obj.setValue(MAPPER.writeValueAsString(param));
        ps.setObject(i, obj);
    }
 
    @Override
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return parse(rs.getString(columnName));
    }
 
    private T parse(String json) throws SQLException {
        if (json == null) return null;
        try {
            return MAPPER.readValue(json, type);
        } catch (JsonProcessingException e) {
            throw new SQLException("JSON 解析失败", e);
        }
    }
    // ... 其余重写方法类似
}

---

六、分页插件（PageHelper）

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>

pagehelper:
  helper-dialect: postgresql
  reasonable: true         # 页码越界时自动修正
  support-methods-arguments: true

@Service
@RequiredArgsConstructor
public class OrderService {
 
    private final OrderMapper orderMapper;
 
    public PageInfo<Order> findOrders(Long userId, int pageNum, int pageSize) {
        // PageHelper 使用 ThreadLocal，必须紧接在查询前调用
        PageHelper.startPage(pageNum, pageSize);
        List<Order> list = orderMapper.findByUserId(userId);
        return new PageInfo<>(list);   // 包含 total、pages 等分页信息
    }
}

---

七、多数据源

@Configuration
@MapperScan(basePackages = "com.example.mapper.order",
            sqlSessionFactoryRef = "orderSqlSessionFactory")
public class OrderDataSourceConfig {
 
    @Bean
    @ConfigurationProperties("spring.datasource.order")
    public DataSource orderDataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean
    public SqlSessionFactory orderSqlSessionFactory(
            @Qualifier("orderDataSource") DataSource ds) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ds);
        bean.setMapperLocations(
            new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/order/**/*.xml"));
        return bean.getObject();
    }
}

多数据源详见 多数据源。

---

八、常见问题

问题	原因	解决
#{} vs ${} 混用	${} 直接拼接 SQL，有注入风险	参数值用 #{}；列名/表名动态时用 ${} 并校验白名单
N+1	<association> 嵌套 select	改为 JOIN 查询 + <association> 列前缀映射
返回自增 ID	未配置 useGeneratedKeys	加 @Options(useGeneratedKeys=true, keyProperty="id")
枚举值乱码	默认按序号存储	注册 EnumOrdinalTypeHandler 或自定义 TypeHandler
批量操作慢	逐条 insert	用 <foreach> 批量插入或开启 rewriteBatchedStatements=true

---

相关链接

• MySQL 基础命令
• MySQL 专题
• MyBatis-Plus — MyBatis 增强：代码生成、条件构造器、分页插件
• JPA与Hibernate — 全自动 ORM，适合简单 CRUD
• 数据访问 — Spring Data 整体架构
• 事务管理 — @Transactional 与 MyBatis 的集成
• 多数据源 — 多数据源配置
• 读写分离 — 主从路由
• 数据库迁移 — Flyway 管理 DDL
• 连接池配置 — HikariCP 调优
• 性能调优 — SQL 慢查询分析