MyBatis多对多查询样例——通过临时表实现(注:懒加载mybatis版本需大于3.5.1)

表关系

Orders与Travller为多对多关系

Orders:订单表,一个订单有多个traveller。

Traveller:游客表，一个Travller有多个订单

中间表：order_traveller，保存有两者的id

业务需求:查询订单详情包括订单中的多个旅客

dao层代码：

public interface OrdersDao {

    @Select("select * from orders")
    @Results(
            id = "ordersMap",
            value = {
                    @Result(id = true, column = "id", property = "id"),
                    @Result(column = "orderNum", property = "orderNum"),
                    @Result(column = "orderTime", property = "orderTime"),
                    @Result(column = "orderStatus", property = "orderStatus"),
                    @Result(column = "peopleCount", property = "peopleCount"),
                    @Result(column = "payType", property = "payType"),
                    @Result(column = "orderDesc", property = "orderDesc"),
                    @Result(column = "productID", property = "product", javaType = Product.class, one = @One(select = "com.czy.ssm.dao.ProductDao.findById", fetchType = FetchType.EAGER)),
                    @Result(column = "memberID", property = "member", javaType = Member.class, one = @One(select = "com.czy.ssm.dao.MemberDao.findById", fetchType = FetchType.EAGER)),
                    @Result(column = "ID", property = "travellers", javaType = java.util.List.class, many = @Many(select = "com.czy.ssm.dao.TravellerDao.findByOrdersId", fetchType = FetchType.LAZY))
            }
    )
    public List<Orders> findAll();

    @Select("select * from orders where id = #{id}")
    @ResultMap("ordersMap")
    Orders findById(String id);
}

public interface TravellerDao {
    @Select("select * from traveller where id in (select travellerId from order_traveller where orderId = #{id})")
    List<Traveller> findByOrdersId(String orderId);
}