【Mybatis】一对多查询

如果有顺序要求，千万不要用set

与Phoenix不同，在mysql中查询的结果不会按照id默认排序。所以如果页面有隐含的顺序要求（两次调用，列表顺序不变）。此时千万不要使用set，而应该使用list。接下来就进入正题

一对多

直接上代码

public class User {
    private Long id;
    private String username;
    private Integer age;
    private List<Order> orderList;
}

public class Order {
    private Long id;
    private String order_no;
    private BigDecimal price;

}

UserMapper.java

public interface UserMapper {
    /**
     * 根据ID查询用户，并且查询出该用户所有订单
     * @param id
     * @return
     */
    User selectByIdWithOrder(Long id);
}

UserMapper.xml

    <resultMap id="userWithOrderMap" type="cn.saytime.domain.User">
        <id property="id" column="uid" javaType="java.lang.Long"></id>
        <result property="username" column="username" javaType="java.lang.String"></result>
        <result property="age" column="age" javaType="java.lang.Integer"></result>
        <collection property="orderList" javaType="java.util.List" ofType="cn.saytime.domain.Order">
            <id property="id" column="oid" javaType="java.lang.Long"></id>
            <result property="order_no" column="order_no" javaType="java.lang.String"></result>
            <result property="price" column="price" javaType="java.math.BigDecimal"></result>
        </collection>
    </resultMap>

    <select id="selectByIdWithOrder" parameterType="java.lang.Long" resultMap="userWithOrderMap">
        SELECT u.id AS uid, u.username, u.age, o.id AS oid, o.order_no, o.price
        FROM tb_user u
        JOIN tb_order o ON u.id = o.uid
        WHERE u.id = #{id}
    </select>