0. Data Preparation
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) NOT NULL,
`birthday` datetime default NULL,
`sex` varchar(10) default NULL,
`address` varchar(256) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user` */
insert into `user`(`id`,`username`,`birthday`,`sex`,`address`) values
(41,'W','2019-05-27 17:47:08','M','Beijing'),
(42,'E','2019-03-02 15:09:37','F','Shanghai'),
(43,'Li','2019-03-04 11:34:34','F','Sydney'),
(45,'R','2019-03-04 12:04:06','M','Melbourne'),
(46,'T','2018-09-07 17:37:26','M','SF'),(48,'BB','2019-03-08 11:44:00','F','LA');
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL,
`ordertime` datetime DEFAULT NULL,
`money` double DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `orders` */
insert into `orders`(`id`,`uid`,`ordertime`,`money`) values
(1,41,'2019-05-20 02:58:02',999.5),
(2,45,'2019-02-14 07:58:00',1399),
(3,41,'2019-06-01 21:00:02',1666);
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(11) NOT NULL,
`role_name` varchar(30) default NULL,
`role_desc` varchar(60) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `role` */
insert into `role`(`ID`,`role_name`,`role_desc`) values
(1,'dean',NULL),
(2,'president',NULL),
(3,'headmaster',NULL);
/*Table structure for table `user_role` */
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`uid` int(11) NOT NULL,
`rid` int(11) NOT NULL,
PRIMARY KEY (`uid`,`rid`),
KEY `FK_Reference_10` (`rid`),
CONSTRAINT `FK_Reference_10` FOREIGN KEY (`rid`) REFERENCES `role` (`id`),
CONSTRAINT `FK_Reference_9` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user_role` */
insert into `user_role`(`uid`,`rid`) values (41,1),(45,1),(41,2); public class User implements Serializable {
private Integer id;
private String username;
private String birthday;
private String sex;
private String address;
private List<Orders> list; // one user can have multiple orders
}
public class Orders implements Serializable {
private Integer id;
private String ordertime;
private Double money;
private Integer uid; // foreign key
private User user; // one order belongs to one user
}
# 1. mybatis looks at the problem from a different perspective
// a. from the users table: one-to-many (one user with multiple orders)
// b. from the perspective of the orders table: one-to-one (an order can belong to only one user)
# 2. mybatis solves the problem differently
// a. sql uses foreign keys to create table relationships
// b. mybatis uses attributes 1. MyBatis multi-table query (multi-table join query)
1.1 One-to-One
<resultMap id="myorder" type="orders" autoMapping="true">
<!--
id tag: primary key field mapping (must be written)
result tag: non-primary key field mapping (the same name can not be written,
recommended to write)
-->
<!--
Leftmost principle: If the same name field exists in the result set,
the one on the left is used by default.
-->
<id property="id" column="id"/>
<result property="ordertime" column="ordertime"/>
<!--
The focus is on the mapping of the orders.user property to a section of the
result set (the users table).
1. one-to-one: association (used)
2. one-to-many: collection
# The association tag
1. properties : property name
2. javaType : the type of this property
Subtab : sets the mapping between users and result sets.
Note the leftmost principle
-->
<association property="user" javaType="user" autoMapping="true">
<id property="id" column="uid"/>
<result property="username" column="username"/>
</association>
</resultMap>
<select id="findOrderByIdWithUser" resultMap="myorder">
select u.*,o.*
from orders o inner join user u on o.uid = u.id
where o.id = #{id}
</select> 1.2 One-to-Many
<resultMap id="myuser" type="user" autoMapping="true">
<id property="id" column="id"/>
<!--
Notice: user.list<Orders> list property maps to a part of the result set.
# The collection tag
1. property : specifies the property name
2. ofType : specifies the element type of the collection
-->
<collection property="list" ofType="orders" autoMapping="true">
<id property="id" column="oid"/>
</collection>
</resultMap>
<select id="findUserByIdWithOrders" resultMap="myuser">
select u.*,o.id as oid,o.uid,o.ordertime,o.money
from user u
inner join orders o on u.id = o.uid
where u.id = #{uid}
</select>
1.3 Many-to-Many
Given the role_id, query the role detail and the corresponding users.
<resultMap id="myrole" type="role" autoMapping="true">
<id property="id" column="id"/>
<result property="rolesc" column="role_desc"/>
<collection property="list" ofType="user" autoMapping="true">
<id property="id" column="uid"/>
</collection>
</resultMap>
<select id="findRoleByIdWithUsers" resultMap="myrole">
select * from
role r
inner join user_role ur
inner join user u
on r.id = ur.rid and u.id = ur.uid
where r.id = #{rid}
</select> 1.4 Summary
2. MyBatis Nested Queries (Cascading Queries)
2.1 What is nested query
Nested query is the original multi-table union query statement is split into multiple single-table query, and then use mybatis syntax nested together.
For example:
* Requirement: query an order and at the same time query the user to whom the order belongs.
* 1. Associative query:
select * from orders o inner join user u on o.uid = u.id where o.id = 1;
* Disadvantages:
The sql statement is difficult to write
If there is a large amount of data in the table, the number of Cartesian
products multiplies, which may cause memory overflow.
* 2. nested queries:
a. Query the orders table based on order ids
select * from orders where id = 1;
// Find information about the order with order id = 1 (uid = 41)
// the result is mapped to an orders object
b. Then query the user table based on the uid (foreign key) in the orders table
select * from user where id = orders.uid; // result mapped to orders.user object.
// The result is mapped to orders.user
c. Finally, by the mybatis framework for nested combinations
Difference with subqueries
1. Nested query executes two sql sentences: one for order information and
one for user information.
2. sub-query execution of a sentence : can only find the order number for
the 1 belongs to the user information
select * from user where id = (select uid from orders where id = 1)
* Advantages:
Simple to write sql statement.
No multiple table associations, no Cartesian product, especially in the case of
a large number of tables, more advantageous.
2.2 One-to-one nested query
Requirement: query an order, and at the same time query the order belongs to the user
SQL we need:
select * from orders where id = 1;
select * from user where id = 41; <!--
# Nested queries focus on.
0. Purpose
select * from user where id = ?
Mapping to the orders.user property
1. Write a statement to query the user table.
UserMapper.findUserById -> UserMapper.xml
2. Nest it here
Two attributes of the association tag
a. column : the name or alias of the column of the database table
for the condition (result set field as a parameter of the query
statement)
b. select : the name of the column or alias of the database table
for which the second sql execution is called.
Note: Non-associative fields can use auto-mapping,
associative field mapping (<result property="uid" column="uid"/>).
Omitting or not will give different results. Omission is not
recommended.
Note: The column property of association is the foreign key column uid of
the table orders of the current class Orders.
-->
<resultMap id="cascadeorder" type="orders" autoMapping="true">
<id property="id" column="id"/>
<result property="uid" column="uid"/>
<association property="user" javaType="user" autoMapping="true"
column="uid"
select="com.demo.mapper.UserMapper.findUserById">
<id property="id" column="uid"/>
<result property="username" column="username"/>
</association>
</resultMap>
<select id="findOrderByIdWithUser3" resultMap="cascadeorder">
select * from orders where id = #{id}
</select> <select id="findUserById" resultType="user">
select * from user where id = #{id}
</select> 2.3 One-to-Many Nested Queries
Requirement: query a user, and at the same time query the user has orders
SQL:
SELECT * FROM USER WHERE id = 41;
SELECT * FROM orders WHERE uid = 41; <resultMap id="cascadeuser" type="user" autoMapping="true">
<id property="id" column="id"/>
<collection property="list" ofType="orders" autoMapping="true"
column="id"
select="com.wxl52d41.demo.mapper.OrdersMapper.findOrdersByUid">
</collection>
</resultMap>
<select id="findUserByIdWithOrders2" resultMap="cascadeuser">
select * from user where user.id = #{uid}
</select>
<select id="findOrdersByUid" resultType="orders">
select * from orders where uid = #{uid}
</select> 2.4 Summary
The resultMap element has two attributes
id: the unique tag of the resultMap, referenced by the select element is used.
type: the final object of which class. Can be an alias or fully qualified name. The resultType is the same as the previous resultType, except that it shows that it is a custom rule mapped to an object of a certain class.
The resultMap element has several child elements
id: configures the relationship between the primary key column and the attributes.
result: configures the relationship between non-primary key columns and attributes.
association: configure many-to-one, one-to-one association field column and attribute relationship, corresponding to an object attribute
collection: configures one-to-many or many-to-many association field and attribute relationships, corresponding to a collection attribute.
Attribute Description
property: The name of the property to be mapped to the JavaBean.
javaType: The type of the property, a full class name, or a type alias. If you match a JavaBean, then MyBatis usually detects it on its own.
column: The column name or alias of the data table.
jdbcType: The type of the column in the database table. This attribute is only useful for columns that are allowed to be null when inserting, updating, or deleting; it is required by JDBC, but not by MyBatis. The value is the value of the JDBCType enumeration.
select: property name that needs to be mapped to a JavaBean
fetchType: association, collection attribute, which query to use the value of the query attribute, the full name of the namespace + id to be specified.
typeHandler: use this attribute can override the type handler, javaType, jdbcType conversion between each other. Generally can be omitted, will detect the use of what type of typeHandler for processing
ofType: attribute of collection, specify the type of the elements in the collection (i.e., generic type)
Notice:
- When cascading queries, for consistent JavaBean properties and database table fields, the id, result elements can be omitted because they can be mapped automatically.
- In resultMap manual mapping, an associated column may correspond to more than one property, it is recommended that they are all manually mapped, otherwise it will affect the query results.
- javaType, jdbcType, typeHandler three attributes can be omitted if omitted.
Comparison of cascade and multi-table join queries and their selection
Cascade queries | Multi-table join queries | |
SQL statement count | many | one |
performances | low | high |
lazy loading | support | not support |
flexibility | better | worse |
SQL complexity | simpler | more complicated |
selection basis | simple and flexible | high performance |
0 Comments