Mybatis one-to-many nested queries and paging

Articles Catalogue


Requirement: According to the classification ID, we query the commodity set under the classification, and each commodity has a picture set.

There are many similar requirements, such as a classic user with N roles, a role with N permissions, then use the user's id to query the role and permission data, and so on.

As for paging plug-ins, either Mybatis-Page Helper or Mybatis-Plus can be assisted. Here we mainly record the impact of different query modes on paging.

Show the results first:

{
    "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": "Here are the details of the goods.",
                "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": "Here are the details of the goods....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"
                    }
                ]
            }
        ]
    }
}

Define model

Product is used for database mapping. In order to keep it concise, other secondary encapsulation is not carried out in Product, but in the way of inheritance.

Define the model ProductVo,

@Data
public class ProductVo extends Product {

    private List<ProductImage> images;
}

Mode 1: Result query

  1. Define the select statement in ProductsMapper.xml, query all the related data at one time, and then map the results.
    <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. Define product ListMap result mapping
    <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>

Be careful:

  • property is the image field of the collection of merchandise pictures defined in Product Vo
  • Column Prefix column prefix is used, but the alias prefix is not related to the field in the database. The image_alias prefix should be consistent with the alias defined in select.
  • Using extends to inherit the existing BaseResultMap, you don't need to rewrite the mapping of each field of the Product table here. mapper.xml automatic generation tools will help us generate the BaseResultMap, which can be inherited directly.
  • collection is used for one-to-many queries, and the result mapping of the query directly reuses the BaseResultMap defined in the Product Images Mapper

summary

Advantage

  • One-time query, centralized mapping, simplicity and efficiency

shortcoming

  • Paging data is inaccurate because the number of items queried in collection is used as a paging constraint.

For example, if you want to look up the data of page=1,limit=10, the original expectation is to query out 10 commodities, and then these 10 commodities are nested to query out their own collection of commodity pictures. But you may find that there are only two or three items, each with its own collection of pictures.

Reason:

Firstly, table records are linked by table connection. If there are three items and four picture records for each item after the related picture table, then there are actually only three items at this time, but there are 12 records in the temporary table in memory. Add limit 0,10 at the end of the statement, in fact, when paging. These are 12 records. Ultimately, the result of the mapping will be only three products, not the 10 products we expect.

Mode 2: Nested queries

  1. Define select statement in ProductsMapper.xml
   <select id="selectProductsBycategoryId"  resultMap="productsListMap">
        select <include refid="Base_Column_List"/>
        from products
        where category_id = #{id}
    </select>
  1. Define product ListMap result mapping
    <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>

Be careful:

  • column is parameter passing, which property of Product is passed to nested query statement, and {productId=id} represents the ID property of Product passed to parameter productId.
  • Select directly uses the select statement defined in the Product Images Mapper
  1. Define the selectByProductId query statement in Product ImagesMapper
    <select id="selectByProductId" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        from product_images
        where product_id = #{productId}
    </select>

summary

Advantage

  • Accurate Paging

shortcoming

  • There is no solution to the problem of N+1. Many SQL query statements are inefficient.

todo

Use Mode 1 to query the results and complete accurate paging.

Thank:

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

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

Tags: xml Mybatis Database SQL

Posted on Fri, 06 Sep 2019 06:22:23 -0700 by dink87522