Mybaties多对多映射

查找用户订单中的商品信息

商品和用户对应关系为多对多
定义sql

SELECT 
	USER.id,
	USER.username,
	USER.address,
	orders.id orderid,
	orders.number,
	orders.createtime,
	orders.note,
	orderdetail.id orderdetailid,
	orderdetail.items_id,
	orderdetail.items_num,
	items.NAME itemsname,
	items.price,
	items.detail 
FROM
	USER,
	orders,
	orderdetail,
	items 
WHERE
	USER.id = orders.user_id 
	AND orders.id = orderdetail.orders_id 
	AND orderdetail.items_id = items.id

Bean

在User类中添加Orders属性

在Orders类中添加Orderdetails属性

在Orderdetails类中添加Items属性

在UserMapper中定义接口方法

public interface UserMapper {
    public List<User> findUserBymanytomany();
}

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">
<!--
	namespace：命名空间，它的作用就是对SQL进行分类化管理，可以理解为SQL隔离
	注意：使用mapper代理开发时，namespace有特殊且重要的作用
 -->
<mapper namespace="edu.ccit.cn.mapper.UserMapper">
    <resultMap id="manytomany" type="User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="address" property="address"/>
        <collection property="orders" ofType="Orders">
            <id column="orderid" property="id"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <collection property="orderdetails" ofType="Orderdetail">
                <id column="orderdetailid" property="id"/>
                <result column="items_id" property="itemsid"/>
                <result column="items_num" property="itemsnum"/>
                <association property="items" javaType="Items">
                    <result column="itemsname" property="name"/>
                    <result column="price" property="price"/>
                    <result column="detail" property="detail" jdbcType="VARCHAR"/>
                </association>
            </collection>
        </collection>
    </resultMap>
    <select id="findUserBymanytomany" resultMap="manytomany">
        SELECT
            user.id,
            user.username,
            user.address,
            orders.id orderid,
            orders.number,
            orders.createtime,
            orderdetail.id orderdetailid,
            orderdetail.items_id,
            orderdetail.items_num,
            items.name itemsname,
            items.price,
            items.detail
        FROM
            user,orders,orderdetail,items
        WHERE
            user.id=orders.user_id AND
            orders.id=orderdetail.orders_id AND
            orderdetail.items_id=items.id
    </select>
</mapper>

测试

 @Test
    public void method04_manyTOmany(){
        UserMapper mapper = ssf.openSession().getMapper(UserMapper.class);
        List<User> userBymanytomany = mapper.findUserBymanytomany();
        for(User user:userBymanytomany){
            for(Orders order:user.getOrders()){
                for(Orderdetail od:order.getOrderdetails()){
                    System.out.println(od);
                }
            }
        }
    }