Implementation of many to many query based on mybatis advanced mapping

1. As before, first give a user a many to many requirement,

To query the user and the commodity information purchased by the user, after analysis, there is no relationship between the user and the commodity database level. The relationship between the user and the commodity needs to be established through the order and order details. According to this requirement, the main tables to be queried are:

Query main table: user table

Query association table: because there is no relationship between goods and users, they are associated through orders and order details, so the association tables are: orders order table, orderdetail order details and items goods table. In this case, how to write SQL? Write this:

In order to facilitate mapping, add aliases appropriately. The above is a large SQL statement written. This SQL statement is getting larger and larger, but don't be afraid. Map one by one later. As long as the method is mentioned, it is very simple.

2. Prepare mapping file analysis:

Now the main table is user, so map the user's information to user. A user can create multiple orders, so add the list < orders > ORDERLIST attribute to the user attribute, which is more and more like hibernate. Then map the orders created by the user to the ORDERLIST. An order can include multiple order details, Therefore, add the order details list attribute in orders: List < orderdetail > orderdetails. An order detail only includes one commodity information, so add the commodity items attribute in ordersdetail.

User. The Java code is as follows:

Orders. The Java code is as follows:

OrdersDetail. The Java code is as follows:

Here, the relationship between POJO classes is set up. Next, write mapper xml

The code is as follows (pay attention to the notes in the code):

With the previous one to many mapping, it is easier to write this analogy; You can't use inheritance here because you find it impossible to apply it. So I can only finish it honestly.

Next, add a method in the interface to query the product information purchased by the user

Finally, write the test class:

The results are as follows:

User goods Png can see my expanded list, whether all the data I want are out;

Summary:

Here, some people may think that this is more troublesome than many to many in hibernate. Now, if there is such a demand, Query the detailed list of commodity information purchased by users (user name, user address, purchase commodity name, purchase commodity time, purchase commodity quantity). At this time, should you use resultmap or resulttype? At this time, you will find that for the above requirements, you can use resulttype to map the queried information to an expanded POJO. It is easy to use. Just add attributes. In this way, you can easily realize the detailed list The functions of the bill, such as the telephone bill, who Zhang San calls at what time, and who Zhang San calls at what time, there is no need to repeat the record. Therefore, according to the requirements, not all one to many queries are the resultmap above. Using resultmap is a function that has special requirements for query results. For example, mapping to list also includes multiple lists.

One to many is a many to many feature: query the information of the goods purchased by the user, and the relationship between the user and the goods is many to many.

Demand 1: query fields: user account number, user name, user gender, commodity name, commodity price (the most common). Common details in enterprise development, user purchase goods details, etc.

Method: use resulttype to output the above items

Demand 2: query fields: user account number, user name, quantity of purchased goods, and commodity details (move the mouse to display the details)

Method: use resultmap to map the product details purchased by the user to the user.

Summary of resultmap:

resultType：

Function: map query results into POJO according to SQL column names and POJO attribute names

Occasion: display of some common detailed records, such as the details of goods purchased by users. When all the information of associated query is displayed on the page, use resulttype to map each record to POJO and traverse the list on the front page.

resultMap：

Use association and collection to complete one-to-one and one to many high-level mapping (with special requirements for results).

association：

Function: map the information of associated query to a POJO

Occasion: to facilitate the query of associated information, you can use association to map the associated order information into the POJO attribute of the user object, for example, query order association to query user information.

collection：

Function: map the associated query user information to a list collection

Occasion: in order to facilitate the query and traversal of the associated information, you can use collection to map the associated information to the list set. For example, you can query the user permission range module and the menu under the module. You can use collection to map the module to the list and traverse the menu list!

The above implementation of many to many query based on mybatis advanced mapping is all the content shared by Xiaobian. I hope it can give you a reference and support more programming tips.