How to configure a one-to-many relationship in MyBatis?

2 years ago
Benjamin Taylor
2 minutes

To set up a one-to-many relationship in MyBatis, nested queries and nested result mappings are required.

Firstly, two query statements need to be defined in the Mapper XML file, one for retrieving information from the main table and another for retrieving information from the sub table. For example:

<!-- 查询主表信息 -->
<select id="selectMainTable" resultMap="MainTableResultMap">
    SELECT *
    FROM main_table
</select>

<!-- 查询从表信息 -->
<select id="selectSubTable" resultMap="SubTableResultMap">
    SELECT *
    FROM sub_table
    WHERE main_table_id = #{mainTableId}
</select>

Next, it is necessary to define two ResultMaps, each used to map the result sets of the main table and the subordinate table. For example:

<!-- 主表ResultMap -->
<resultMap id="MainTableResultMap" type="MainTable">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <!-- 定义从表的集合 -->
    <collection property="subTables" ofType="SubTable" resultMap="SubTableResultMap" />
</resultMap>

<!-- 从表ResultMap -->
<resultMap id="SubTableResultMap" type="SubTable">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <result property="mainTableId" column="main_table_id" />
</resultMap>

Finally, define a method in the Mapper interface to call the two previously mentioned query statements in order to achieve a one-to-many relationship query. For example:

public interface MainTableMapper {
    MainTable selectMainTable(int id);
}

When the selectMainTable method is called, MyBatis will first execute the SQL query to retrieve information from the primary table, then based on the primary table’s ID value, it will execute the SQL query to retrieve information from the secondary table and map the query results to the secondary table collection property in the primary table object.

bannerAds