MyBatis One-to-Many Query Function

　Firstly, let's state a requirement: to query order details based on order id - we know that an order can contain multiple order details (for those who are not clear about the requirements, please leave a message or click on the order section on Taobao to understand). At this time, one order corresponds to multiple order ids. When this kind of requirement occurs, how should we query it?

　　At this moment, our data model is shown as follows (left). Since the query user is also one of our requirements, we have extended it on the original basis, and the data model is as follows (right):

　　　　So we need to use the resultMap method to handle it. The solution to the problem is: add an order detail List field of type Orderdetail to the orders class. Then, through the configuration file, map the data obtained to the list through the resultMap's collection tag.

　　　　It is obvious that it is not reasonable to implement it using the resultType method. Because we need to create a pojo that has both orders and order details, and then our mybatis framework will map many pojo objects for us (as many as the number of order details).

　　　　The specific implementation is as follows:

　　SQL statement

　　　　Determine the main query table: order table

　　　　Determine the associated query table: order detail table

　　　　Add the association of the order detail table on the basis of one-to-one query.

SELECT 
 orders.*,
 USER.username,
 USER.sex,
 USER.address,
 orderdetail.id orderdetail_id,
 orderdetail.items_id,
 orderdetail.items_num,
 orderdetail.orders_id
FROM
 orders,
 USER,
 orderdetail
WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id

　　Problem display

　　　　After the query is completed, we found a problem: as shown in the figure, our id appears multiple times, which is caused by multiple order details in the database using the same order id. This results in an orders record appearing in each record. The specific solution has been mentioned at the beginning, so it will not be repeated here.

　　Add a list of order detail attributes to orders　　

　　

　　　　Define resultMap

<!-- resultMap for order and order detail
  Use extends inheritance, no need to repeat the mapping of users
   -->
  <resultMap type="cn.itcast.mybatis.po.Orders" id="dinxtends="OrdersUserResultMap">
    <!-- Order information -->
    <!-- User information -->
    <!-- Use extends inheritance, no need to configure the mapping of order information and user information in it -->
    <!-- Order detail information
    A single order query returned multiple details, and collection needs to be used for mapping
    collection: Maps multiple records associated with a query to a collection object
    property: Map the multiple records associated with the query to which attribute of cn.itcast.mybatis.po.Orders
    ofType: Specify the type of pojo mapped to the list attribute
     -->
     <collection property="orderdetails" ofType="cn.itcast.mybatis.po.Orderdetail">
       <!-- id: Unique identifier of order detail
       property: Map the unique identifier of the order detail to which attribute of cn.itcast.mybatis.po.Orderdetail
        -->
       <id column="orderdetail_id" property="id">/>
       <result column="items_id" property="itemsId">/>
       <result column="items_num" property="itemsNum">/>
       <result column="orders_id" property="ordersId">/>
     </collection>
  </resultMap>

　　　　　　

　　mapper　　

　　

　　Summary

　　　　It is actually using the resultMap's collection to map multiple records of the associated query to a list attribute.

The above is what the editor introduces to everyone about the mybatis one-to-many query function, hoping it will be helpful to everyone. If you have any questions, please leave a message, and the editor will reply to everyone in time. Thank you very much for everyone's support for the Yell Tutorial website!

Declaration: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been edited by humans, and does not assume any relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#oldtoolbag.com (Please replace # with @ when sending an email to report, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.)