Data Interaction between Multiple Tables
In fact, one-to-one and one-to-many mappings have already been touched on in the previous configuration. I explained directly in the log because I wanted to write a summary log (that is, this article) after that to summarize the configuration of these advanced mappings. For example, one-to-one queries are involved in related nested result set queries, while one-to-many queries are added to one or more nested result sets, which can be either an entity class or a collection. Many-to-many queries are slightly more complicated. For example, in a mall management system, a customer can have more than one item in a shopping list, and a product can be selected by more than one customer. The relationship between them is many-to-many. Suppose there is a requirement to query all the customer information in the database, including their shopping list and the goods information in the list. What should we do? I'll talk about it next.
Whether one-to-one, one-to-many or many-to-many queries involve data interaction between different tables, because they need primary and foreign key relationships to correlate. For example, customer lists and shopping list tables have customer id attributes. According to this attribute, different customers can be queried for their corresponding shopping lists. After having primary and foreign key relationships, they will Advanced mapping is possible.
Case Model
In order to show three mapping examples, a shopping mall management system model is established, which contains three data tables: customer table, shopping cart table and product list. There is a one-to-many relationship between the customer and the list of goods, because a customer can go into the shopping mall many times, and each purchase generates a shopping list (shopping cart), and each shopping list belongs to only one customer, so the relationship between the customer list and the shopping cart table is one-to-many. There is also a product list, because there can be multiple items in a shopping list, so the product list and the shopping cart list are one-to-many relationships. Because a customer can buy more than one product, and a product can also belong to more than one customer, so customers and goods constitute a multi-to-many relationship. When we test, we can set up three basic tables: customer table, shopping cart table and commodity table (of course, it is better to add more tables, such as commodity evaluation table). The query between them can combine one-to-one, one-to-many and many-to-many relationships. After clearing up the relationship between the tables, we can test them.
One-to-one nested result set query
From the example model above, we can see that there are two tables with one-to-one relationship, shopping cart table and user table. We should pay attention to the order of the two tables. Shopping cart and customer are one-to-one relationship, but not vice versa. Customer and shopping cart are one-to-many relationship. In the query requirement, this multi-table one-to-one query scenario is typically one-to-one nested result set query. For example, if we want to query the shopping cart table, a shopping list information, and the corresponding personal information of the customer. There are two ways to configure, or two ways to achieve:
resultType combines query wrapper classes
To implement the one-to-one nested result set query above, if you use resultType to configure the SQL mapping, because it involves the interaction between two tables and can only be mapped to one entity class, you need to create a new query wrapper class to include the attributes corresponding to the result set fields of the two tables contained therein. The expression is not good -,. That is to say, a new query wrapper class is created, which contains not only the attributes corresponding to the fields in the shopping cart table, but also the attributes corresponding to the fields in the user table, so as to receive the data in the two tables contained in the result set. Take a look at this query wrapper class:
// Shopping cart table one-to-many query packaging class
public class ShoppingCartInstance extends ShoppingCart implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private String username; //Customer Name
private String gender; //Gender
private String province; //provincial capital
private String city; //City
public ShoppingCartInstance() {
}
public ShoppingCartInstance(int cartId, int userId, int productId,
String productName, int number,
double price, User user, String username, String gender, String province,
String city) {
super(cartId, userId, productId, productName, number, price);
this.username = username;
this.gender = gender;
this.province = province;
this.city = city;
}
// Omit get() and set() methods
}Our query wrapper class inherits all the attributes of the shopping cart entity class. Of course, you can redefine them one by one, so that some unnecessary attributes can be discarded. Then, the attributes in the customer table are added to the query package class to correspond to the fields of the customer data table in the return result set.
After the data wrapping class is ready, we can write our SQL statements and mapping configuration:
<!-- One-to-one query: resultType Realization -->
<select id="queryShoppingCartInstance1" parameterType="int" resultType="ShoppingCartInstance">
SELECT
S.cartId, S.productName, S.userId, S.price, S.number,
U.id, U.username, U.gender, U.province, U.city
FROM ShoppingCart S left outer join user U on S.userId = U.id
WHERE S.cartId = #{id}
</select>The SQL statement queries the shopping list according to id, and associates another data table, user customer table, according to the foreign key.
public void TestAssociationQuery() throws IOException {
SqlSession sqlSession = dataConn.getSqlSession();
List<ShoppingCartInstance> resultList = sqlSession.selectList("queryShoppingCartInstance1", 2);
StringBuffer result = new StringBuffer();
double totalAmount;
System.out.println("Customer Name: " + resultList.get(0).getUsername());
System.out.println("Gender: " + resultList.get(0).getGender());
System.out.println("List of commodities:" + "\r\n");
for(int i=0; i<resultList.size(); i++) {
ShoppingCartInstance shoppingCartInstance = resultList.get(i);
result.append("Commodity name:" + shoppingCartInstance.getProductName() + "\r\n");
result.append("Quantity of goods:" + shoppingCartInstance.getNumber() + "\r\n");
totalAmount = (shoppingCartInstance.getPrice()*shoppingCartInstance.getNumber());
result.append("Total commodity price:" + String.format("%.2f", totalAmount) + "\r\n");
System.out.println(result.toString());
result.setLength(0);
}
sqlSession.close();
}Finally, write a simple test case to query the list of goods with the shopping cart id of 2 and the corresponding customer information.
resultMap combines association Tags
With resultMap configuration, flexibility is greatly enhanced. First, resultMap allows us to configure one-to-one mapping between field names in the return result set and attribute names in the Java wrapper class. And the association tag and collection tag provided in resultMap are powerful and can configure the interaction between multiple tables. If the resultMap configuration is used in the above example, we can directly add an instance of the User class to the query wrapper class:
public class ShoppingCartInstance extends ShoppingCart implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
// private String username; // Customer name
// private String gender; //Gender
// private String province; //Provincial Capital
// private String city; //City
private User user; //Corresponding users
private Product products;
public ShoppingCartInstance() {
}
public ShoppingCartInstance(int cartId, int userId, int productId,
String productName, int number,
double price, User user) {
super(cartId, userId, productId, productName, number, price);
// this.username = username;
// this.gender = gender;
// this.province = province;
// this.city = city;
this.user = user;
}
// Omit get() and set() methods
}This User class instance is used to receive customer information returned to the result set. Next, we configure their mapping relationships:
<resultMap type="shoppingCartInstance" id="shoppingCartResult"> <id property="id" column="shoppingCart_id"/> <result property="cartId" column="cart_id"/> <result property="productName" column="product_name"/> <result property="price" column="product_price"/> <result property="number" column="product_number"/> <result property="productId" column="product_id"/> <association property="user" column="cartUser_id" javaType="com.mybatis.po.User" resultMap="userResult"/> </resultMap> <resultMap type="com.mybatis.po.User" id="userResult"> <id property="id" column="user_id"/> <!-- To configure User Mapping Relations in Classes --> <result property="username" column="user_name"/> <result property="gender" column="user_gender"/> <result property="province" column="user_province"/> <result property="city" column="user_city"/> </resultMap>
In the mapping relation configuration resultMap of query wrapper class, type indicates that the received entity class is shoppingCartInstance. In addition to configuring the mapping of various database fields and entity class attributes, the next step is to configure the associated nested result set with association tag, that is, to query the instance of User class in wrapper class, and to specify User class. The mapping relationship resultMap of a class refers to the external userResult (that is, the one below).
After configuring the resultMap, the SQL statement is very simple, just change the resultType to resultMap:
<!-- One-to-many queries SQL Sentence -->
<select id="queryShoppingCartInstance2" parameterType="int" resultMap="shoppingCartResult">
SELECT
S.cartId as cart_id,
S.productName as product_name,
S.userId as cartUser_id,
S.price as product_price,
S.number as product_number,
U.id as user_id,
U.username as user_name,
U.gender as user_gender,
U.province as user_province,
U.city as user_city
FROM ShoppingCart S left outer join user U on S.userId = U.id
WHERE S.cartId = #{id}
</select>Finally, test cases:
public void TestAssociationQuery() throws IOException {
SqlSession sqlSession = dataConn.getSqlSession();
List<ShoppingCartInstance> resultList = sqlSession.selectList("queryShoppingCartInstance2", 2);
StringBuffer result = new StringBuffer();
double totalAmount;
System.out.println("Customer Name: " + resultList.get(0).getUser().getUsername());
System.out.println("Gender: " + resultList.get(0).getUser().getGender());
System.out.println("List of commodities:" + "\r\n");
for(int i=0; i<resultList.size(); i++) {
ShoppingCartInstance shoppingCartInstance = resultList.get(i);
result.append("Commodity name:" + shoppingCartInstance.getProductName() + "\r\n");
result.append("Quantity of goods:" + shoppingCartInstance.getNumber() + "\r\n");
totalAmount = (shoppingCartInstance.getPrice()*shoppingCartInstance.getNumber());
result.append("Total commodity price:" + String.format("%.2f", totalAmount) + "\r\n");
System.out.println(result.toString());
result.setLength(0);
}
sqlSession.close();
}
One-to-many queries
One-to-many query, if we add one on the basis of the above example, and query some commodity information of each item in the shopping list, such as the customer's evaluation of the item, how to do?
Packaging class and SQL mapping configuration
First of all, look at the query packaging class. Since it is based on the previous example, we need to add a result set attribute to the original query packaging class. Because a commodity can be evaluated by multiple customers, we use a set of Li to evaluate the commodity attributes. St denotes:
// One-to-many query packaging class
public class ShoppingCartInstance extends ShoppingCart implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private User user; //Corresponding users
private List<ProductAppraise> productAppraise; //Assessment Set of Commodities
public ShoppingCartInstance() {
}
public ShoppingCartInstance(int cartId, int userId, int productId,
String productName, int number,
double price, User user, List<ProductAppraise> productAppraise) {
super(cartId, userId, productId, productName, number, price);
this.user = user;
this.productAppraise = productAppraise;
}
// Omit get() and set() methods
}After the query wrapper class is defined, go to the SQL statement and mapping configuration. Because of nested result sets and combinations, it is obvious that we use resultMap to configure mapping relationships:
<!-- One-to-many mapping configuration --> <resultMap type="shoppingCartInstance" id="multiShoppingCartResult" extends="shoppingCartResult"> <collection property="productAppraise" ofType="ProductAppraise"> <!-- Primary key --> <id column="id" property="id"/> <result property="productId" column="productAppraise_id"/> <result property="productScore" column="product_score"/> <result property="userId" column="user_id"/> </collection> </resultMap>
Because our requirement is to increase the evaluation of the query products on the basis of the above queries, we only need to inherit the above resultMap, and then add a nested set configuration, using the collection tag.
Configuration is not complete, and there are the most important SQL statements:
<!-- One-to-many queries SQL Sentence -->
<select id="queryShoppingCartInstance2" parameterType="int" resultMap="shoppingCartResult">
SELECT
S.cartId as cart_id,
S.productName as product_name,
S.userId as cartUser_id,
S.price as product_price,
S.number as product_number,
U.id as user_id,
U.username as user_name,
U.gender as user_gender,
U.province as user_province,
U.city as user_city
FROM ShoppingCart S left outer join user U on S.userId = U.id
WHERE S.cartId = #{id}
</select>In the SQL statement, query the data in three tables, associate the customer with the corresponding shopping cart according to the foreign key customer id, and associate the evaluation information of the goods and goods in the shopping cart according to the product Id.
test case
Finally, a test case of one-to-many queries is presented.
// One-to-many query test cases
public void TestAssociationQuery() throws IOException {
SqlSession sqlSession = dataConn.getSqlSession();
List<ShoppingCartInstance> resultList = sqlSession.selectList("queryShoppingCartInstance4");
StringBuffer result = new StringBuffer();
double totalAmount;
System.out.println("Customer Name: " + resultList.get(0).getUser().getUsername());
System.out.println("Gender: " + resultList.get(0).getUser().getGender());
System.out.println("List of commodities:" + "\r\n");
for(int i=0; i<resultList.size(); i++) {
ShoppingCartInstance shoppingCartInstance = resultList.get(i);
result.append("Commodity name:" + shoppingCartInstance.getProductName() + "\r\n");
result.append("Quantity of goods:" + shoppingCartInstance.getNumber() + "\r\n");
totalAmount = (shoppingCartInstance.getPrice()*shoppingCartInstance.getNumber());
result.append("Total commodity price:" + String.format("%.2f", totalAmount) + "\r\n");
List<ProductAppraise> appraiseList = shoppingCartInstance.getProductAppraise();
for(int j=0; j<appraiseList.size(); j++) {
ProductAppraise productAppraise = appraiseList.get(j);
System.out.println("Commodity score:" + productAppraise.getProductScore());
}
System.out.println(result.toString());
result.setLength(0);
}
sqlSession.close();
}In the test case, every time a shopping CartInstance object is obtained from the result set, the set of commodity evaluation is obtained from the object in line 18, and then the commodity score is obtained from the set of commodity evaluation. Here we should pay attention to it.
Test results:
Many-to-many queries
There is also a more complex need to use many-to-many queries. From the previous case model, it can be seen that the relationship between customers and commodities is a multi-to-many relationship, because a customer can buy a variety of commodities, the same commodity can also be purchased by multiple customers, which forms a very obvious many-to-many relationship. If there is such a requirement now, query all customers, as well as the list of goods corresponding to these customers, as well as the list of all goods in the commodity id information. How do you do it? Or start by querying the wrapper class:
// Multi-to-Multi Query Packaging Class
public class ShoppingCartInstance extends ShoppingCart implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private User user; //Corresponding users
private Product products;
public ShoppingCartInstance() {
}
public ShoppingCartInstance(int cartId, int userId, int productId,
String productName, int number,
double price, User user, Product products) {
super(cartId, userId, productId, productName, number, price);
this.user = user;
this.products = products;
}
// Omit get() and set() methods
}In order to simplify the example, we only query the commodity id information in the commodity information, and then query the packaging class. On the basis of the above example, we remove the commodity evaluation set and add a commodity result set entity class instance object products to receive the commodity information.
Multiple association Tags
Next to the SQL mapping configuration:
<resultMap type="shoppingCartInstance" id="shoppingCartResult"> <id property="id" column="shoppingCart_id"/> <result property="cartId" column="cart_id"/> <result property="productName" column="product_name"/> <result property="price" column="product_price"/> <result property="number" column="product_number"/> <result property="productId" column="product_id"/> <association property="user" column="cartUser_id" javaType="com.mybatis.po.User" resultMap="userResult"/> <association property="products" column="cartUser_id" javaType="com.mybatis.po.Product"> <result property="productId" column="products_id"/> </association> </resultMap> <resultMap type="com.mybatis.po.User" id="userResult"> <id property="id" column="user_id"/> <!-- To configure User Mapping Relations in Classes --> <result property="username" column="user_name"/> <result property="gender" column="user_gender"/> <result property="province" column="user_province"/> <result property="city" column="user_city"/> </resultMap>
In the multi-to-multi mapping configuration, it is possible to note that two association tags are used, because we have two entity classes nested in our query wrapper class, one customer class and one commodity class. One difference between the two associations tag configurations is that the customer mapping relationship configuration uses an external resultMap, while the commodity information mapping relationship configuration is configurated directly in the association tag. Both methods can be used. After configuring the SQL mapping, go to the SQL statement:
<!-- Many-to-many queries SQL Sample statement --> <select id="queryShoppingCartInstance4" resultMap="shoppingCartResult"> SELECT S.cartId as cart_id, S.productName as product_name, S.userId as cartUser_id, S.price as product_price, S.number as product_number, S.productId as product_id, U.id as user_id, U.username as user_name, U.gender as user_gender, U.province as user_province, U.city as user_city, P.productId as products_id FROM Products P, ShoppingCart S, user U WHERE S.userId = U.id AND S.productId = P.productId </select>
This is relatively simple, pay attention to the key links, through the customer id related to the customer and customer's list of goods, through the commodity id related to the list of goods and commodity information. Finally, test cases:
// Many-to-many test cases
public void TestAssociationQuery() throws IOException {
SqlSession sqlSession = dataConn.getSqlSession();
List<ShoppingCartInstance> resultList = sqlSession.selectList("queryShoppingCartInstance4");
StringBuffer result = new StringBuffer();
double totalAmount;
for(int i=0; i<resultList.size(); i++) {
ShoppingCartInstance shoppingCartInstance = resultList.get(i);
result.append("Customer Name: " + shoppingCartInstance.getUser().getUsername() + "\r\n");
result.append("Gender: " + shoppingCartInstance.getUser().getGender() + "\r\n");
result.append("commodity id: " + shoppingCartInstance.getProducts().getProductId() + "\r\n");
result.append("Commodity name:" + shoppingCartInstance.getProductName() + "\r\n");
result.append("Quantity of goods:" + shoppingCartInstance.getNumber() + "\r\n");
totalAmount = (shoppingCartInstance.getPrice()*shoppingCartInstance.getNumber());
result.append("Total commodity price:" + String.format("%.2f", totalAmount) + "\r\n");
System.out.println(result.toString());
result.setLength(0);
}
sqlSession.close();
}It is also concise and clear, because all information is in a query packaging class, we can take out one by one:
Complete implementation has been uploaded GitHub:
https://github.com/justinzengtm/SSM-Framework