Mybatis学习03:多表查询和延迟加载
多表查询分为三种情况
一对多
,
多对一
,
多对多
一对多
和多对一
的情况类似于用户User
和账户Account
的关系: 一个用户User
可以对应多个账户Account
,而一个账户Account
只能对应一个用户User
.多对多
的情况类似于用户User
和角色Role
的关系: 一个用户User
可以对应多个角色Role
,一个角色Role
也可以对应一个用户User
.
使用sql连接查询
我们可以在sql语句中连接查询多个表,并通过配置<resultMap>
将查询结果封装起来.
多对一查询
项目准备
-
创建数据库表
users
和accounts
如下:DROP TABLE IF EXISTS users; create table users( id int auto_increment primary key, username varchar(32) not null comment '用户名称', birthday datetime null comment '生日', sex char null comment '性别', address varchar(256) null comment '地址' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into users(id,username,birthday,sex,address) values (41,'老王','2018-02-27 17:47:08','男','北京'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(48,'小马宝莉','2018-03-08 11:44:00','女','北京修正');
DROP TABLE IF EXISTS accounts; CREATE TABLE accounts ( id int(11) not null primary key, money double default null comment '金额', uid int(11) default null comment '用户编号', FOREIGN key (uid) REFERENCES users (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into accounts(id,uid,money) values (1,46,1000),(2,45,1000),(3,46,2000);
-
在
cn.maoritian.domian
包下创建实体类User
和Account
如下:public class User implements Serializable { private Integer id; private String username; private String address; private String sex; private Date birthday; private List<Account> accounts; // 一对多关系映射:主表实体应该包含从表实体的集合引用 public List<Account> getAccounts() {return accounts; } public void setAccounts(List<Account> accounts) {this.accounts = accounts; } public Integer getid() {return id; } public void setid(Integer id) {this.id = id; } public String getUsername() {return username; } public void setUsername(String username) {this.username = username; } public String getAddress() {return address; } public void setAddress(String address) {this.address = address; } public String getSex() {return sex; } public void setSex(String sex) {this.sex = sex; } public Date getBirthday() {return birthday; } public void setBirthday(Date birthday) {this.birthday = birthday; } @Override public String toString() {return "User{" + "id=" + id + ", username='" + username + '\'' + ", address='" + address + '\'' + ", sex='" + sex + '\'' + ", birthday=" + birthday + ", accounts=" + accounts + '}'; } }
public class Account implements Serializable { private Integer id; private Integer uid; private Double money; private User user; // 多对一关系映射:从表实体应该包含一个主表实体的对象引用 public User getUser() {return user; } public void setUser(User user) {this.user = user; } public Integer getid() {return id; } public void setid(Integer id) {this.id = id; } public Integer getuid() {return uid; } public void setuid(Integer uid) {this.uid = uid; } public Double getmoney() {return money; } public void setmoney(Double money) {this.money = money; } @Override public String toString() {return "Account{" + "id=" + id + ", uid=" + uid + ", money=" + money + ", user=" + user + '}'; } }
实现多对一查询
-
在
cn.maoritian.IAccountDao
中定义查询方法findAll()
package cn.maoritian.dao; public interface IAccountDao { // 查询所有账户及其对应的用户信息 List<Account> findAll(); }
-
在
resources/cn/maoritian/dao/IAccountDaoImpl.xml
中配置相应的sql语句以及查询结果映射如下:<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.maoritian.dao.IAccountDao"> <!-- 定义将查询结果封装为Account对象的规则 --> <resultMap id="accountMap" type="cn.maoritian.domain.Account"> <id property="id" column="aid"/> <result property="uid" column="uid"/> <result property="money" column="money"/> <!-- association标签定义pojo对象的成员pojo对象 --> <association property="user" column="uid" javaType="cn.maoritian.domain.User"> <id property="id" column="id"/> <result property="username" column="username"/> <result property="address" column="address"/> <result property="sex" column="sex"/> <result property="birthday" column="birthday"/> </association> </resultMap> <!-- findAll()方法的sql语句 --> <select id="findAll" resultMap="accountMap"> select accounts.id as aid,accounts.uid,accounts.money,users.* from accounts inner join users on accounts.uid = users.id; </select> </mapper>
一对多查询
项目准备
创建数据库表和实体类的过程与上一节多对一查询项目准备的过程完全相同
实现一对多查询
-
在
cn.maoritian.IUserDao
中定义查询方法findAll()
package cn.maoritian.dao; public interface IUserDao { // 查询所有用户及其对应的帐户信息 List<User> findAll(); }
-
在
resources/cn/maoritian/dao/IUserDao.xml
中配置相应的sql语句以及查询结果映射如下:<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.maoritian.dao.IUserDao"> <!-- 定义将查询结果封装为User对象的规则--> <resultMap id="userMap" type="cn.maoritian.domain.User"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="address" column="address"></result> <result property="sex" column="sex"></result> <result property="birthday" column="birthday"></result> <!-- collection标签定义pojo对象的成员pojo对象集合 --> <collection property="accounts" ofType="cn.maoritian.domain.Account"> <id property="id" column="aid"></id> <result property="uid" column="uid"></result> <result property="money" column="money"></result> </collection> </resultMap> <!-- findAll()方法的sql语句 --> <select id="findAll" resultMap="userMap"> select users.*, accounts.id as aid, accounts.uid, accounts.money from users left join accounts on users.id = accounts.uid </select> </mapper>
多对多查询
项目准备
-
创建数据库表
users
,roles
和中间表user_role
DROP TABLE IF EXISTS users; create table users( id int auto_increment primary key, username varchar(32) not null comment '用户名称', birthday datetime null comment '生日', sex char null comment '性别', address varchar(256) null comment '地址' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into users(id,username,birthday,sex,address) values (41,'老王','2018-02-27 17:47:08','男','北京'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(48,'小马宝莉','2018-03-08 11:44:00','女','北京修正');
DROP TABLE IF EXISTS roles; CREATE TABLE roles ( id int(11) not null primary key COMMENT '编号', ROLE_NAME varchar(30) default null COMMENT '角色名称', ROLE_DESC varchar(60) default null COMMENT '角色描述', ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into roles(id,ROLE_NAME,ROLE_DESC) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');
DROP TABLE IF EXISTS user_role; CREATE TABLE user_role ( uid int(11) not null COMMENT '用户编号', rid int(11) not null COMMENT '角色编号', primary key (uid,rid), key FK_Reference_10 (rid), CONSTRAINT FK_Reference_10 foreign key (rid) references roles (id), CONSTRAINT FK_Reference_9 foreign key (uid) references users (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_role(uid,rid) values (41,1),(45,1),(41,2);
-
在
cn.maoritian.domian
包下创建实体类User
和Role
如下:package cn.maoritian.domain; public class User implements Serializable { private Integer id; private String username; private String address; private String sex; private Date birthday; private List<Role> roles; // 多对多关系映射 public Integer getId() {return id; } public void setId(Integer id) {this.id = id; } public String getUsername() {return username; } public void setUsername(String username) {this.username = username; } public String getAddress() {return address; } public void setAddress(String address) {this.address = address; } public String getSex() {return sex; } public void setSex(String sex) {this.sex = sex; } public Date getBirthday() {return birthday; } public void setBirthday(Date birthday) {this.birthday = birthday; } public List<Role> getRoles() {return roles; } public void setRoles(List<Role> roles) {this.roles = roles; } @Override public String toString() {return "User{" + "id=" + id + ", username='" + username + '\'' + ", address='" + address + '\'' + ", sex='" + sex + '\'' + ", birthday=" + birthday + ", roles=" + roles + '}'; } }
package cn.maoritian.domain; public class Role implements Serializable { private Integer roleId; private String roleName; private String roleDesc; private List<User> users; // 多对多关系映射 public List<User> getUsers() {return users; } public void setUsers(List<User> users) {this.users = users; } public Integer getRoleId() {return roleId; } public void setRoleId(Integer roleId) {this.roleId = roleId; } public String getRoleName() {return roleName; } public void setRoleName(String roleName) {this.roleName = roleName; } public String getRoleDesc() {return roleDesc; } public void setRoleDesc(String roleDesc) {this.roleDesc = roleDesc; } @Override public String toString() {return "Role{" + "roleId=" + roleId + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } }
实现多对多查询
-
查询所有用户
-
在
cn.maoritian.IUserDao
中定义查询方法findAll()
package cn.maoritian.dao; public interface IAccountDao { // 查询所有账户及其对应的用户信息 List<Account> findAll(); }
-
在
resources/cn/maoritian/dao/IAccountDaoImpl.xml
中配置相应的sql语句以及查询结果映射如下:<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.maoritian.dao.IUserDao"> <!-- 定义将查询结果封装为User对象的规则--> <resultMap id="userMap" type="cn.maoritian.domain.User"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="address" column="address"></result> <result property="sex" column="sex"></result> <result property="birthday" column="birthday"></result> <!-- collection标签定义pojo对象的成员pojo对象集合 --> <collection property="roles" ofType="cn.maoritian.domain.Role"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> </collection> </resultMap> <!-- 查询所有 --> <select id="findAll" resultMap="userMap"> select users.*,roles.id as rid,roles.role_name,roles.role_desc from user_role left join users on user_role.uid = users.id left join roles on user_role.rid = roles.id </select> </mapper>
-
-
查询所有角色
-
在
cn.maoritian.IRoleDao
中定义查询方法findAll()
package cn.maoritian.dao; public interface IRoleDao { // 查询所有账户及其对应的用户信息 List<Role> findAll(); }
-
在
resources/cn/maoritian/dao/IRoleDao.xml
中配置相应的sql语句以及查询结果映射如下:<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.maoritian.dao.IRoleDao"> <!-- 定义将查询结果封装为Role对象的规则--> <resultMap id="roleMap" type="cn.maoritian.domain.Role"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> <collection property="users" ofType="cn.maoritian.domain.User"> <id column="id" property="id"></id> <result column="username" property="username"></result> <result column="address" property="address"></result> <result column="sex" property="sex"></result> <result column="birthday" property="birthday"></result> </collection> </resultMap> <!--查询所有--> <select id="findAll" resultMap="roleMap"> select users.*,roles.id as rid,roles.role_name,roles.role_desc from user_role left join roles on user_role.rid = roles.id left join users on user_role.uid = users.id </select> </mapper>
-
使用延迟加载
通过sql语句连接查询,我们会把一个对象的所有关联对象都查询出来,这属于立即加载
的方式.Mybatis也支持延迟加载
,允许在真正使用到关联对象时才发起查询.通过使用延迟加载
,我们可以将原sql语句分解为主表查询和副表查询两个sql语句.
开启Mybatis延迟加载
通过在Mybatis主配置文件SqlMapConfig.xml
的<settings>
标签下添加设置如下,已开启延迟加载:
<settings>
<!-- 开启Mybatis延迟加载的全局开关 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 将所有加载项均设为延迟加载,默认值即为false -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
实现延迟加载
下面我们将所有的sql连接查询拆分为主查询和副查询两个sql语句,并通过<resultMap>
将两个查询方法连接起来,实现延迟加载.
多对一查询的延迟加载
通过拆分sql语句以及修改<resultMap>
下的<association>
标签,可以实现延迟加载.
-
定义主表查询和附表查询的DAO层接口方法
在
cn.maoritian.IAccountDao
中定义查询方法findAll()
package cn.maoritian.dao; public interface IAccountDao { // 查询所有账户及其对应的用户信息 List<Account> findAll(); }
在
cn.maoritian.IUserDao
中定义查询方法findUserById()
package cn.maoritian.dao; public interface IUserDao { // 根据id查询用户 User findUserById(Integer id); }
-
实现两个DAO层方法:
在
resources/cn/maoritian/dao/IAccountDaoImpl.xml
中配置实现findAll()
方法的sql语句:<!-- findAll()方法的sql语句 --> <select id="findAll" resultMap="accountMap"> select * from accounts </select>
在
resources/cn/maoritian/dao/IUserDaoImpl.xml
中配置实现findUserById()
方法的sql语句:<!-- 根据id查询用户 --> <select id="findUserById" parameterType="int" resultType="cn.maoritian.domain.User"> select * from user where id = #{id} </select>
-
配置输出映射:
在
resources/cn/maoritian/dao/IAccountDaoImpl.xml
中配置输出映射如下:<!-- 定义将查询结果封装为Account对象的规则 --> <resultMap id="accountUserMap" type="cn.maoritian.domain.Account"> <id property="id" column="id"/> <result property="uid" column="uid"/> <result property="money" column="money"/> <!-- select属性指定副查询调用的方法,column指定传给select方法的参数 --> <association property="user" javaType="cn.maoritian.domain.User" column="uid" select="cn.maoritian.dao.IUserDao.findById"/> </resultMap>
一对多,多对多查询的延迟加载
通过拆分sql语句以及修改<resultMap>
下的<collection>
标签,可以实现延迟加载.
-
定义主表查询和附表查询的DAO层接口方法
在
cn.maoritian.IUserDao
中定义查询方法findAll()
package cn.maoritian.dao; public interface IUserDao { // 查询所有用户及其对应的帐户信息 List<User> findAll(); }
在
cn.maoritian.IAccountDao
中定义查询方法findUserById()
package cn.maoritian.dao; public interface IAccountDao { // 根据uid查询账户 Account findAccountByUid(Integer uid); }
-
实现两个DAO层方法:
在
resources/cn/maoritian/dao/IUserDaoImpl.xml
中配置实现findAll()
方法的sql语句:<!-- findAll()方法的sql语句 --> <select id="findAll" resultMap="userMap"> select * from users </select>
在
resources/cn/maoritian/dao/IAccountDao.xml
中配置实现findAccountById()
方法的sql语句:<!-- 根据uid查询账户 --> <select id="findAccountByUid" parameterType="int" resultType="cn.maoritian.domain.Account"> select * from user where id = #{uid} </select>
-
配置输出映射:
<!-- 定义将查询结果封装为User对象的规则--> <resultMap id="userMap" type="cn.maoritian.domain.User"> <id property="id" column="id"/> <result property="username" column="username"/> <result property="address" column="address"/> <result property="sex" column="sex"/> <result property="birthday" column="birthday"/> <!-- select属性指定副查询调用的方法,column指定传给select方法的参数 --> <collection property="accounts" ofType="cn.maoritian.domain.Account" select="cn.maoritian.cao.IAccountDao.findAccountByUid" column="id"/> </resultMap>
注意的坑
resultMap
标签内子标签的顺序问题
Mybatis报错如下:
The content of element type "resultMap" must match
"(constructor?,id*,result*,association*,collection*,discriminator?)".
翻译为中文如下:
元素类型为 "resultMap" 的内容必须匹配
"(constructor?,id*,result*,association*,collection*,discriminator?)"
也就是说<resultMap>
标签内的子标签顺序必须严格遵循<id>
,<result>
,<association>
,<collection>
的顺序