mybatis的一对多和多对多查询
来源:互联网 发布:网络博客犯法吗 编辑:程序博客网 时间:2024/06/06 02:43
一对多和多对多的关系
我是以你平时买东西为例子的,一个用户对应多个订单,一个订单对应多个订单明细,一个订单明细对应一个商品,根据这些关系来进行实例演示。
实例演示
- 一对多(一个订单对应多个订单明细)
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 + "]"; }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
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 + "]"; }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
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 + "]"; }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
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 + "]"; }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
6)OrdersCustomMapper.xml文件完成之后编写OrdersCustomMapper.java文件(面向接口编程的,这个类的路径就是OrdersCustomMapper.xml中命名空间的名字,其中的方法就是OrdersCustomMapper.xml中的select的id对应的方法)
package com;import java.util.List;public interface OrdersCustomMapper { /**查询订单以及订单明细**/ public List<OrdersCustom> findOrdersAndOrderDetailResultMap();}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
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); }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 多对多其实跟一对多差不多,只不过修改一下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>
阅读全文
0 0
- mybatis的一对多和多对多查询
- mybatis的一对多和多对多查询
- mybatis的一对多和多对多查询
- mybatis 一对多和多对一关联查询
- mybatis 一对多和多对一关联查询
- mybatis 实现关联查询的一对多和多对多查询
- mybatis一对一,一对多,多对多的关联查询
- mybatis 关联数据的查询 多对一 一对多
- MyBatis实现一对多和多对一的关联关系的查询
- mybatis一对多查询
- mybatis一对多查询
- Mybatis一对多查询
- Mybatis 一对多 查询
- mybatis 一对多查询
- Mybatis 一对多 查询
- Mybatis一对多查询
- Mybatis一对多查询
- Mybatis(一对多查询)
- python 中的队列
- #NOIP模拟赛bzoj3449#大佬(期望好题)
- noip模拟10.24
- 【1013】数字河
- Leetcode 1. Two Sum
- mybatis的一对多和多对多查询
- openstack RPC通信
- 新人
- 1701 H2王建瑜 连续第一天
- React 非DOM属性
- BAT脚本编写教程
- C#调用科大讯飞离线语音合成TTS
- 景驰无人驾驶 1024 编程邀请赛
- 网站无法访问的一些问题与解决