一对多和多对多的关系
我是以你平时买东西为例子的,一个用户对应多个订单,一个订单对应多个订单明细,一个订单明细对应一个商品,根据这些关系来进行实例演示。
实例演示
- 一对多(一个订单对应多个订单明细)
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>