<> one-on-one （resultType Implementation mode ）
Here we use the order and user model , From the order point of view , An order can only be created for one user , So it's one-on-one , But from the user's point of view , One user can create multiple orders , So it's one to many . But we're here from an order point of view , Then it can be treated as a pair
1. So we build a requirement ： Query the order and associate with the user who placed the order
2. Create user table and order table and establish good relationship
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username`
varchar(32) NOT NULL COMMENT ' User name ', `birthday` date DEFAULT NULL COMMENT ' birthday ',
`sex` char(1) DEFAULT NULL COMMENT ' Gender ', `address` varchar(256) DEFAULT NULL
COMMENT ' address ', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT
CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id`
int(11) NOT NULL COMMENT ' Order user id', `number` varchar(32) NOT NULL COMMENT ' order number ',
`createtime` datetime NOT NULL COMMENT ' Order creation time ', `note` varchar(100) DEFAULT
NULL COMMENT ' remarks ', PRIMARY KEY (`id`), KEY `FK_orders_1` (`user_id`),
CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON
DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT
Then we insert a little bit of data manually
3. Write on demand sql sentence
Query all the information of the order and query the user information of the order
4. Yes sql Statement, we can create the pojo class
order pojo, Generated attribute getter and setter method
Since the order user has only three attributes , Then we choose to create a new class to inherit the order pojo that will do
5. since pojo There are classes , So where do you map the query results ? It's obviously mapped to this subclass here , Because a subclass can pass through a parent getter Method to get all the properties of the parent class （ Although these attributes are private Of ）, So you can write it Mapper.xml The document is ready
6. Then it can be based on mapper.xml Documentation mapper The interface is over （ use mapper Development of interface agent dao）
7. OK, so you can write a test class to test it
8. We use debug To see the results
<> one-on-one （resultMap Implementation mode ）
use resultMap You can map the information from the association query to a database pojo On the properties of , For example, here we can Orders Class pojo As its attribute , Then map the association result to this attribute .
1. New one User class
2. stay Orders Add in User attribute , generate getter and setter method
3. use resultMap Written in the same way mapper.xml file , Write this resultMap We can do it according to the database query results , You'll find it's not so messy , Write well id Unique identity of , For fields and maps of query results pojo Property name of
<!-- definition resultMap Map the entire query result to Orders in --> <resultMap id="OrderUserResultMap"
type="orders"> <!--column Specifies the unique identity in the query column property Specifies the property name of the mapping type --> <id column="id"
property="id"/> <!-- The common column is the same as above --> <result column="user_id" property="userId"/>
<result column="number" property="number"/> <result column="createtime"
property="createtime"/> <result column="note" property="note"/> <!-- Configure association mapping information
property: Map associated information to Orders On which property of the class javaType: What type of information is mapped --> <association
property="user" javaType="user"> <!-- Unique identity of the associated query column： Specifies the column name of the query that uniquely identifies the user
（orders Foreign key of table user_id It's about building a new business user Primary Key id On ） property： Which attribute is mapped to the user --> <id
column="user_id" property="id"/> <!-- Common attributes are the same as above --> <result column="username"
property="username"/> <result column="sex" property="sex"/> <result
column="birthday" property="birthday"/> <result column="address"
property="address"/> </association> </resultMap>
<!-- Query the order and query the order user information at the same time , Use the definition above resultMap--> <select id="findOrdersUserResultMap"
resultMap="OrderUserResultMap"> SELECT orders.*, USER.username, USER.birthday,
USER.sex, USER.address FROM orders, USER WHERE orders.user_id = user.id
4. to write Mapper Interface
5. Test class （ Add a unit test to the above test class ）
<> One to one query summary
1.resultType： use resultType The implementation is relatively simple , If pojo The queried column names are not included in the , The attribute corresponding to the column name needs to be added , The mapping is completed , Therefore, if there are no special requirements for query results, it is recommended to use resultType.
2.resultMap： It needs to be defined separately resultMap, It's a bit cumbersome to implement , If there are special requirements for query results , use resultMap You can map the associated query pojo In the properties of , although resultMap trouble , however resultMap Delay loading can be realized
<> One to many query （ Recommended use resultMap）
Because if you use resultType Duplicate data may be found , If you want to avoid this situation, then you have to write your own loop judgment conditions , It's troublesome , Why do you say that . Let's analyze a requirement first
1. Realize a query order at the same time query user information and order details , There must be commodity information in the order details , So we need to create one more commodity table , Then we can map the query data to the order table , So we only add order details to the order , And then write it resultMap Map the association information to this attribute
Here is a list of products
CREATE TABLE `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name`
varchar(32) NOT NULL COMMENT ' Trade name ', `price` float(10,1) NOT NULL COMMENT
' merchandise valuation ', `detail` text COMMENT ' Product description ', `pic` varchar(64) DEFAULT NULL COMMENT
' Product picture ', `createtime` datetime NOT NULL COMMENT ' date of manufacture ', PRIMARY KEY (`id`) )
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
2. Create an order detail table , Build good relationship with order table and commodity table
CREATE TABLE `orderdetail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `orders_id`
int(11) NOT NULL COMMENT ' order id', `items_id` int(11) NOT NULL COMMENT ' commodity id',
`items_num` int(11) DEFAULT NULL COMMENT ' Quantity of goods purchased ', PRIMARY KEY (`id`), KEY
`FK_orderdetail_1` (`orders_id`), KEY `FK_orderdetail_2` (`items_id`),
CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders`
(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_orderdetail_2`
FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON
UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
3. Manually insert the data into the two new tables
4. Query the results according to the requirements
5. in the light of sql Statement can be written resultMap as well as mapper.xml The document is ready
According to the analysis, we can know that , In front of one-to-one, we have carried out the order and user association query , And also compiled resultMap, Then it can be used extends Inherits what has been written resultMap, Then write a new one , and java The inheritance is similar
<!-- Define order Association, query users and order details ResultMap--> <resultMap id="findOrderUserAndOrderDetail"
type="orders" extends="OrderUserResultMap"> <!-- Because it inherits the order related query user's resultMap definition
So there's no need to define these two parts , Just define the order details --> <!-- Define order details Multiple details are found in an order Association , To use collection Mapping
collection： For association query, multiple records are mapped to the collection object property： Mapping Association queries to multiple records to Orders Which property of the class
ofType： Specify map to list Set corresponding to pojo Types of --> <collection property="orderdetails"
ofType="Orderdetail"> <!--column: Unique identity of the query column , from sql Statement uses an alias , This is to distinguish it from the user field
property: corresponding pojo Which field of --> <id column="orderdetail_id" property="id"/> <result
column="orders_id" property="ordersId"/> <result column="items_id"
property="itemsId"/> <result column="items_num" property="itemsNum"/>
</collection> </resultMap> <select id="findOrderUserAndOrderDetail"
resultMap="findOrderUserAndOrderDetail"> 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 </select>
6. to write mapper Interface
7. Write test class
8. View test results
Let's see the details
<> One to many summary
It can be seen that , use resultMap You can easily map the associated query data to a list In the collection , You only need to add a collection of corresponding types to the mapping class , Then generate getter and setter method , And then in the resultMap Configure query fields and pojo Class
<> Many to many
With the above data table, we have a one to many query basis , Then we can implement a many to many query
1. Realize a query user at the same time query the user to buy goods , Through the analysis of the above table, we can know that there is no connection between users and products , But there is a connection between the user and the order , The order is linked to the order details , The order details are then linked to the product , So is there an indirect connection between users and products ? Then can a user buy a variety of goods , Can a product be purchased by multiple users , So there is a many to many relationship between them
2. Demand is to query the user and then query the product , Then the final query information can be mapped to the user table .
3. And then in the user Class , In this way, the order attribute can be mapped to this attribute （ The order can be associated with the order details ）
4. Then add the order details attribute to the order , In this way, the order details can be mapped to this attribute （ Only when you have the order details can you associate the goods ）
5. Finally, add product attributes to the order details , Map the product information to this attribute
6. It can be seen from the above analysis , They are linked one by one ,4 The two classes depend on each other to form a nested relationship , Finally, the query result is also in line with the nested relationship in the figure below
7. to write sql Query statement
8. to write mapper.xml file
Although it looks very long , But it's not hard to understand , The analysis of graph , The outermost layer is the final mapping result , Then deal with the relationship between tables and decide to use association still collection, Look at the connections between tables （ Foreign key ） The corresponding field name and its pojo Corresponding property name , As long as you have a unique logo , Ordinary fields are simple
<!-- Define query users and their purchase information resultMap--> <resultMap id="findUserAndItems" type="user">
<!-- User information Unique identity in the query column , Number of orders user_id The foreign key refers to the user id --> <id column="user_id"
property="id"/> <result column="username" property="username"/> <result
column="sex" property="sex"/> <result column="address" property="address"/>
<!-- Order information One user corresponds to multiple orders Add order attributes to users --> <collection property="orderslist"
ofType="Orders"> <!-- The field name of the order column found is id, corresponding Orders So are the attributes of id, There is no way to query users id
So there are no duplicate field names , No need to set an alias --> <id column="id" property="id"/> <result column="user_id"
property="userId"/> <result column="number" property="number"/> <result
column="createtime" property="createtime"/> <result column="note"
property="note"/> <!-- Configure order details here Add order detail attribute to order Because an order corresponds to multiple details , Therefore, the nested form is adopted -->
<collection property="orderdetails" ofType="Orderdetail">
<!-- The query column uses an alias orderdetail_id, Corresponding set pojo The field of the class is id It's to differentiate orders id Field name --> <id
column="orderdetail_id" property="id"/> <result column="items_id"
property="itemsId"/> <result column="items_num" property="itemsNum"/> <result
column="orders_id" property="ordersId"/> <!-- Product information , Because an order detail corresponds to a commodity ,
So add the commodity attribute to the order details So it's nested in the order details , And the relationship between them is one-to-one , So use it association--> <association
property="items" javaType="items"> <!-- Unique identity of the query column , In order details items_id Our foreign key is commodity id--> <id
column="items_id" property="id"/> <result column="items_name" property="name"/>
<result column="items_detail" property="detail"/> <result column="items_price"
property="price"/> </association> </collection> </collection> </resultMap>
<!-- Query the information of users and products purchased by users --> <select id="findUserAndItems"
resultMap="findUserAndItems"> SELECT orders.*, user.username, user.sex,
user.address, orderdetail.id orderdetail_id, orderdetail.items_id,
orderdetail.items_num, orderdetail.orders_id, items.name items_name,
items.detail items_detail, items.price items_price FROM orders, user,
orderdetail, items WHERE orders.user_id = user.id AND
orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id </select>
9. to write mapper Interface
10. Test class
11. View results
Expand information layer by layer , Do we find that it conforms to the inclusion relation above , It's amazing , In this way, many interesting functions can be realized
1. Use when there are no special requirements resultTypt
2. use resultMap The corresponding relationship should be determined , use association still collection
3. be careful pojo Dependencies between classes
4. From this we can know mybatis than hibernate Too much flexibility
This is where the advanced mapping is written , After all, it's my study notes , It may not be comprehensive , But I personally feel that I understand this mapping very well . Ha ha, it's amazing