包头有没有专业做淘宝网站的,做平面设计必看的网站,杨浦苏州网站建设,工程科技 网站设计在 MyBatis 中#xff0c;关联查询#xff08;也称为复杂映射#xff09;是指将多个表的数据通过 SQL 查询和结果映射的方式#xff0c;组合成一个或多个 Java 对象。这种查询方式用于处理实体之间的关系#xff0c;如一对一、一对多和多对多关系。通过关联查询#xff0…在 MyBatis 中关联查询也称为复杂映射是指将多个表的数据通过 SQL 查询和结果映射的方式组合成一个或多个 Java 对象。这种查询方式用于处理实体之间的关系如一对一、一对多和多对多关系。通过关联查询你可以更好地表示现实世界中的业务逻辑并简化数据访问层的代码。
一、数据准备
1、用户表
CREATE TABLE users (
id int(11) PRIMARY KEY AUTO_INCREMENT,
username varchar(20),
password varchar(50),
realname varchar(20)
);
INSERT INTO users VALUES (1, admin, 123456, 管理员);
INSERT INTO users VALUES (2, tom, 123, 汤姆);
INSERT INTO users VALUES (3, jerry, 456, 杰瑞);
INSERT INTO users VALUES (4, zhangsan, 111, 张三);
INSERT INTO users VALUES (5, lisi, 222, 李四);
2、订单表
CREATE TABLE orders (
id int(11) PRIMARY KEY AUTO_INCREMENT,
order_number varchar(30),
total_price double,
status varchar(5),
user_id int(11)
);
INSERT INTO orders VALUES (1, 202112290838001, 2535, 已评价, 2);
INSERT INTO orders VALUES (2, 202112290838002, 4704.6, 已签收, 2);
INSERT INTO orders VALUES (3, 202112290838003, 3620, 已支付, 2);
INSERT INTO orders VALUES (4, 202112290840001, 600, 已发货, 3);
INSERT INTO orders VALUES (5, 202112290840002, 280, 未支付, 3);
3、订单详情表
CREATE TABLE orders_detail (
id int(11) PRIMARY KEY AUTO_INCREMENT,
amount int(11),
goods_id int(11),
orders_id int(11)
);
INSERT INTO orders_detail VALUES (1, 1, 1, 1);
INSERT INTO orders_detail VALUES (2, 3, 8, 1);
INSERT INTO orders_detail VALUES (3, 1, 2, 2);
INSERT INTO orders_detail VALUES (4, 2, 7, 2);
INSERT INTO orders_detail VALUES (5, 1, 3, 3);
INSERT INTO orders_detail VALUES (6, 6, 6, 3);
INSERT INTO orders_detail VALUES (7, 2, 4, 4);
INSERT INTO orders_detail VALUES (8, 1, 5, 5);
4、商品表
CREATE TABLE goods (
id int(11) PRIMARY KEY AUTO_INCREMENT,
goods_name varchar(50),
description varchar(500),
price double
);
INSERT INTO goods VALUES (1, 手机, 手机, 2499);
INSERT INTO goods VALUES (2, 笔记本电脑, 笔记本电脑, 4699);
INSERT INTO goods VALUES (3, IPAD, IPAD, 3599);
INSERT INTO goods VALUES (4, 运动鞋, 运动鞋, 300);
INSERT INTO goods VALUES (5, 外套, 外套, 280);
INSERT INTO goods VALUES (6, 可乐, 可乐, 3.5);
INSERT INTO goods VALUES (7, 辣条, 辣条, 2.8);
INSERT INTO goods VALUES (8, 水杯, 水杯, 12);
5、数据模型分析 二、一对一查询——订单查询关联用户信息查询
一、通过resultType方式实现
1、实体类
实体类Orders类不能映射全部字段需要新创建的实体类创建一个包括查询字段较多的实体类。 OrdersQuery中包含了Orders以及Users需要查询的属性。
package org.example.vo;public class OrdersQuery {//订单属性private Integer id;private String orderNumber;private Double totalPrice;private String status;private Integer userId;//用户属性private String username;private String password;private String realname;public OrdersQuery() {}public OrdersQuery(Integer id, String orderNumber, Double totalPrice, String status, Integer userId, String username, String password, String realname) {this.id id;this.orderNumber orderNumber;this.totalPrice totalPrice;this.status status;this.userId userId;this.username username;this.password password;this.realname realname;}public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public String getOrderNumber() {return orderNumber;}public void setOrderNumber(String orderNumber) {this.orderNumber orderNumber;}public Double getTotalPrice() {return totalPrice;}public void setTotalPrice(Double totalPrice) {this.totalPrice totalPrice;}public String getStatus() {return status;}public void setStatus(String status) {this.status status;}public Integer getUserId() {return userId;}public void setUserId(Integer userId) {this.userId userId;}public String getUsername() {return username;}public void setUsername(String username) {this.username username;}public String getPassword() {return password;}public void setPassword(String password) {this.password password;}public String getRealname() {return realname;}public void setRealname(String realname) {this.realname realname;}Overridepublic String toString() {return OrdersQuery{ id id , orderNumber orderNumber \ , totalPrice totalPrice , status status \ , userId userId , username username \ , password password \ , realname realname \ };}
}2、mapper接口 import org.example.vo.OrdersQuery;
import java.util.List;public interface OrdersMapper {//通过resultType实现查询订单信息关联查询相关的用户信息ListOrdersQuery selectUseResultType();
}3、mapper文件
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceorg.example.mapper.OrdersMapper!--1、ResultType类型实现一对一查询--select idselectUseResultType resultTypeorg.example.vo.OrdersQuerySELECT o.*,u.username,u.password,u.realname FROM orders o,users u WHERE o.user_idu.id/select
/mapper
4、测试代码 //ResultType类型实现一对一查询Testpublic void testOneToOneResultType() {SqlSession sqlSession MybatisUtil.getSession();OrdersMapper ordersMapper sqlSession.getMapper(OrdersMapper.class);ListOrdersQuery list ordersMapper.selectUseResultType();for (OrdersQuery ordersQuery : list) {System.out.println(ordersQuery);}sqlSession.close();}
二、通过resultMap方式实现
1、用户类
package org.example.entity;public class Users {private Integer id;private String username;private String password;private String realname;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 getPassword() {return password;}public void setPassword(String password) {this.password password;}public String getRealname() {return realname;}public void setRealname(String realname) {this.realname realname;}Overridepublic String toString() {return Users{ id id , username username \ , password password \ , realname realname \ };}
}2、订单类
在Orders类中加入Users属性Users属性用于存储关联查询的用户信息。 因为订单关联查询用户是一对一关系所以这里使用单个Users对象存储关联查询的用户信息。
public class Orders {
private Integer id;
private String orderNumber;
private Double totalPrice;
private String status;
private Integer userId;
private Users users;//一对一映射属性一个订单属于一个用户
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber orderNumber;
}
public Double getTotalPrice() {
return totalPrice;
}
public void setTotalPrice(Double totalPrice) {
this.totalPrice totalPrice;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status status;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId userId;
}
public Users getUsers() {
return users;
}
public void setUsers(Users users) {
this.users users;
}
Override
public String toString() {
return Orders{
id id
, orderNumber orderNumber \
, totalPrice totalPrice
, status status \
, userId userId
, users users
};
}
}
3、mapper接口
//通过resultMap实现查询订单信息关联查询相关的用户信息ListOrders selectUseResultMap();
4、mapper文件
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceorg.example.mapper.OrdersMapper
!--2、resultMap实现一对一查询--resultMap idselectResultMap typeorg.example.entity.Ordersid columnid propertyid/result columnorder_number propertyorderNumber/result columntotal_price propertytotalPrice/result columnstatus propertystatus/result columnuser_id propertyuserId/!--一对一映射property:要映射的实体类的属性名javaType指定映射属性的类型--association propertyusers javaTypeorg.example.entity.Usersid columnuser_id propertyid/result columnusername propertyusername/result columnpassword propertypassword/result columnrealname propertyrealname//association/resultMapselect idselectUseResultMap resultMapselectResultMapselect o.id, o.order_number, o.total_price, o.status, o.user_id, u.username,u.password, u.realname from orders o, users u where o.user_idu.id/select5、测试代码 //resultMap实现一对一查询Testpublic void testOneToOneResultMap() {SqlSession sqlSession MybatisUtil.getSession();OrdersMapper ordersMapper sqlSession.getMapper(OrdersMapper.class);ListOrders list ordersMapper.selectUseResultMap();for (Orders orders : list) {System.out.println(orders);}sqlSession.close();}
三、一对多查询——查询订单信息关联订单详情信息
1、订单详情类
package org.example.entity;public class OrderDetail {private Integer id;private Integer amount;private Integer ordersId;private Integer goodsId;public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public Integer getAmount() {return amount;}public void setAmount(Integer amount) {this.amount amount;}public Integer getOrdersId() {return ordersId;}public void setOrdersId(Integer ordersId) {this.ordersId ordersId;}public Integer getGoodsId() {return goodsId;}public void setGoodsId(Integer goodsId) {this.goodsId goodsId;}Overridepublic String toString() {return OrderDetail{ id id , amount amount , ordersId ordersId , goodsId goodsId };}
}2、订单类
在Order类中加入ListOrdersDetail detailList 属性details属性用于存储关联查询的订单详 情。 因为订单关联查询订单详情是一对多关系所以这里使用集合对象存储关联查询的订单详情信息。
package org.example.entity;import java.util.List;public class Orders {private Integer id;private String orderNumber;private Double totalPrice;private String status;private Integer userId;private Users users;//一对一映射属性一个订单属于一个用户ListOrderDetail orderDetailList;//一对多关系属性一个订单包含多个订单详情public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public String getOrderNumber() {return orderNumber;}public void setOrderNumber(String orderNumber) {this.orderNumber orderNumber;}public Double getTotalPrice() {return totalPrice;}public void setTotalPrice(Double totalPrice) {this.totalPrice totalPrice;}public String getStatus() {return status;}public void setStatus(String status) {this.status status;}public Integer getUserId() {return userId;}public void setUserId(Integer userId) {this.userId userId;}public Users getUsers() {return users;}public void setUsers(Users users) {this.users users;}public ListOrderDetail getOrderDetailList() {return orderDetailList;}public void setOrderDetailList(ListOrderDetail orderDetailList) {this.orderDetailList orderDetailList;}Overridepublic String toString() {return Orders{ id id , orderNumber orderNumber \ , totalPrice totalPrice , status status \ , userId userId , users users , orderDetailList orderDetailList };}
}3、mapper接口 //查询订单信息关联订单详情信息ListOrders selectOrdersAndDetail();
4、mapper文件
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceorg.example.mapper.OrdersMapper!--3、一对多关系属性--resultMap iddetailResultMap typeorg.example.entity.Orders id columnid propertyid/result columnorder_number propertyorderNumber/result columntotal_price propertytotalPrice/result columnstatus propertystatus/result columnuser_id propertyuserId/association propertyusers javaTypeorg.example.entity.Usersid columnuser_id propertyid/result columnusername propertyusername/result columnpassword propertypassword/result columnrealname propertyrealname//association!--一对多映射property:订单实体的属性ofType集合里面存储的类型--collection propertyorderDetailList ofTypeorg.example.entity.OrderDetailid columndetail_id propertyid/result columnamount propertyamount/result columnorders_id propertyordersId/result columngoods_id propertygoodsId//collection/resultMapselect idselectOrdersAndDetail resultMapdetailResultMapSELECT o.*,u.username,u.password,u.realname ,d.id detail_id,d.amount,d.orders_id,d.goods_idFROM orders o,users u,orders_detail dWHERE o.user_idu.id and o.idd.orders_id/select
/mapper
5、测试代码
//一对多查询Testpublic void testOneToMany() {SqlSession sqlSession MybatisUtil.getSession();OrdersMapper ordersMapper sqlSession.getMapper(OrdersMapper.class);ListOrders list ordersMapper.selectOrdersAndDetail();for (Orders orders : list) {System.out.println(orders);}sqlSession.close();}
四、多对多查询——查询订单信息关联查询商品信息
1、商品类
将OrderDetail类中Integer类型的goods_id属性修改为Goods类型属性goods属性用于存储关联查询的商品信息。 订单与订单详情是一对多关系订单详情与商品是一对一关系反之商品与订单详情是一对多关系订单详情与订单是一对一关系所以订单与商品为多对多关系。
package org.example.entity;public class Goods {private Integer id;private String goodsName;private String description;private Double price;public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public String getGoodsName() {return goodsName;}public void setGoodsName(String goodsName) {this.goodsName goodsName;}public String getDescription() {return description;}public void setDescription(String description) {this.description description;}public Double getPrice() {return price;}public void setPrice(Double price) {this.price price;}Overridepublic String toString() {return Goods{ id id , goodsName goodsName \ , description description \ , price price };}
}2、订单详情类
package org.example.entity;public class OrderDetail {private Integer id;private Integer amount;private Integer ordersId;private Integer goodsId;private Goods goods;//在订单详情里面定义与商品表的关系public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public Integer getAmount() {return amount;}public void setAmount(Integer amount) {this.amount amount;}public Integer getOrdersId() {return ordersId;}public void setOrdersId(Integer ordersId) {this.ordersId ordersId;}public Integer getGoodsId() {return goodsId;}public void setGoodsId(Integer goodsId) {this.goodsId goodsId;}public Goods getGoods() {return goods;}public void setGoods(Goods goods) {this.goods goods;}Overridepublic String toString() {return OrderDetail{ id id , amount amount , ordersId ordersId , goodsId goodsId , goods goods };}
}3、mapper接口
//查询订单信息关联查询商品信息//订单信息和商品表没有直接关系在订单详情中定义与商品的关系ListOrders selectOrdersAndGoods();
4、mapper文件
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceorg.example.mapper.OrdersMapper!--4、多对多--resultMap idgoodsResultMap typeorg.example.entity.Orders id columnid propertyid/result columnorder_number propertyorderNumber/result columntotal_price propertytotalPrice/result columnstatus propertystatus/result columnuser_id propertyuserId/!--一对一--association propertyusers javaTypeorg.example.entity.Usersid columnuser_id propertyid/result columnusername propertyusername/result columnpassword propertypassword/result columnrealname propertyrealname//association!--一对多映射--collection propertyorderDetailList ofTypeorg.example.entity.OrderDetailid columndetail_id propertyid/result columnamount propertyamount/result columnorders_id propertyordersId/result columngoods_id propertygoodsId/!--多对多--association propertygoods javaTypeorg.example.entity.Goodsid columngoods_id propertyid/result columngoods_name propertygoodsName/result columndescription propertydescription/result columnprice propertyprice//association/collection/resultMapselect idselectOrdersAndGoods resultMapgoodsResultMapSELECT o.*,u.username,u.password,u.realname ,d.id detail_id,d.amount,d.orders_id,d.goods_id,g.goods_name,g.description,g.priceFROM orders o,users u,orders_detail d,goods gWHERE o.user_idu.id and o.idd.orders_id and d.goods_idg.id/select/mapper
5、测试代码 //多对多查询Testpublic void testManyToMany1() {SqlSession sqlSession MybatisUtil.getSession();OrdersMapper ordersMapper sqlSession.getMapper(OrdersMapper.class);ListOrders list ordersMapper.selectOrdersAndGoods();for (Orders orders : list) {System.out.println(orders);}sqlSession.close();}
