Mybatis分页中遇到的坑2

站在巨人的肩膀上

 

 http://crocutax.com/blog/mybatis-one-to-many-nestes-query-and-page-query

Mybatis一对多嵌套查询和分页

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

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

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

先展示结果：

Copy{
    "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，

Copy@Data
public class ProductVo extends Product { private List<ProductImage> images; } 

方式1：结果查询

1. 在ProductsMapper.xml中定义select语句，一次性将关联数据全部查询出来，然后进行结果映射

Copy    <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>

2. 定义productsListMap结果映射

Copy    <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语句

Copy   <select id="selectProductsBycategoryId"  resultMap="productsListMap"> select <include refid="Base_Column_List"/> from products where category_id = #{id} </select>

2. 定义productsListMap结果映射

Copy    <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语句

3. 在ProductImagesMapper定义selectByProductId查询语句

Copy    <select id="selectByProductId" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List"/> from product_images where product_id = #{productId} </select>

总结

优点

• 准确分页

缺点

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

转载于:https://www.cnblogs.com/longxok/p/10911172.html