Various uses of the <collection> tag in MyBatis

In MyBatis,<collection>Tags are a key tool for dealing with One-to-Many relationships, which can cleverly map query results to collection properties of Java objects. The following is<collection>The different usages and examples of   are sorted out as follows in combination with the information in the knowledge base:

1. Nested Select

passselectAttribute refers to another SQL query based on a column in the main table (such asid) As a parameter, query the set data of the subtable.

Features

• Each master record triggers a subquery once (may cause N+1 issues).

• Suitable for scenarios where data volume is small or recursive query is required.

Sample code

<!-- The main tableresultMap -->
<resultMap  type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <!-- Collection properties：permissions -->
    <collection 
        property="permissions" 
        ofType="Permission" 
        select="" 
        column="id"
    />
</resultMap>

<!-- The main table查询SQL -->
<select  resultMap="UserMap">
    SELECT id, username FROM users WHERE id = #{id}
</select>

<!-- Subquery（） -->
<select  resultType="Permission">
    SELECT * FROM permissions WHERE user_id = #{id}
</select>

explain

• property="permissions": Map toUserClassicpermissionsCollection attributes.

• ofType="Permission": Specify the type of the collection element.

• select="...": Mapper method that references subquery.

• column="id": Put the main tableidPassed as a parameter to the subquery.

2. Nested Results

passcolumnandofTypeDirectly map the results of JOIN queries to avoid querying the database multiple times.

Features

• Get all data at once through JOIN to reduce the number of queries.

• Repeated data needs to be processed (the main table field will be repeated, and it needs to be passedcolumnPrefixDistinguish).

Sample code

<!-- The main tableresultMap -->
<resultMap  type="User">
    <id property="id" column="user_id"/>
    <result property="username" column="username"/>
    <!-- Collection properties：orders -->
    <collection 
        property="orders" 
        ofType="Order" 
        columnPrefix="order_"
    >
        <id property="orderId" column="order_id"/>
        <result property="amount" column="amount"/>
    </collection>
</resultMap>

<!-- The main table查询SQL（useJOIN） -->
<select  resultMap="UserMap">
    SELECT 
         AS user_id, 
        , 
         AS order_id, 
         
    FROM users u 
    LEFT JOIN orders o ON  = o.user_id 
    WHERE  = #{id}
</select>

explain

• columnPrefix="order_": Prefix the subtable field name (such asorder_id) is distinguished from the main table field.

• <collection>Internal definition of mapping rules for subtable fields.

• Main table fields (such asuser_id) and subtable fields (such asorder_id) distinguished by alias.

3. Recursive Query

Used to build hierarchical structures (such as tree menus, permission structures), through<collection>Self-reference implements recursion.

Features

• passselectThe attribute refers to its own or the query method of the same level to form a recursive call.

• Suitable for organizational structure, classification trees and other scenarios.

Sample code

<!-- Handle tree structureresultMap -->
<resultMap  type="Category">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="parentId" column="parent_id"/>
    <!-- Recursive query of child nodes -->
    <collection 
        property="children" 
        ofType="Category" 
        select="selectCategoriesByParentId" 
        column="id"
    />
</resultMap>

<!-- Query the child node of the parent node -->
<select  resultMap="CategoryMap">
    SELECT * FROM categories WHERE parent_id = #{id}
</select>

explain

• column="id": Convert the current node’sidPassed to the child query as parent node ID.

• <collection>Quoting the same mapperselectCategoriesByParentIdMethod, form recursion.

• Stop recursion until there are no children.

4. Use column to pass multiple parameters

When a subquery requires multiple parameters, multiple columns can be specified by expressions.

grammar

column="{param1=column1, param2=column2}"

Sample code

<collection 
    property="items" 
    ofType="Item" 
    select="" 
    column="{userId=id, date=createTime}"
/>

explain

• SubqueryselectItemsByUserAndDateTwo parameters are required:userIdanddate。

• columnThe expression will be the main tableidMap asuserId，createTimeMap asdate。

5. The difference between javaType and ofType

• ofType: Must specify, to define the type of elements in the collection (such asPermission）。

• javaType: Optional, specify the type of the collection (such asArrayList). If not specified, MyBatis is used by default.List。

Example

<!-- Explicitly specifyjavaType -->
<collection 
    property="permissions" 
    javaType="ArrayList" 
    ofType="Permission" 
    select="..." 
    column="..."
/>

Summary: Applicability of different scenarios

By reasonable choice<collection>The usage of   can efficiently handle one-to-many mapping requirements in MyBatis.

This is the end of this article about the various uses of the <collection> tag in MyBatis. For more related contents of MyBatis collection tags, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!