MyBatis: The three most common writing methods for multi-table query

Preface: Does a link table make you bald?

Have you written about this requirement?

• Query a user and its role list
• Inquiry of orders and details
• Query the project and its subordinates for multiple stages and persons in charge

Finally, I wrote a lot of complex SQL + resultMaps. After running, I found that either the data is duplicated or the data is lost. The worst thing is that it may trigger N+1 query...

In fact, the problem is not that MyBatis is not working, but that the method you use is wrong.

🔍 Three most common ways to write

✅ Method 1: Conjunction table query + flat mapping (recommended)

This is meMost commonly used and most stableOne way: write a conjunction table SQL, find out all the required fields, return to the flat structure, and then assemble it in the Java layer.

Example SQL:

SELECT  AS userId,  AS userName,
        AS roleId,  AS roleName
FROM user u
LEFT JOIN user_role ur ON  = ur.user_id
LEFT JOIN role r ON ur.role_id = 

Features:

• A SQL checks all data;
• High performance, logically intuitive;
• Can work perfectly with paging plugins such as PageHelper.

My advice:

For Java layerMap<Long, UserDTO>Grouping encapsulation is suitable for unified aggregation logic at the Service layer.

⚙️ Method 2: Nested result (resultMap + collection)

Suitable for "tree structures" with clearer structures, such as orders and line items, items and submodules.

Example XML:

<resultMap  type="User">
  <id property="id" column="user_id"/>
  <result property="name" column="user_name"/>
  <collection property="roles" ofType="Role">
    <id property="id" column="role_id"/>
    <result property="name" column="role_name"/>
  </collection>
</resultMap>

Features:

• The multi-table mapping level is clear;
• More suitable for small and medium-sized data volumes.

Experience in trapping:

• Writing the column name field incorrectly will result in the inner collection being null;
• When duplicate data occurs, manually deduplicate it or use Set;
• Not suitable for paging, it can easily lead to "the paging is the main table, but the collection is the full quantity".

🧩 Method 3: nested query (nested select)

Suitable for scenarios where logic decoupling and maintenance is based on modules.

Example XML:

<collection property="roles" ofType="Role" select="selectRolesByUserId" column="id"/>

Features:

• Each primary table data triggers a secondary query;
• Easy to maintain butVery easy N+1；
• Not suitable for large-scale data query.

Practical suggestions:

Unless you can guarantee that up to 10 main records will be checked at each time, use it with caution.Performance bottlenecks are easily present here!

📉The pit you may have stepped on

✅ Practical advice: How to choose these three methods?

📦 My best practice template (for real projects)

• The DAO layer only checks data, does not perform structural assembly；
• Service layer assembles DTO according to business model；
• Do not directly send the results of multiple tables to the front-end, and unify the encapsulation of the response structure;
• DTO + Builder mode + Map grouping, flexible and easy to use;

📌 A summary of the sentence:

Multi-table query and select the right method, write code to reduce half of the bugs.

There is no need to worry about which method is the most "perfect", the key isIs it suitable for your current scenario。
remember:If you can use joint searches, don’t do sub-queries; try not to use nested structures when paging scenarios; small data is convenient for graphs, and structures are preferred.

This is the end of this article about MyBatis multi-table query. For more related contents of MyBatis multi-table query, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!