Mybatis学习03:多表查询和延迟加载

本文介绍MyBatis框架中实现多表查询的方法,包括一对多、多对一和多对多关系的数据查询,并详细讲解如何通过配置实现延迟加载,减少数据库查询次数,提高应用程序性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


多表查询分为三种情况 一对多, 多对一, 多对多

  • 一对多多对一的情况类似于用户User和账户Account的关系: 一个用户User可以对应多个账户Account,而一个账户Account只能对应一个用户User.
  • 多对多的情况类似于用户User和角色Role的关系: 一个用户User可以对应多个角色Role,一个角色Role也可以对应一个用户User.

使用sql连接查询

我们可以在sql语句中连接查询多个表,并通过配置<resultMap>将查询结果封装起来.

多对一查询

项目准备

  1. 创建数据库表usersaccounts如下:

    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);
    
  2. cn.maoritian.domian包下创建实体类UserAccount如下:

    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 + '}'; }
    }
    

实现多对一查询

  1. cn.maoritian.IAccountDao中定义查询方法findAll()

    package cn.maoritian.dao;
    
    public interface IAccountDao {
    
        // 查询所有账户及其对应的用户信息
        List<Account> findAll();
    }
    
  2. 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>
    

一对多查询

项目准备

创建数据库表和实体类的过程与上一节多对一查询项目准备的过程完全相同

实现一对多查询

  1. cn.maoritian.IUserDao中定义查询方法findAll()

    package cn.maoritian.dao;
    
    public interface IUserDao {
    
     	// 查询所有用户及其对应的帐户信息
        List<User> findAll();
    }
    
  2. 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>
    

多对多查询

项目准备

  1. 创建数据库表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);
    
  2. cn.maoritian.domian包下创建实体类UserRole如下:

    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 + '\'' + '}'; }
    }
    

实现多对多查询

  1. 查询所有用户

    1. cn.maoritian.IUserDao中定义查询方法findAll()

      package cn.maoritian.dao;
      
      public interface IAccountDao {
      
          // 查询所有账户及其对应的用户信息
          List<Account> findAll();
      }
      
    2. 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>
      
  2. 查询所有角色

    1. cn.maoritian.IRoleDao中定义查询方法findAll()

      package cn.maoritian.dao;
      
      public interface IRoleDao {
      
          // 查询所有账户及其对应的用户信息
          List<Role> findAll();
      }
      
    2. 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>标签,可以实现延迟加载.

  1. 定义主表查询和附表查询的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);
    }
    
  2. 实现两个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>
    
  3. 配置输出映射:

    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>标签,可以实现延迟加载.

  1. 定义主表查询和附表查询的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);
    }
    
  2. 实现两个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>
    
  3. 配置输出映射:

    <!-- 定义将查询结果封装为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>的顺序

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值