MyBatis多表查询和注解开发

// 假设有一个User实体类和一个Order实体类，以及相应的数据库表
public class User {
    private Integer id;
    private String username;
    // 省略其他属性、构造函数、getter和setter
}
 
public class Order {
    private Integer id;
    private Integer userId;
    private String orderNumber;
    // 省略其他属性、构造函数、getter和setter
}
 
// 映射文件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.example.mapper.UserMapper">
 
    <!-- 定义结果映射 -->
    <resultMap id="UserOrderResultMap" type="com.example.entity.User">
        <id property="id" column="user_id"/>
        <result property="username" column="username"/>
        <!-- 定义关联的订单信息 -->
        <collection property="orders" ofType="com.example.entity.Order">
            <id property="id" column="order_id"/>
            <result property="userId" column="user_id"/>
            <result property="orderNumber" column="order_number"/>
        </collection>
    </resultMap>
 
    <!-- 使用结果映射的查询多表 -->
    <select id="selectUserWithOrders" resultMap="UserOrderResultMap">
        SELECT u.id AS user_id, u.username, o.id AS order_id, o.user_id, o.order_number
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE u.id = #{id}
    </select>
 
</mapper>
 
// UserMapper接口
public interface UserMapper {
    User selectUserWithOrders(Integer id);
}
 
// 使用MyBatis注解开发，无需XML映射文件
public interface UserMapper {
 
    @Select("SELECT u.id, u.username, o.id AS order_id, o.user_id, o.order_number " +
            "FROM users u " +
            "LEFT JOIN orders o ON u.id = o.user_id " +
            "WHERE u.id = #{id}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "orders", column = "order_id", javaType = List.class, 
                    many = @Many(select = "selectOrdersByUserId"))
    })
    User selectUserWithOrders(Integer id);
 
    @Select("SELECT id, user_id, order_number FROM orders WHERE user_id = #{userId}")
    List<Order> selectOrdersByUserId(Integer userId);
}

在这个例子中，我们展示了如何使用MyBatis进行多表查询，并且如何使用结果映射<resultMap>以及注解@Results和@Many来简化结果的映射处理。这样的