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:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
User.java:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
address.java:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
t_uesr.sql:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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_address.sql:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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_user_address.sql:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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');
懒加载:
fetchType="lazy"
userDaoTest:
查询结果:
-------------------------------------------
新人只求记录学习生活!
如果觉得这篇文章对你有小小的帮助的话,记得在右下角点个“推荐”哦,博主在此感谢!