Mybatis中多对多查询

数据要求查出用户的信息，用户下的订单，用户购买的商品详情，以及买的东西的名称，数量等具体信息。其中用户订单与商品详情订单是多对多关系。一个订单可以有多个商品详情，一个商品详情可以对应多个订单。

下面是我的数据库表：

要搞清楚如何编写代码主要是理清楚他们之间的关系：一个用户会下多个订单（list），订单对应多个商品详情（list），一个商品详情（商品编号，购买数量）对应一个商品信息（商品名称，商品生产等等）

下面是查询所有用户购买的商品信息。

user类：

package com.zwj.model;

import java.io.Serializable;
import java.util.Date;
import java.util.List;


public class User implements Serializable {
	private int id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址

	private List<Order> orders;

	public List<Order> getOrders() {
		return orders;
	}

	public void setOrders(List<Order> orders) {
		this.orders = orders;
	}

	public User() {
	}

	public User(String username, String sex, Date birthday, String address) {
		this.username = username;
		this.sex = sex;
		this.birthday = birthday;
		this.address = address;
	}

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}
}

order类：

package com.zwj.model;

import java.util.Date;
import java.util.List;

public class Order {

    private  Integer id;
    private  Integer user_id;
    private  String number;
    private Date createtime;
    private String note;

   // private User user;


    private List<Orderdetail> orderdetails;

    public List<Orderdetail> getOrderdetails() {
        return orderdetails;
    }

    public void setOrderdetails(List<Orderdetail> orderdetails) {
        this.orderdetails = orderdetails;
    }

    /*public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }*/

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", user_id=" + user_id +
                ", number='" + number + '\'' +
                ", createtime=" + createtime +
                ", note='" + note +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }
}

orderdetail类：

package com.zwj.model;

public class Orderdetail {

    private Integer id;
    private Integer itemsId;
    private  Integer itemsNum;

    private Items items;

    public Items getItems() {
        return items;
    }

    public void setItems(Items items) {
        this.items = items;
    }

    @Override
    public String toString() {
        return "Orderdetail{" +
                "id=" + id +
                ", itemsId=" + itemsId +
                ", itemsNum=" + itemsNum +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getItemsId() {
        return itemsId;
    }

    public void setItemsId(Integer itemsId) {
        this.itemsId = itemsId;
    }

    public Integer getItemsNum() {
        return itemsNum;
    }

    public void setItemsNum(Integer itemsNum) {
        this.itemsNum = itemsNum;
    }
}

items类：
package com.zwj.model;

public class Items {
private int id;
private String name;
private float price;
private String detail;

@Override
public String toString() {
    return "Items{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", price=" + price +
            ", detail='" + detail + '\'' +
            '}';
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public float getPrice() {
    return price;
}

public void setPrice(float price) {
    this.price = price;
}

public String getDetail() {
    return detail;
}

public void setDetail(String detail) {
    this.detail = detail;
}

}

接口方法：

    public List<User> findUserAndOrdeInfo();

映射代码段：

<resultMap id="findUserAndOrdeInfo1" type="User"><!--  type就写返回的模型的名称-->
        <id column="id" property="id"></id>
        <result column="username" property="username"></result><!--properties是真实的属性，column是别名-->
        <result column="address" property="address"></result>
        <collection property="orders" ofType="order">
            <id column="order_id" property="id"></id>
            <result column="number" property="number"></result><!--properties是真实的属性，column是别名-->
            <result column="createtime" property="createtime"></result>
            <result column="note" property="note"></result>
            <collection property="orderdetails" ofType="orderdetail">
                <id column="orderdetail_id" property="id"></id>
                <result column="items_id" property="itemsId"></result><!--properties是真实的属性，column是别名-->
                <result column="items_num" property="itemsNum"></result>
                <association property="items" javaType="items">
                    <id column="items_id" property="id"></id>
                    <result column="name" property="name"></result><!--properties是真实的属性，column是别名-->
                    <result column="price" property="price"></result>
                    <result column="detail" property="detail"></result>
                </association>
            </collection>
        </collection>
    </resultMap>

    <select id="findUserAndOrdeInfo" resultMap="findUserAndOrdeInfo1">
       SELECT u.id,
               u.username,
               u.address,
               o.id order_id,
               o.number,
               o.createtime,
               o.note,
               od.id orderdetail_id,
               od.items_id,
               od.items_num,
               it.`name`,
               it.price,
               it.detail
       FROM `user` u,orders o,orderdetail od,items it
       WHERE u.id=o.user_id AND o.id=od.orders_id AND od.items_id=it.id

测试：

@Test
    public void test4(){

       UserMapper findallmessage =session.getMapper(UserMapper.class);
        List<User> users=findallmessage.findUserAndOrdeInfo();
        for (User user:users){
            System.out.println(user);
            for(Order order:user.getOrders()){
                System.out.println("===============================================");
                System.out.println(order);
                for(Orderdetail orderdetail:order.getOrderdetails()){
                    System.out.println(orderdetail);
                    System.out.println(orderdetail.getItems());
                }
            }
        }
    }

结果：可以看到王五下了两笔订单，每一笔订单都有两个两个商品。

总结：我觉得很重要的是搞清楚各表之间的关系，如果是一对多，则在一的那个实体类中添加一个集合存储多的那个实体类。如果是一对一，就直接添加要关联查找的那个实体类作为属性。

欢迎指正哈！