Mybatis one-to-many query list attribute processing

- 1. Description
- - 1. `` tag attribute description
  - 2. Sample code
- 2. Tile query
- 3. Nested Select for Collection
- - 3.1 Foreign key query
  - 3.2 Select passes in multiple parameters

1. Description

1. tag attribute description

property: Specify the collection property name in the main object.

ofType: specifies the type of the collection element. The default value is java.lang.Object. The type of collection elements can be specified by specifying the ofType attribute.

column: Specifies the column name used by the collection element, which is used to match the columns in the result set. (inner select associated field, if not specified, the primary key corresponding to the foreign key will be used as the value of the column attribute by default)

columnPrefix: Specify the column name prefix used by the collection elements to match the columns in the result set. (for automatic matching of inner column mapping in flat query mode)

select: Specify the SQL query statement to get the elements of the collection.

resultSetType: Specifies the type of SQL query result set. The default value is FORWARD_ONLY, and it can also be set to SCROLL_SENSITIVE and SCROLL_INSENSITIVE. For specific usage, please refer to the JDBC documentation.

fetchType: Specifies the loading method of the collection. The default value is “lazy”, and the optional value is “eager”. When the fetchType attribute is set to “eager”, MyBatis will return the result set after executing the SQL statement , immediately loads the collection elements.

javaType: Specifies the Java type of the collection. Usually, it does not need to be specified. MyBatis will automatically infer it according to the type of the collection property.

jdbcType: Specifies the type of the collection attribute in the database, you can refer to the type definition in JDBC. If not specified, MyBatis will automatically infer.

notNullColumn: Specifies the name of one or more attribute columns that cannot be null. MyBatis throws an exception when these property columns are empty. The notNullColumn attribute can be separated by commas (,) to specify multiple attribute columns.

columnOverride: Specify the mapping relationship between one or more element attributes and the columns in the result set.

typeHandler: specifies the type handler for an element attribute.

ofTypeHandler: Specifies the type handler for the collection elements.

resultMap: A mapper that specifies the elements of a collection, usually defined by nesting result tags, and specified using the id of the resultMap.

orderBy: Specify ascending or descending order. When multiple sorting conditions are used, separate them with spaces. For example, orderBy=”id desc,create_time asc” means to sort by id in descending order first, and then in ascending order by createTime. (not recommended, it is recommended to manually specify the sorting method in SQL)

notNullColumn: Specifies the name of one or more attribute columns that cannot be null. MyBatis throws an exception when these property columns are empty. The notNullColumn attribute can be separated by commas (,) to specify multiple attribute columns.

statementType: Specify the type of SQL executed by the operation. There are three types: STATEMENT, PREPARED and CALLABLE, which represent simple statements, prepared statements and call stored procedures. The default is PREPARED.

columnPrefix usage

If the columnPrefix attribute is used, the column attribute in the result tag can be omitted, and MyBatis will automatically complete the mapping between the attribute and the column name.

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

Use the columnPrefix prefix to simplify the code. When using it, use table alias as the column prefix, for example columnPrefix="r."

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

mybatis official document

2. Sample code

Entity class

public class User {<!-- -->
    private Integer id;
    private String name;
    private Integer age;
    private List<Order> orders;

    // omit getter and setter methods
}

public class Order {<!-- -->
    private Integer id;
    private String orderNo;
    private Date createTime;

    // omit getter and setter methods
}

2. Tile query

Can be used in combination with columnPrefix and resultMap

Mybatis XML

<select id="getUserOrdersById" resultMap="userResultMap">
  SELECT u.id, u.name, u.age, o.id AS order_id, o.order_no, o.create_time
  FROM user u
  INNER JOIN `order` o ON u.id = o.user_id
  WHERE u.id = #{<!-- -->id}
</select>

<resultMap id="userResultMap" type="com.example.User">
  <id property="id" column="id" />
  <result property="name" column="name" />
  <result property="age" column="age" />
  <collection property="orders" ofType="com.example.Order" resultMap="orderResultMap" />
</resultMap>

<resultMap id="orderResultMap" type="com.example.Order">
  <id property="id" column="order_id" />
  <result property="orderNo" column="order_no" />
  <result property="createTime" column="create_time" />
</resultMap>

3. Nested Select for Collection

Nested queries support lazy loading. You can adjust the collection loading method by setting fetchType. The default value is “lazy”, and the optional value is “eager”.

3.1 Foreign key query

<select id="getUserOrdersById" resultMap="userResultMap">
  SELECT u.id, u.name, u.age
  FROM user u
  WHERE u.id = #{id}
</select>

<select id="getOrderByUserId" resultMap="orderResultMap">
  SELECT o.id, o.order_no, o.create_time
  FROM `order` o
  WHERE o. user_id = #{userId}
</select>

<resultMap id="orderResultMap" type="com.example.Order">
  <id property="id" column="order_id" />
  <result property="orderNo" column="order_no" />
  <result property="createTime" column="create_time" />
</resultMap>

<resultMap id="userResultMap" type="com.example.User">
  <id property="id" column="id" />
  <result property="name" column="name" />
  <result property="age" column="age" />
  <collection property="orders" ofType="com.example.Order" resultMap="orderResultMap" select="getOrderByUserId" column="id">
      <!-- Here column="id" is used to specify that the parameter value of the inner select statement is the id attribute value (ie user ID) in the result set of the outer query statement -->
  </collection>
</resultMap>

3.2 select to pass in multiple parameters

As mentioned above, when specifying select to nest SQL, you need to specify column="id". If the inner SQL needs to pass in multiple parameters, you can use the following method

public class OrderQuery {<!-- -->
    private Long userId; // user id
    private Integer status; // order status
    // getter and setter methods
}

<select id="getOrdersByUserId" resultType="Order">
    SELECT *
    FROM `order`
    WHERE user_id = #{userId}
    AND status = #{status}
</select>

<resultMap id="userMap" type="User">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="age" column="age"/>
    <!-- Note that the select attribute of the association tag here uses the properties of the OrderQuery object -->
    <result property="orders" column="id"
            select="com.example.mapper.OrderMapper.getOrdersByUserId">
        <association property="param" javaType="OrderQuery">
            <result property="userId" column="id"/>
            <result property="status" value="1"/> <!-- Here is a fixed value, which can also be replaced by a dynamic expression -->
        </association>
    </result>
</resultMap>

association one-to-one attribute processing

The above describes the processing of one-to-many query list attributes. In fact, association is more commonly used in the processing of one-to-one attributes.

package com.example;

public class Order {<!-- -->
  private int orderId;
  private String orderName;
  private Customer customer;

  // getters and setters for orderId, orderName, customer
}

package com.example;

public class Customer {<!-- -->
  private int customerId;
  private String customerName;

  // getters and setters for customerId, customerName
}

<resultMap id="orderMap" type="com.example.Order">
  <id property="orderId" column="order_id"/>
  <result property="orderName" column="order_name"/>
  <association property="customer" javaType="com.example.Customer">
    <id property="customerId" column="customer_id"/>
    <result property="customerName" column="customer_name"/>
  </association>
</resultMap>

In this example, the Order class has a Customer property, and we use the Association association object to map the Customer from the query to the Customer property of the Order object.
It should be noted that Association can also be used nestedly, and we can realize the association between multiple objects through multi-layer Association. In addition, it should be noted that when mapping the association object Association, make sure that the JOIN operation in the SQL statement is correct.