mybatis的一对多和多对多查询

一对多和多对多的关系

我是以你平时买东西为例子的，一个用户对应多个订单，一个订单对应多个订单明细，一个订单明细对应一个商品，根据这些关系来进行实例演示。

实例演示

一对多（一个订单对应多个订单明细） 1）定义一个用户实体类User.java package com; import java.util.Date; import java.util.List; public class User { private Integer id; // 姓名 private String username; // 性别 private String sex; // 地址 private String address; // 生日 private Date birthday; // 用户创建的订单列表 private List<Orders> ordersList; public Integer getId() { return id; } public void setId(Integer 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 String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public List<Orders> getOrdersList() { return ordersList; } public void setOrdersList(List<Orders> ordersList) { this.ordersList = ordersList; } @Override public String toString() { return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", address=" + address + ", birthday=" + birthday + "]"; } }

2)定义一个订单实体类Order.java

package com; import java.util.Date; import java.util.List; public class Orders { /** 主键订单Id */ private Integer id; /** 下单用户id */ private Integer userid; /** 订单号 */ private String number; /** 创建订单时间 */ private Date createTime; /** 备注 */ private String note; // 用户信息 private User user; // 订单明细 private List<OrderDetail> orderdetails; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getUserid() { return userid; } public void setUserid(Integer userid) { this.userid = userid; } 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; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } public List<OrderDetail> getOrderdetails() { return orderdetails; } public void setOrderdetails(List<OrderDetail> orderdetails) { this.orderdetails = orderdetails; } @Override public String toString() { return "Orders [id=" + id + ", userid=" + userid + ", number=" + number + ", createTime=" + createTime + ", note=" + note + ", user=" + user + "]"; } }

3)订单明细实体类 OrderDetail.java

package com; public class OrderDetail { /** 主鍵，明细表Id */ private Integer id; /** 訂單Id */ private Integer ordersId; /** 商品id */ private Integer itemsId; /** 商品购买数量 */ private Integer itemsNum; // 明细对应的商品信息 private Items items; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getOrdersId() { return ordersId; } public void setOrdersId(Integer ordersId) { this.ordersId = ordersId; } 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; } public Items getItems() { return items; } public void setItems(Items items) { this.items = items; } @Override public String toString() { return "OrderDetail [id=" + id + ", ordersId=" + ordersId + ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + "]"; } }

4)商品实体类

package com; import java.util.Date; public class Items { /** 商品表主键Id */ private Integer id; /** 商品名称 */ private String itemsName; /** 商品定价 */ private float price; /** 商品描述 */ private String detail; /** 商品图片 */ private String picture; /** 生产日期 */ private Date createTime; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getItemsName() { return itemsName; } public void setItemsName(String itemsName) { this.itemsName = itemsName; } 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 String getPicture() { return picture; } public void setPicture(String picture) { this.picture = picture; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } @Override public String toString() { return "Items [id=" + id + ", itemsName=" + itemsName + ", price=" + price + ", detail=" + detail + ", picture=" + picture + ", createTime=" + createTime + "]"; } }

5）几个实体类写完了之后根据他们的关系来进行编写对应的mapper.xml文件了。 一个订单对应多个订单明细对应的OrdersCustomMapper.xml

<mapper namespace="com.OrdersCustomMapper"> <!-- 一对多查询，一个订单对应多个订单明细 --> <select id="findOrdersAndOrderDetailResultMap" resultMap="ordersAndOrderDetailResultMap"> SELECT t1.*, t2.username, t2.sex, t2.address, t3.id orderdetail_id, t3.items_id, t3.items_num, t3.orders_id FROM orders t1, t_user t2, orderdetail t3 WHERE t1.user_id = t2.id AND t3.orders_id=t1.id </select> <resultMap type="com.Orders" id="OrdersUserResultMap"> <id column="id" property="id"/> <result column="user_id" property="userid"/> <result column="number" property="number"/> <result column="createtime" property="createTime"/> <result column="note" property="note"/> <!-- 订单关联用户 --> <association property="user" javaType="com.User"> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap> </mapper>

6)OrdersCustomMapper.xml文件完成之后编写OrdersCustomMapper.java文件(面向接口编程的，这个类的路径就是OrdersCustomMapper.xml中命名空间的名字，其中的方法就是OrdersCustomMapper.xml中的select的id对应的方法)

package com; import java.util.List; public interface OrdersCustomMapper { /**查询订单以及订单明细**/ public List<OrdersCustom> findOrdersAndOrderDetailResultMap(); }

7)测试类Test.java

package pojo; import java.io.IOException; import java.io.Reader; import java.util.List; import java.util.Map; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.OrdersCustom; import com.OrdersCustomMapper; import com.Student; import com.User; public class Test { public static void main(String[] args) throws IOException { String resource = "sqlConfig.xml"; Reader reader = Resources.getResourceAsReader(resource); SqlSessionFactory sqlMapper = new SqlSessionFactoryBuilder().build(reader); SqlSession session = sqlMapper.openSession(); // Student student = new Student(); // session.insert("com.StudentMapper.insertStudent", student); //插入操作 // session.select("com.StudentMapper.selectStudent",student); //查询操作 // List<Student> students = session.selectList("com.StudentMapper.selectStudent"); // session.commit(); // System.out.println(students); //Map<String, Object> map = session.select("com.StudentMapper.selectUsers", null); // Map<String, Object> map = session.selectMap("com.StudentMapper.selectUsers","id");//resultMap方法查询 // System.out.println("集合的长度="+map.size()); // System.out.println(map); /**一对一查询，一个订单对应一个用户**/ OrdersCustomMapper oc = session.getMapper(OrdersCustomMapper.class); List<OrdersCustom> list = oc.findOrdersUser(); System.out.println(list); /**一对多查询，一个订单对应多个订单明细**/ List<OrdersCustom> list2 = oc.findOrdersAndOrderDetailResultMap(); System.out.println("list2="+list2); /**多对多查询，一个用户对应多个订单，一个订单对应多个订单明细，一个订单明细对应一个商品 **/ List<User> list3 = oc.findUserAndItemsResultMap(); System.out.println("list3="+list3); } } 多对多其实跟一对多差不多，只不过修改一下OrdersCustomMapper.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.OrdersCustomMapper"> <!-- 一对一查询 ，一个订单对应一个用户--> <select id="findOrdersUser" resultType="com.OrdersCustom" resultMap="OrdersUserResultMap"> SELECT t1.*, t2.username, t2.sex, t2.address FROM orders t1, t_user t2 WHERE t1.user_id=t2.id </select> <!-- 一对多查询，一个订单对应多个订单明细 --> <select id="findOrdersAndOrderDetailResultMap" resultMap="ordersAndOrderDetailResultMap"> SELECT t1.*, t2.username, t2.sex, t2.address, t3.id orderdetail_id, t3.items_id, t3.items_num, t3.orders_id FROM orders t1, t_user t2, orderdetail t3 WHERE t1.user_id = t2.id AND t3.orders_id=t1.id </select> <!-- 多对多查询，查询用户以及用户购买的商品信息，一个用户对应多个订单，一个订单对应多个订单明细，一个订单明细对应一个商品 --> <select id="findUserAndItemsResultMap" resultMap="userAndItemsResultMap"> SELECT t1.*, t2.username, t2.sex, t2.address, t3.id orderdetail_id, t3.items_id, t3.items_num, t3.orders_id, t4.itemsname items_name, t4.detail items_detail, t4.price items_price FROM orders t1, t_user t2, orderdetail t3, items t4 WHERE t1.user_id = t2.id AND t3.orders_id=t1.id AND t3.items_id = t4.id </select> <resultMap type="com.User" id="userAndItemsResultMap"> <!-- 用户信息 --> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> <!-- 一个用户对应多个订单 --> <collection property="ordersList" ofType="com.Orders"> <id column="id" property="id"/> <result column="user_id" property="userid"/> <result column="number" property="number"/> <result column="createtime" property="createTime"/> <result column="note" property="note"/> <!-- 一个订单对应多个订单明细 --> <collection property="orderdetails" ofType="com.OrderDetail"> <id column="orderdetail_id" property="id"/> <result column="items_id" property="itemsId"/> <result column="items_num" property="itemsNum"/> <result column="orders_id" property="ordersId"/> <!-- 一个订单明细对应一个商品 --> <association property="items" javaType="com.Items"> <id column="items_id" property="id"/> <result column="items_name" property="itemsName"/> <result column="items_detail" property="detail"/> <result column="items_price" property="price"/> </association> </collection> </collection> </resultMap> <resultMap type="com.Orders" id="ordersAndOrderDetailResultMap"> <id column="id" property="id"/> <result column="user_id" property="userid"/> <result column="number" property="number"/> <result column="createtime" property="createTime"/> <result column="note" property="note"/> <!-- 订单关联用户 --> <association property="user" javaType="com.User"> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> <!-- 关联订单详细 --> <collection property="orderdetails" ofType="com.OrderDetail"> <id column="orderdetail_id" property="id"/> <result column="items_id" property="itemsId"/> <result column="items_num" property="itemsNum"/> <result column="orders_id" property="ordersId"/> </collection> </resultMap> <resultMap type="com.Orders" id="OrdersUserResultMap"> <id column="id" property="id"/> <result column="user_id" property="userid"/> <result column="number" property="number"/> <result column="createtime" property="createTime"/> <result column="note" property="note"/> <!-- 订单关联用户 --> <association property="user" javaType="com.User"> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap> </mapper>