How can Mybatis return fields from multiple tables?

9 months ago

William Carter

2 minutes

In MyBatis, you can use nested queries or join queries to retrieve fields from multiple tables.

Nested queries: By using nested queries, it is possible to retrieve fields from multiple tables within a single query. When defining the Mapper XML file, nested select statements can be used to query associated tables and map the results to corresponding Java objects.

例子:

<select id="getUserWithOrders" resultMap="UserWithOrdersResultMap">
    select * from users
    where user_id = #{userId}
</select>

<resultMap id="UserWithOrdersResultMap" type="User">
    <result property="userId" column="user_id"/>
    <result property="userName" column="user_name"/>
    <collection property="orders" ofType="Order" resultMap="OrderResultMap"/>
</resultMap>

<resultMap id="OrderResultMap" type="Order">
    <result property="orderId" column="order_id"/>
    <result property="orderDate" column="order_date"/>
    <result property="totalAmount" column="total_amount"/>
</resultMap>

Join queries: Join queries allow for multiple tables to be included in a single query, mapping results to corresponding Java objects while defining the Mapper XML file.

For example:

<select id="getUserWithOrders" resultMap="UserWithOrdersResultMap">
    select u.*, o.*
    from users u
    join orders o on u.user_id = o.user_id
    where u.user_id = #{userId}
</select>

<resultMap id="UserWithOrdersResultMap" type="User">
    <result property="userId" column="user_id"/>
    <result property="userName" column="user_name"/>
    <result property="orderId" column="order_id"/>
    <result property="orderDate" column="order_date"/>
    <result property="totalAmount" column="total_amount"/>
</resultMap>

By using the methods mentioned above, it is possible to retrieve fields from multiple tables in MyBatis. The appropriate method for querying related table data should be chosen based on specific business requirements.