How to achieve nested queries for one-to-many relations…

1 year ago
Emily Johnson
2 minutes

In MyBatis, a one-to-many relationship query can be achieved through nested queries.

First, it is necessary to define two entity classes: one is the entity class for the one side, and the other is the entity class for the many side. For example, define a User class and an Order class, where a user can have multiple orders.

public class User {
    private Integer id;
    private String username;
    private List<Order> orders;
    // 省略getter和setter方法
}

public class Order {
    private Integer id;
    private String orderNo;
    private Integer userId;
    // 省略getter和setter方法
}

Then, two methods are defined in the Mapper interface: one for querying user information, and another for querying order information associated with the user.

public interface UserMapper {
    User getUser(Integer id);
    List<Order> getOrdersByUserId(Integer userId);
}

Next, implement these two methods in the Mapper.xml file.

Firstly, implement the getUser method.

<select id="getUser" parameterType="Integer" resultType="User">
    SELECT * FROM user WHERE id = #{id}
</select>

Next, implement the getOrdersByUserId method:

<select id="getOrdersByUserId" parameterType="Integer" resultType="Order">
    SELECT * FROM orders WHERE userId = #{userId}
</select>

Finally, use nested queries to achieve one-to-many relationship queries. Utilize tags in the getUser method to nest the order information queries.

<select id="getUser" parameterType="Integer" resultType="User">
    SELECT * FROM user WHERE id = #{id}
    <collection property="orders" ofType="Order" select="getOrdersByUserId">
        <id property="userId" column="id"/>
    </collection>
</select>

In this way, you can retrieve a user and all their corresponding order information through the getUser method.

User user = userMapper.getUser(1);
System.out.println(user.getUsername());
for (Order order : user.getOrders()) {
    System.out.println(order.getOrderNo());
}

The above is the method of implementing nested queries for one-to-many relationships using MyBatis. Through nested queries, data from the many side can be nested within the one side to achieve a one-to-many relationship query.

bannerAds