myBatis高级查询(多表联查)
本节主要是对mybatis高级查询,对订单商品信息模型分析
高级查询:一对一,一对多,多对多查询
延迟加载
高级映射:
1.1数据模型分析思路:
1.每张表要存放的数据内容
2.表与表直接的对应关系
1.2表直接的关系
![这里给出几张表的关系图(https://img-blog.csdnimg.cn/20200515155155939.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDczNTQwOQ==,size_16,color_FFFFFF,t_70)
用户表user:
记录了购买商品的用户信息
订单表:orders
记录了用户所创建的订单(购买商品的订单)
订单明细表:orderdetail:
记录了订单的详细信息即购买商品的信息
商品表:items
记录了商品信息
orders和user:
orders—>user:一个订单只由一个用户创建,一对一
user—->orders:一个用户可以创建多个订单,一对多
orders和orderdetail:
orders—>orderdetail:一个订单可以包括 多个订单明细,因为一个订单可以购买多个商品,每个商品的购买信息在orderdetail记录,一对多关系
orderdetail–> orders:一个订单明细只能包括在一个订单中,一对一
orderdetail和items:
orderdetail—>itesms:一个订单明细只对应一个商品信息,一对一
items–> orderdetail:一个商品可以包括在多个订单明细 ,一对多
再分析数据库级别没有关系的表之间是否有业务关系:
orders和items:
orders和items之间可以通过orderdetail表建立 关系。
1.3建表语句
用户表:
CREATE TABLE user_order (
id int NOT NULL AUTO_INCREMENT,
user_name varchar(50) NOT NULL ,
sex varchar(50) NOT NULL,
createtime datetime,
address varchar(100),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into user_order(user_name,sex,address) values(‘张三’,‘男’,‘佛山伯顿城’);
insert into user_order(user_name,sex,address) values(‘李四’,‘男’,‘佛山伯顿城’);
insert into user_order(user_name,sex,address) values(‘王二’,‘男’,‘佛山伯顿城’);
insert into user_order(user_name,sex,address) values(‘李逵’,‘男’,‘佛山伯顿城’);
insert into user_order(user_name,sex,address) values(‘王雪’,‘女’,‘佛山伯顿城’);
订单表:
CREATE TABLE orders (
id int NOT NULL AUTO_INCREMENT,
user_id int NOT NULL ,
number varchar(50) NOT NULL,
createtime varchar(50),
note varchar(1000),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into orders(user_id,number,note) values(1,‘2’,‘经济’);
insert into orders(user_id,number,note) values(1,‘2’,‘公干’);
insert into orders(user_id,number,note) values(2,‘2’,‘看见黄瓜’);
insert into orders(user_id,number,note) values(3,‘2’,‘士大夫’);
insert into orders(user_id,number,note) values(4,‘5’,‘广东省’);
订单明细表:
CREATE TABLE orderdetail (
id int NOT NULL AUTO_INCREMENT,
orders_id int NOT NULL,
items_id int NOT NULL,
items_num varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into orderdetail(orders_id,items_id,items_num) values(1,1,‘2’);
insert into orderdetail(orders_id,items_id,items_num) values(1,1,‘3’);
insert into orderdetail(orders_id,items_id,items_num) values(2,3,‘1’);
商品信息表:
CREATE TABLE items (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL ,
detail varchar(50) NOT NULL,
price varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into items(name,detail,price) values(‘化妆品’,‘祛痘’,‘100’);
insert into items(name,detail,price) values(‘鞋子’,‘李宁’,‘120’);
insert into items(name,detail,price) values(‘T恤’,‘男’,‘120’);
2.一对一查询
2.1查询订单信息,关联查询用户信息
/*
订单和用户是一对一关系,订单类继承用户类,使用用户类 的信息
代码中使用了lombock插件,如果没安装该插件,则使用gettter和setter方法
*/
@Data
public class Orders extends User{
private int id;
private int userId;
private String number;
private String createtime;
private String note;
private User user;
//private List<OrderDetail> orderDetailList;
}
@Data
public class User{
private int id;
private String userName;
private String sex;
private String createTime;
private String address;
private List ordersList;
}
<!--association:用于关联查询单个对象的信息-->
<association property="user" javaType="com.example.springbootdemo.dto.User">
<id column="id" property="id"/>
<result column="userName" property="user_name"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="selectOderByUserId" resultMap="oderUserInfo">
SELECT
orders.*,
user_order.*
FROM
orders,
user_order
WHERE orders.user_id = user_order.id and orders.id=#{oderId}
2.2一对多 需求:查询订单orders关联的订单明细信息orderdetails @Data public class Orders extends User{
private int id;
private int userId;
private String number;
private String createtime;
private String note;
private User user;
private List<OrderDetail> orderDetailList;
}
@Data
public class OrderDetail {
private int id;
private int ordersId;
private int itemsId;
private String itemsNum;
//private Items items;
}
<select id="selectOrderDetail" resultMap="orderDetailMap">
SELECT
orders.*,
user_order.*,
orderdetail.id,
orderdetail.orders_id,
orderdetail.items_id,
orderdetail.items_num
FROM
orders,
user_order,
orderdetail
WHERE orders.user_id=user_order.id AND orderdetail.orders_id=orders.id and orders.id=#{oderId}
</select>
2.3多对多
需求:查询用户及用户购买商品信息。
(其他pto类如上)
@Data
public class OrderDetail {
private int id;
private int ordersId;
private int itemsId;
private String itemsNum;
private Items items;
}
<!--订单信息-->
<collection property="ordersList" ofType="com.example.springbootdemo.dto.Orders">
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
<!--订单明细-->
<collection property="orderDetailList" ofType="com.example.springbootdemo.dto.OrderDetail">
<id column="id" property="id"/>
<result column="orders_id" property="ordersId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum" jdbcType="VARCHAR"/>
<!--商品信息-->
<association property="items" javaType="com.example.springbootdemo.dto.Items">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="detail" property="detail"/>
<result column="price" property="price"/>
</association>
</collection>
</collection>
</resultMap>
<select id="selectUserAndItems" resultMap="userAndItemsMapper">
SELECT
orders.*,
user_order.user_name,
user_order.sex,
user_order.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.orders_id,
items.name items_name,
items.detail items_detail,
items.price items_price
FROM
orders,
user_order,
orderdetail,
items
WHERE
orders.user_id = user_order.id AND orderdetail.orders_id=orders.id
AND orderdetail.items_id=items.id
</select>