Mybatis一对多嵌套查询和分页

需求:根据分类ID查询分类下所属的商品集合,每个商品又有一个图片集合。

类似的需求有很多,比如经典的一个用户有N个角色,一个角色有N个权限,那么通过用户的id来查询角色和权限数据等等。

至于分页插件,无论是Mybatis-PageHelper还是Mybatis-Plus都可以辅助,这里主要记录不同查询方式对分页的影响。

先展示结果:

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
{
"code": 0,
"msg": "success",
"data": {
"total": 9,
"size": 2,
"pages": 5,
"current": 1,
"records": [
{
"id": 1,
"code": "1410854032",
"name": "Esmeralda Kilback",
"categoryId": "1",
"originPrice": 359,
"price": 103,
"sales": 299,
"commentCount": 0,
"freight": 1,
"detail": "这里是商品详情",
"createdAt": "2018-04-09 18:52:05",
"updatedAt": "2018-04-24 23:41:49",
"images": [
{
"id": 40,
"productId": "1",
"link": "uploads/product/201804/18/78a6e4e4d73bfc64b7aef88a90e7f192.png",
"createdAt": "2018-04-09 18:52:05",
"updatedAt": "2018-04-18 16:37:09"
},
{
"id": 41,
"productId": "1",
"link": "uploads/product/201804/18/fffdccaa36a8475ed3d2c71c2f43cb86.png",
"createdAt": "2018-04-09 18:52:05",
"updatedAt": "2018-04-18 16:37:09"
},
{
"id": 301,
"productId": "1",
"link": "uploads/product/201804/18/68b18cbcb090a94123abd9d729528370.png",
"createdAt": "2018-04-18 16:35:56",
"updatedAt": "2018-04-18 16:35:56"
}
]
},
{
"id": 8,
"code": "1925117917",
"name": "Edgardo Osinski",
"categoryId": "1",
"originPrice": 389,
"price": 154,
"sales": 199,
"commentCount": 0,
"freight": 14,
"detail": "这里是商品详情...5052 Kyler Walk Suite 921",
"createdAt": "2018-04-09 18:52:05",
"updatedAt": "2018-04-09 18:52:05",
"images": [
{
"id": 58,
"productId": "8",
"link": "uploads/default.png",
"createdAt": "2018-04-09 18:52:05",
"updatedAt": "2018-04-09 18:52:05"
},
{
"id": 59,
"productId": "8",
"link": "uploads/default2.png",
"createdAt": "2018-04-09 18:52:05",
"updatedAt": "2018-04-09 18:52:05"
},
{
"id": 60,
"productId": "8",
"link": "uploads/default3.png",
"createdAt": "2018-04-09 18:52:05",
"updatedAt": "2018-04-09 18:52:05"
}
]
}
]
}
}

定义模型

Product用于数据库映射,为了保持其简洁,其他的二次封装不在Product里进行,而用继承的方式。

定义模型ProductVo

1
2
3
4
5
@Data
public class ProductVo extends Product {

private List<ProductImage> images;
}

方式1:结果查询

  1. 在ProductsMapper.xml中定义select语句,一次性将关联数据全部查询出来,然后进行结果映射
1
2
3
4
5
6
7
8
9
10
11
12
13
<select id="selectProductsBycategoryId"  resultMap="productsListMap">
select
p.id,
p.name,
p.code,
...
i.id images_id,
i.product_id images_product_id,
...
from products p
inner join product_images i on p.id = i.product_id
where p.category_id = #{id}
</select>
  1. 定义productsListMap结果映射
1
2
3
<resultMap id="productsListMap" type="com.longke.mallb2c.entity.vo.ProductVo" extends="BaseResultMap">-->
<collection property="images" columnPrefix="images_" resultMap="com.longke.mallb2c.mapper.ProductImagesMapper.BaseResultMap"/>
</resultMap>

注意:

  • property就是在ProductVo中定义的商品图片集合images字段
  • 用到了columnPrefix列前缀,只是别名前缀,跟数据库内的字段无关,这个images_别名前缀跟select中定义别名时要保持一致。
  • 用到了extends继承已有的BaseResultMap,不用在这里再重新写Product表的每个字段的映射了。mapper.xml自动生成工具都会帮我们生成这个BaseResultMap,直接继承即可。
  • collection用于一对多查询,查询的结果映射直接复用ProductImagesMapper中定义的BaseResultMap

总结

优点

  • 一次性查询,集中映射,简单,效率

缺点

  • 会将collection中查询到的条数作为分页的约束条件,导致分页数据不准确。

比如想查page=1,limit=10的数据,本来期望的是查询出10个商品,然后这10个商品分别再嵌套查询出自己的商品图片集合。但是会发现,可能商品只有两三个,每个下面都带了自己的商品图片集合。

原因:

先通过表连接把表记录关联进来了,如果有3个商品,关联图片表之后每个商品有4条图片记录,那么其实这时候虽然只有三个商品,但是这个内存中的临时表已经有12条记录了,在语句的最后加上 limit 0,10,其实分页的时候分的是这12条记录。最终就会导致最终的映射结果只出现了3个商品,而非我们期望的10个商品。

方式2:嵌套查询

  1. 在ProductsMapper.xml中定义select语句
1
2
3
4
5
<select id="selectProductsBycategoryId"  resultMap="productsListMap">
select <include refid="Base_Column_List"/>
from products
where category_id = #{id}
</select>
  1. 定义productsListMap结果映射
1
2
3
4
5
<resultMap id="productsListMap" type="com.longke.mallb2c.entity.vo.ProductVo" extends="BaseResultMap">
<collection property="images" ofType="com.longke.mallb2c.entity.ProductImage"
column="{productId=id}" select="com.longke.mallb2c.mapper.ProductImagesMapper.selectByProductId">
</collection>
</resultMap>

注意:

  • column是参数传递,即将Product的哪个属性传递给嵌套的查询语句,{productId=id}代表将Product的id属性传递给参数productId
  • select直接使用ProductImagesMapper中定义的select语句
  1. ProductImagesMapper定义selectByProductId查询语句
1
2
3
4
5
<select id="selectByProductId" resultMap="BaseResultMap">
SELECT <include refid="Base_Column_List"/>
from product_images
where product_id = #{productId}
</select>

总结

优点

  • 准确分页

缺点

  • 没有解决N+1的问题,多条SQL查询语句,效率太差

todo

使用方式1结果查询,并完成准确分页。

感谢:

https://blog.csdn.net/isea533/article/details/28921533

https://blog.csdn.net/baidu_38116275/article/details/78622669

记录工作,记录成长,您的支持将鼓励我继续创作!