How to configure One-to-Many query in MyBatis?

2 years ago
Benjamin Taylor
3 minutes

In MyBatis, one-to-many queries can be achieved by configuring relationship. Below are the steps to configure one-to-many queries.

  1. In the MyBatis mapping file, first define the query statement for the main table and assign an alias to each field. For example, if the main table is user, the query statement is defined as follows:
  2. Use the following query to get user information, including their ID, username, age, and address, for a specific user ID.
  3. Here, a left join is used to connect the main table with the sub-table, linking them through the id of the main table and the user_id of the sub-table.
  4. In the mapping file, specify the query for the child table and assign an alias to each field. For example, if the child table is “address,” the query would be defined as follows:
  5. Retrieve addresses for a specific user by their user ID.
  6. Here, the user_id is used as the query condition to link the primary table’s id with the secondary table’s user_id.
  7. In the entity class corresponding to the main table, add a collection property of the entity class corresponding to the subtable to store the results of a one-to-many query. For example, add a List
    property to the User class.
  8. Omitting the getter and setter methods, the class User includes fields for id (integer), username (String), age (integer), and a list of addresses.
  9. In the mapping file, establish the association between the main table and the subordinate table using the tag. Use the tag in the query statement of the main table to reference the query statement of the subordinate table, and specify the corresponding entity class collection property of the subordinate table using the property attribute. For example:
  10. Retrieve the user’s id, username, age, and address from the user table, and left join it with the address table using the user id as the key. The query should only return results based on the specified user id. Additionally, map the addresses to the user based on the addressResultMap.
  11. The resultMap property here points to the result mapping of the subordinate table, which can be defined using another tag.
  12. Define the result mapping of the subordinate table in the query statement using the tag. For example:
  13. The “addressResultMap” is mapping properties from the Address class to the columns in the database table.
  14. Here, the tag is used to configure each field in the subordinate table to its corresponding entity class property.

By configuring the steps above, it is possible to achieve a one-to-many query. When querying the main table, MyBatis will automatically query the data from the secondary table based on the configured relationship, and map the query results to the collection property of the corresponding entity class of the main table.

bannerAds