mybatis之关联查询

一、简单一对一级联查询

  创建两张表，假设一个老师对应一个班级

CREATE TABLE `teacher` (
  `t_id` int(11) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert  into `teacher`(`t_id`,`t_name`) values (1,'张三');
insert  into `teacher`(`t_id`,`t_name`) values (2,'李四');

CREATE TABLE `class` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(20) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert  into `class`(`c_id`,`c_name`,`teacher_id`) values (1,'Java',1);
insert  into `class`(`c_id`,`c_name`,`teacher_id`) values (2,'UI',2);

CREATE TABLE `student` (
  `s_id` int(11) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
insert  into `student`(`s_id`,`s_name`,`class_id`) values (1,'AA',1);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (2,'BB',1);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (3,'CC',1);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (4,'DD',2);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (5,'EE',2);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (6,'FF',2);

  创建实体
  Teacher 实体

public class Teacher {
    private int id;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

  Classes实体

public class Classes {
    private int id;
    private String name;
    private Teacher teacher;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "Classes{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", teacher=" + teacher +
                '}';
    }
}

ClassesMapper.xml 查询将出现两种方式

方式一：级联查询

<resultMap id="classResultMap" type="cn.hxzy.mybatis.entity.Classes">
    <id column="c_id" property="id"/>
    <result column="c_name" property="name"/>
    <result column="t_id" property="teacher.id"/>
    <result column="t_name" property="teacher.name"/>
</resultMap>

<select id="selectAll" resultMap="classResultMap">
    SELECT * FROM `class` c LEFT JOIN `teacher` t ON t.`t_id`=c.`teacher_id`
</select>

二、嵌套结果与嵌套查询

  方式二：嵌套结果，使用嵌套结果映射来处理重复的联合结果的子集封装联表查询的数据(去除重复的数据)

<resultMap id="classResultMap2" type="cn.hxzy.mybatis.entity.Classes">
    <id column="c_id" property="id"/>
    <result column="c_name" property="name"/>
    <association property="teacher" javaType="cn.hxzy.mybatis.entity.Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
    </association>
</resultMap>

<select id="selectAll" resultMap="classResultMap2">
    SELECT * FROM `class` c LEFT JOIN `teacher` t ON t.`t_id`=c.`teacher_id`
</select>

  方式三：嵌套查询，通过执行另外一个 SQL 映射语句来返回预期的复杂类型

<select id="getClass2" resultMap="ClassResultMap2">
    select * from class 
</select>

<resultMap type="cn.hxzy.mybatis.entity.Classes" id="ClassResultMap2">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" select="getTeacher"></association>
</resultMap>

<select id="getTeacher" parameterType="int" resultType="cn.hxzy.mybatis.entity.Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>

association 定义关联对象的封装规则

select：表明当前属性是调用 select 指定的方法查出的结果。

column：指定将哪一列的值传给这个方法。

注意：$ 与 # 的区别：

#{} 使用占位符 ？的方式编译和发送 SQL；好处：防止 SQL 注入（推荐）

${} 将用户填入的参数直接拼接到 SQL。坏处：SQL 注入；

注意：使用 #{} 不能生成表名和字段名，所以在字段和表名处，必须使用 ${}。

将 #{id} 转成 ${id} 会报 There is no getter for property named ‘id’ in ‘class java.lang.Integer’ ，只需要将参数换成 ${_parameter} 或者换成更高的 3.5.6 即可。

三、练习

  练习：创建项目 mybatis11，分别使用嵌套查询和嵌套结果完成如下用户角色的查询。

需使用两种查询完成如下功能：

一、查询单个用户时将其所属角色查询出来。

参考代码：

①创表数据：

CREATE TABLE `resource` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(50) NOT NULL COMMENT '名字',
  `url` varchar(50) NOT NULL COMMENT '地址',
  `pid` int(11) DEFAULT NULL COMMENT '父级id',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `order_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4;

/*Data for the table `resource` */

insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (1,'用户管理','/user',0,'2020-07-07 09:50:33','2020-07-07 09:50:36',20);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (2,'用户添加','/userEdit',1,'2020-07-07 14:38:18','2020-07-07 14:38:41',NULL);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (3,'用户修改','/userEdit',1,'2020-07-07 14:38:20','2020-07-07 14:38:43',NULL);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (4,'用户删除','/user',1,'2020-07-07 09:50:38','2020-07-07 14:38:45',NULL);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (5,'用户查询','/user',1,'2020-07-07 14:38:25','2020-07-07 14:38:47',NULL);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (6,'角色管理','/role',0,'2020-07-07 14:38:27','2020-07-07 14:40:26',30);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (7,'角色添加','/roleEdit',6,'2020-07-07 09:50:39','2020-07-07 14:38:48',NULL);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (8,'角色修改','/roleEdit',6,'2020-07-07 14:38:39','2020-07-07 14:38:50',NULL);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (12,'角色查询','/role',6,'2020-07-07 16:23:28','2020-07-09 10:17:27',NULL);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (13,'角色删除','/roleDelete',6,'2020-07-07 16:23:46','2020-07-07 16:27:44',NULL);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (14,'角色授权','/editRoleOfResource',6,'2020-07-07 16:24:11','2020-07-09 10:17:29',NULL);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (15,'资源管理','/resource',0,'2020-07-07 16:24:41','2020-07-09 10:17:31',40);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (16,'资源添加','/resourceEdit',15,'2020-07-07 16:25:15','2020-07-12 17:33:40',4);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (17,'资源删除','/resource',15,'2020-07-07 16:25:37','2020-07-12 17:33:34',3);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (19,'资源修改','/resourceEdit',15,'2020-07-07 16:26:09','2020-07-12 17:33:27',2);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (20,'资源查询','/resource',15,'2020-07-07 16:27:05','2020-07-12 17:32:59',1);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (21,'首页','/index',0,'2020-07-07 16:32:35','2020-07-09 10:17:39',10);
insert  into `resource`(`id`,`name`,`url`,`pid`,`create_time`,`update_time`,`order_number`) values (22,'资源授权','/authorize',6,'2020-07-13 15:45:22',NULL,1);

/*Table structure for table `role` */

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `remark` text,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

/*Data for the table `role` */

insert  into `role`(`id`,`name`,`remark`,`create_time`,`update_time`) values (1,'超级管理员','拥有整个网站的全部权限','2020-07-06 15:13:38','2020-07-06 15:03:08');
insert  into `role`(`id`,`name`,`remark`,`create_time`,`update_time`) values (2,'用户管理员','用户基本信息修改[用户添加，用户修改，用户查询，<br>用户修改密码，用户删除]','2020-07-09 10:17:07','2020-07-09 10:16:59');
insert  into `role`(`id`,`name`,`remark`,`create_time`,`update_time`) values (5,'普通用户','普通用户，只能查看网站首页','2020-07-07 16:30:53','2020-07-07 16:31:13');
insert  into `role`(`id`,`name`,`remark`,`create_time`,`update_time`) values (6,'测试','测试','2020-07-10 10:56:32','2020-07-12 14:49:33');

/*Table structure for table `role_resource` */

CREATE TABLE `role_resource` (
  `role_id` int(11) NOT NULL,
  `resource_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*Data for the table `role_resource` */

insert  into `role_resource`(`role_id`,`resource_id`) values (1,2);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,3);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,4);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,5);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,6);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,7);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,8);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,12);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,13);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,15);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,16);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,17);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,19);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,20);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,21);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,1);
insert  into `role_resource`(`role_id`,`resource_id`) values (2,1);
insert  into `role_resource`(`role_id`,`resource_id`) values (2,2);
insert  into `role_resource`(`role_id`,`resource_id`) values (2,3);
insert  into `role_resource`(`role_id`,`resource_id`) values (2,4);
insert  into `role_resource`(`role_id`,`resource_id`) values (2,5);
insert  into `role_resource`(`role_id`,`resource_id`) values (2,21);
insert  into `role_resource`(`role_id`,`resource_id`) values (5,21);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,22);
insert  into `role_resource`(`role_id`,`resource_id`) values (1,14);

/*Table structure for table `user` */

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `login_name` varchar(50) NOT NULL,
  `login_password` varchar(100) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `role_pk` (`role_id`),
  CONSTRAINT `role_pk` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8mb4;

/*Data for the table `user` */

insert  into `user`(`id`,`name`,`login_name`,`login_password`,`role_id`,`create_time`,`update_time`) values (87,'管理员','admin','123',1,'2020-07-09 10:17:48','2020-07-09 16:58:45');
insert  into `user`(`id`,`name`,`login_name`,`login_password`,`role_id`,`create_time`,`update_time`) values (88,'普通用户','user','123',5,'2020-07-07 16:31:30','2020-07-09 10:17:52');
insert  into `user`(`id`,`name`,`login_name`,`login_password`,`role_id`,`create_time`,`update_time`) values (91,'管理员','wpf','123',2,'2020-07-12 17:32:16','2020-07-13 10:10:38');

②实体类的创建

①Resource实体类：

public class Resource {

  private Integer id;
  private String name;
  private String url;
  private Integer pid;
  private Date createTime;
  private Date updateTime;
  private Integer orderNumber;


  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getUrl() {
    return url;
  }

  public void setUrl(String url) {
    this.url = url;
  }

  public Integer getPid() {
    return pid;
  }

  public void setPid(Integer pid) {
    this.pid = pid;
  }

  public Date getCreateTime() {
    return createTime;
  }

  public void setCreateTime(Date createTime) {
    this.createTime = createTime;
  }

  public Date getUpdateTime() {
    return updateTime;
  }

  public void setUpdateTime(Date updateTime) {
    this.updateTime = updateTime;
  }

  public Integer getOrderNumber() {
    return orderNumber;
  }

  public void setOrderNumber(Integer orderNumber) {
    this.orderNumber = orderNumber;
  }
}

②Role实体类

public class Role {

  private Integer id;
  private String name;
  private String remark;
  private Date createTime;
  private Date updateTime;


  @Override
  public String toString() {
    return "Role{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", remark='" + remark + '\'' +
            ", createTime=" + createTime +
            ", updateTime=" + updateTime +
            '}';
  }

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getRemark() {
    return remark;
  }

  public void setRemark(String remark) {
    this.remark = remark;
  }

  public Date getCreateTime() {
    return createTime;
  }

  public void setCreateTime(Date createTime) {
    this.createTime = createTime;
  }

  public Date getUpdateTime() {
    return updateTime;
  }

  public void setUpdateTime(Date updateTime) {
    this.updateTime = updateTime;
  }
}

③RoleResource实体类

public class RoleResource {

  private Integer roleId;
  private Integer resourceId;


  public Integer getRoleId() {
    return roleId;
  }

  public void setRoleId(Integer roleId) {
    this.roleId = roleId;
  }

  public Integer getResourceId() {
    return resourceId;
  }

  public void setResourceId(Integer resourceId) {
    this.resourceId = resourceId;
  }
}

④User实体类

public class User {

  private Integer id;
  private String name;
  private String loginName;
  private String loginPassword;
  private Date createTime;
  private Date updateTime;
  private Role role;

  @Override
  public String toString() {
    return "User{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", loginName='" + loginName + '\'' +
            ", loginPassword='" + loginPassword + '\'' +
            ", createTime=" + createTime +
            ", updateTime=" + updateTime +
            ", role=" + role +
            '}';
  }

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getLoginName() {
    return loginName;
  }

  public void setLoginName(String loginName) {
    this.loginName = loginName;
  }

  public String getLoginPassword() {
    return loginPassword;
  }

  public void setLoginPassword(String loginPassword) {
    this.loginPassword = loginPassword;
  }

  public Date getCreateTime() {
    return createTime;
  }

  public void setCreateTime(Date createTime) {
    this.createTime = createTime;
  }

  public Date getUpdateTime() {
    return updateTime;
  }

  public void setUpdateTime(Date updateTime) {
    this.updateTime = updateTime;
  }

  public Role getRole() {
    return role;
  }

  public void setRole(Role role) {
    this.role = role;
  }
}

③mapper文件夹下面的UserMapper接口

④resources下创建mapper文件夹，在mapper文件下创建userMapper.xml文件

①userMapper.xml文件内容

<?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="org.example.mapper.UserMapper">
<!--嵌套结果查询-->
    <resultMap id="userResultMap1" type="org.example.entity.User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="login_name" property="loginName"/>
        <result column="login_password" property="loginPassword"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <association property="role" javaType="org.example.entity.Role">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="remark" property="remark"/>
            <result column="create_time" property="createTime"/>
            <result column="update_time" property="updateTime"/>
        </association>
    </resultMap>
    <select id="findById1" resultMap="userResultMap1">
        select * from user u left join role r on u.role_id=r.id where u.id=#{id}
    </select>

<!--嵌套查询-->
    <resultMap id="userResultMap2" type="org.example.entity.User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="login_name" property="loginName"/>
        <result column="login_password" property="loginPassword"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <association property="role" column="role_id" select="roleQueryById">
        </association>
    </resultMap>
    <select id="findById2" resultMap="userResultMap2">
        select * from user where id=#{id}
    </select>
    <select id="roleQueryById" resultType="org.example.entity.Role">
        select id, name, remark, create_time createTime, update_time updateTime from role where id=#{id}
    </select>
</mapper>

⑤创建配置文件：mybatis-conf.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/userMapper.xml"></mapper>
    </mappers>
</configuration>

⑥Test类里面测试结果是否和预期的一样

  AppTest测试类

package org.example;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.example.entity.User;
import org.example.mapper.UserMapper;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;

public class AppTest {
    SqlSession session;

    @Before
    public void init() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-conf.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        session = sessionFactory.openSession(true);
    }

    @Test
    public void getById1() {
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.findById1(88);
        System.out.println(user);
    }

    @Test
    public void getById2() {
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.findById2(87);
        System.out.println(user);
    }

    @After
    public void close() {
        session.close();
    }
}

四、练习结果