mybatis 嵌套查询与懒加载

懒加载：对于页面有很多静态资源的情况下（比如网商购物页面），为了节省用户流量和提高页面性能，可以在用户浏览到当前资源的时候，再对资源进行请求和加载。

　　　　　　fetchType="lazy"

mybatis多对一嵌套查询：

嵌套查询的特点就是：利用一个查询的时候发送多条sql语句；
1+n条sql语句：1条主sql,还有多条分别查询语句：

例如：我想要查询用户的详细信息包括地址，（地址在另一张表，此时表被关系表管理），我就需要嵌套查询：

1.先根据id查询用户信息：

select * from t_user where id =5

2.再根据用户信息查询用户地址的关系表得到地址表中的id：

select address_id aid from t_user_address ua where user_id =5

3.根据关系表地址id拿到地址的详细信息：

select * from t_address where id =1
select * from t_address where id =2

代码原理：

t_address 地址信息表

t_address_user地址id和用户id关系表

t_user用户表

查询的sql和结果：

代码实现：

配置懒加载：mybatis-config.xml

 <settings>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

编写sql:UserDAO.xm 1 <resultMap id="mynesting" type="user">

 1 <resultMap id="mynesting" type="user">
 2         <id property="id" column="id"></id>
 3         <result property="name" column="name"></result>
 4         <result property="sal" column="sal"></result>
 5         <result property="birth" column="birthday"></result>
 6         <collection fetchType="lazy" property="addresses" ofType="Address" column="id" javaType="ArrayList"  select="selectAddressId">
 7         </collection>
 8     </resultMap>
 9 
10     <resultMap id="myua" type="ua">
11         <id property="addressId" column="aid"></id>
12         <collection fetchType="lazy" property="addresses" ofType="Address" column="aid" javaType="ArrayList"  select="selectAddress">
13         </collection>
14     </resultMap>
15 
16     <select id="ById" resultMap="mynesting">
17       select * from t_user where id =#{id}
18     </select>
19 
20     <select id="selectAddressId" resultMap="myua" >
21         select address_id aid from t_user_address ua where user_id =#{id}
22     </select>
23 
24     <select id="selectAddress" resultType="address">
25       select * from t_address where id =#{id}
26     </select>

UserDAO:

 1 package com.etc.dao;
 2 
 3 import com.etc.entity.User;
 4 
 5 import java.util.List;
 6 
 7 public interface UserDao {
 8  
 9     List<User> ById(int id);
10 
11 
12 }

UA.java:

 1 package com.etc.entity;
 2 
 3 import java.util.List;
 4 
 5 public class UA {
 6     private int userId;
 7     private int addressId;
 8     private List<Address> addresses;
 9 
10     public List<Address> getAddresses() {
11         return addresses;
12     }
13 
14     public void setAddresses(List<Address> addresses) {
15         this.addresses = addresses;
16     }
17 
18     public int getUserId() {
19         return userId;
20     }
21 
22     public void setUserId(int userId) {
23         this.userId = userId;
24     }
25 
26     public int getAddressId() {
27         return addressId;
28     }
29 
30     public void setAddressId(int addressId) {
31         this.addressId = addressId;
32     }
33 
34     @Override
35     public String toString() {
36         return ""+addresses;
37     }
38 }

UA.java

User.java:

 1 package com.etc.entity;
 2 
 3 import java.util.Date;
 4 import java.util.List;
 5 
 6 public class User {
 7     private int id;
 8     private String name;
 9     private double sal;
10     private Date birth;
11     private List<Address> addresses;
12 
13     public User() {
14     }
15 
16     public User(int id, String name, double sal, Date birth, List<Address> addresses) {
17         this.id = id;
18         this.name = name;
19         this.sal = sal;
20         this.birth = birth;
21         this.addresses = addresses;
22     }
23 
24     public int getId() {
25         return id;
26     }
27 
28     public void setId(int id) {
29         this.id = id;
30     }
31 
32     public String getName() {
33         return name;
34     }
35 
36     public void setName(String name) {
37         this.name = name;
38     }
39 
40     public double getSal() {
41         return sal;
42     }
43 
44     public void setSal(double sal) {
45         this.sal = sal;
46     }
47 
48     public Date getBirth() {
49         return birth;
50     }
51 
52     public void setBirth(Date birth) {
53         this.birth = birth;
54     }
55 
56     public List<Address> getAddresses() {
57         return addresses;
58     }
59 
60     public void setAddresses(List<Address> addresses) {
61         this.addresses = addresses;
62     }
63 
64     @Override
65     public String toString() {
66         return "User{" +
67                 "id=" + id +
68                 ", name='" + name + '\'' +
69                 ", sal=" + sal +
70                 ", birth=" + birth +
71                 ", addresses=" + addresses +
72                 '}';
73     }
74 }

User.java

address.java:

 1 package com.etc.entity;
 2 
 3 public class Address {
 4     private int id;
 5     private String name;
 6     private User user;
 7 
 8     public int getId() {
 9         return id;
10     }
11 
12     public void setId(int id) {
13         this.id = id;
14     }
15 
16     public String getName() {
17         return name;
18     }
19 
20     public void setName(String name) {
21         this.name = name;
22     }
23 
24     public User getUser() {
25         return user;
26     }
27 
28     public void setUser(User user) {
29         this.user = user;
30     }
31 
32     @Override
33     public String toString() {
34         return "Address{" +
35                 "id=" + id +
36                 ", name='" + name + '\'' +
37                 ", user=" + user +
38                 '}';
39     }
40 }

address.java

t_uesr.sql:

 1 /*
 2 Navicat MySQL Data Transfer
 3 
 4 Source Server         : Test
 5 Source Server Version : 50562
 6 Source Host           : localhost:3310
 7 Source Database       : mybatis
 8 
 9 Target Server Type    : MYSQL
10 Target Server Version : 50562
11 File Encoding         : 65001
12 
13 Date: 2019-07-25 11:43:23
14 */
15 
16 SET FOREIGN_KEY_CHECKS=0;
17 
18 -- ----------------------------
19 -- Table structure for t_user
20 -- ----------------------------
21 DROP TABLE IF EXISTS `t_user`;
22 CREATE TABLE `t_user` (
23   `id` int(11) NOT NULL AUTO_INCREMENT,
24   `name` varchar(255) DEFAULT NULL,
25   `sal` float(255,0) DEFAULT NULL,
26   `birthday` date DEFAULT NULL,
27   PRIMARY KEY (`id`)
28 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
29 
30 -- ----------------------------
31 -- Records of t_user
32 -- ----------------------------
33 INSERT INTO `t_user` VALUES ('5', 'asda', '1000', '2019-07-22');
34 INSERT INTO `t_user` VALUES ('6', 'asda', '1000', '2019-07-22');
35 INSERT INTO `t_user` VALUES ('7', 'asdasdaa', '1000', '2019-07-22');
36 INSERT INTO `t_user` VALUES ('8', 'asdasdaa', '1000', '2019-07-22');
37 INSERT INTO `t_user` VALUES ('9', 'asdasdaa', '1000', '2019-07-22');
38 INSERT INTO `t_user` VALUES ('10', 'asdasdaa', '1000', '2019-07-22');
39 INSERT INTO `t_user` VALUES ('11', 'asdasdaa', '1000', '2019-07-22');
40 INSERT INTO `t_user` VALUES ('12', 'asdasdaa', '1000', '2019-07-22');
41 INSERT INTO `t_user` VALUES ('13', 'asdasdaa', '1000', '2019-07-23');
42 INSERT INTO `t_user` VALUES ('14', 'asdasdaa', '1000', '2019-07-23');

t_user.sql

t_address.sql:

 1 /*
 2 Navicat MySQL Data Transfer
 3 
 4 Source Server         : Test
 5 Source Server Version : 50562
 6 Source Host           : localhost:3310
 7 Source Database       : mybatis
 8 
 9 Target Server Type    : MYSQL
10 Target Server Version : 50562
11 File Encoding         : 65001
12 
13 Date: 2019-07-25 11:43:30
14 */
15 
16 SET FOREIGN_KEY_CHECKS=0;
17 
18 -- ----------------------------
19 -- Table structure for t_address
20 -- ----------------------------
21 DROP TABLE IF EXISTS `t_address`;
22 CREATE TABLE `t_address` (
23   `id` int(11) NOT NULL AUTO_INCREMENT,
24   `name` varchar(255) DEFAULT NULL,
25   PRIMARY KEY (`id`)
26 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
27 
28 -- ----------------------------
29 -- Records of t_address
30 -- ----------------------------
31 INSERT INTO `t_address` VALUES ('1', '这是地址');
32 INSERT INTO `t_address` VALUES ('2', '这是地址');
33 INSERT INTO `t_address` VALUES ('3', '7');
34 INSERT INTO `t_address` VALUES ('4', '8');
35 INSERT INTO `t_address` VALUES ('5', '9');
36 INSERT INTO `t_address` VALUES ('6', '10');

t_address.sql

t_user_address.sql:

 1 /*
 2 Navicat MySQL Data Transfer
 3 
 4 Source Server         : Test
 5 Source Server Version : 50562
 6 Source Host           : localhost:3310
 7 Source Database       : mybatis
 8 
 9 Target Server Type    : MYSQL
10 Target Server Version : 50562
11 File Encoding         : 65001
12 
13 Date: 2019-07-25 11:43:12
14 */
15 
16 SET FOREIGN_KEY_CHECKS=0;
17 
18 -- ----------------------------
19 -- Table structure for t_user_address
20 -- ----------------------------
21 DROP TABLE IF EXISTS `t_user_address`;
22 CREATE TABLE `t_user_address` (
23   `user_id` int(11) DEFAULT NULL,
24   `address_id` int(11) DEFAULT NULL,
25   UNIQUE KEY `address_unqiue` (`address_id`) USING BTREE
26 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
27 
28 -- ----------------------------
29 -- Records of t_user_address
30 -- ----------------------------
31 INSERT INTO `t_user_address` VALUES ('5', '1');
32 INSERT INTO `t_user_address` VALUES ('5', '2');
33 INSERT INTO `t_user_address` VALUES ('6', '3');
34 INSERT INTO `t_user_address` VALUES ('6', '4');
35 INSERT INTO `t_user_address` VALUES ('6', '5');

t_user_address.sql

懒加载：
fetchType="lazy"

userDaoTest：


查询结果：

posted @ 2019-07-25 12:07 刘认真阅读(1157) 评论(0) 编辑收藏举报

刷新页面返回顶部

刘认真

mybatis 嵌套查询与懒加载

公告