Mybatisの关联查询

开始前的准备

  • 数据库 
    drop database if exists mybatisdemo;
create  database mybatisdemo;
use mybatisdemo;
DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee`(
  `id`  int NOT NULL AUTO_INCREMENT,
  `name`  varchar(32) NOT NULL COMMENT '员工名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employee` VALUES (1, '张三');
INSERT INTO `employee` VALUES (2, '李四');
INSERT INTO `employee` VALUES (3, '王五');
INSERT INTO `employee` VALUES (4, '王六');
INSERT INTO `employee` VALUES (5, '王八');

CREATE TABLE `dept` (
  `id`  int NOT NULL AUTO_INCREMENT  ,
  `name`  varchar(32) NOT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dept` VALUES (1, '技术部');
INSERT INTO `dept` VALUES (2, '市场部');
INSERT INTO `dept` VALUES (3, '销售部');

CREATE TABLE `emp_dept`(
  emp_id int,
  dept_id int,
  PRIMARY KEY (`emp_id`,`dept_id`)
)

insert into emp_dept values(1,1);
insert into emp_dept values(2,1);
insert into emp_dept values(3,3);
insert into emp_dept values(4,2);
insert into emp_dept values(5,3);
  • 先导入前三个表，第三个表数据先别insert
  • 导入mybatis_guanlian_select1项目，此项目的代码都已经用generator生成好了：密码930b
  • 要实现关联查询，必须要第三张表，表的内容是两个关联表的主键，否则会造成数据冗余

一对一关联查询

  1. 生成的Employee实体类并没有关于部门的字段（因为原来的表没有部门的字段），所以我们现在要添加对应的部门字段
    private Dept dept;
//对应的get set方法省略
  2. 进入测试类
    @Test
    public void test1() {
        List<Employee> employees = employeeMapper.selectByExample(null);
        for (Employee e :
                employees) {
            Dept dept = e.getDept();
            System.out.println(e.getName()+"->"+((dept!=null)?dept.getName():"天堂"));
        }
    }
  • 运行结果
    DEBUG [main] - ==>  Preparing: select id, name from employee 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 5
张三->天堂
李四->天堂
王五->天堂
王六->天堂
王八->天堂
    由此可知此方法无法select到dept对象！
  1. 在before里设置自动提交

sqlSession = sqlSessionFactory.openSession(true);
（或者在after里编写自动提交代码）
sqlSession.commit()
（或者像这样编写）

4. 新增一名employee，name为龙哥，发现需要使用左连接才能查询到龙哥的信息：

select * from employee e 
left join emp_dept ed on e.id=ed.emp_id
left join dept d on d.id=ed.dept_id;
  • 查询结果
  1. 此时发现查询的数据有我们不需要的，我们修改一下sql语句
    select e.id,e.name,dept_id,d.name 'dept_name' from employee e 
join emp_dept ed on e.id=ed.emp_id
join dept d on d.id=ed.dept_id;
  • 查询结果
  1. 编写一对一查询方法
  • 初步Mapper.xml
    <select id="">
   select
   e.id,e.name,dept_id,d.name 'dept_name'
   from employee e
   join emp_dept ed
   on e.id=ed.emp_id
   join dept d
   on d.id=ed.dept_id;
 </select>
    此时发现映射的resultMap只有
    因此我们需要改写resultMap

=-=完整版=-=

  • Mapper.java

    List<Employee> selectAllEmployeeWithDept();

  • Mapper.xml

    <resultMap id="BaseResultMap" type="net.suncaper.mybatisdemo.domain.Employee">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <association property="dept">
      <id column="dept_id"  property="id"/>
      <result column="dept_name" property="name"/>
    </association>
  </resultMap>
<!--association是负责将Dept在一对一查询中关联起来-->
<select id="selectAllEmployeeWithDept" resultMap="BaseResultMap">
    select
    e.id,e.name,dept_id,d.name 'dept_name'
    from employee e
    left join emp_dept ed
    on e.id=ed.emp_id
    left join dept d
    on d.id=ed.dept_id;
  </select>

  • Test.java

    @Test
    public void testSelectAllEmployeeWithDept() {
        List<Employee> employees = employeeMapper.selectAllEmployeeWithDept();
        for (Employee e :
                employees) {
            Dept dept = e.getDept();
            System.out.println(e.getName()+"->"+((dept!=null)?dept.getName():"天堂"));
        }
    }

  • 执行结果

    DEBUG [main] - ==>  Preparing: select e.id,e.name,dept_id,d.name 'dept_name' from employee e left join emp_dept ed on e.id=ed.emp_id left join dept d on d.id=ed.dept_id; 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 6
张三->技术部
李四->技术部
王六->市场部
王五->销售部
王八->销售部
龙哥->天堂

    最后把原来的baseResultMap复制一份，重新写一份新的，把原来添加到baseResultMap的代码都删掉

    <resultMap id="EmployeeWithDeptResultMap" type="net.suncaper.mybatisdemo.domain.Employee" extends="BaseResultMap">
<!--注意这个extends！继承了BaseResultMap-->
<!--就算继承了BaseResultMap，但是type是无法删除的，会导致编译无法通过-->
    <association property="dept">
      <id column="dept_id"  property="id"/>
      <result column="dept_name" property="name"/>
    </association>
  </resultMap>
  <select id="selectAllEmployeeWithDept" resultMap="EmployeeWithDeptResultMap">
    select
    e.id,e.name,dept_id,d.name 'dept_name'
    from employee e
    left join emp_dept ed
    on e.id=ed.emp_id
    left join dept d
    on d.id=ed.dept_id;
  </select>

一对一延迟加载（引子）

  1. 再次进行（开始前的准备中的导入项目）
  2. 这次我们不要关联层，重新写数据库
    drop database if exists mybatisdemo;
create  database mybatisdemo;
use mybatisdemo;
DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee`(
  `id`  int NOT NULL AUTO_INCREMENT,
  `name`  varchar(32) NOT NULL COMMENT '员工名称',
  dept_id int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employee`(`id`,`name`) VALUES (1, '张三');
INSERT INTO `employee`(`id`,`name`) VALUES (2, '李四');
INSERT INTO `employee`(`id`,`name`) VALUES (3, '王五');
INSERT INTO `employee`(`id`,`name`) VALUES (4, '王六');
INSERT INTO `employee`(`id`,`name`) VALUES (5, '王八');

CREATE TABLE `dept` (
  `id`  int NOT NULL AUTO_INCREMENT  ,
  `name`  varchar(32) NOT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dept` VALUES (1, '技术部');
INSERT INTO `dept` VALUES (2, '市场部');
INSERT INTO `dept` VALUES (3, '销售部');


update employee set dept_id = 1 where id = 1;
update employee set dept_id = 1 where id = 2;
update employee set dept_id = 3 where id = 3;
update employee set dept_id = 2 where id = 4;
update employee set dept_id = 3 where id = 5;
  3. 把导入项目里生成好的Mapper.java,Mapper.xml,都删掉，然后进行generator操作（注意把配置文件里的dept_emp table标签删除）
  4. 此时实验以下单表查询是否可以成功
  • Test.java
    @Test
    public void test1(){
        List<Employee> employees = employeeMapper.selectByExample(null);
        for (Employee e :
                employees) {
            System.out.println(e.getId()+":"+e.getName()+"->"+e.getDeptId());
        }
    }
  • 运行结果
    DEBUG [main] - ==>  Preparing: select id, name, dept_id from employee 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 5
1:张三->1
2:李四->1
3:王五->3
4:王六->2
5:王八->3
  1. 可是我们现在要查询的是人和部门名，因此要修改employee实体类，添加实体Dept dept在Employee类里
    注意保留generator生成的成员变量
    private Dept dept;
//get set方法省略
  2. 此时要想知道怎么查，先写sql语句
    select * from employee e 
left join dept d
on e.dept_id=d.id;
  • 查询结果
  1. 现在我们想先查询出Employee表的dept_id,然后通过dept_id查询需要的类别名，再包装显示出来
    EmpMapper.xml
     <resultMap id="BaseResultMap" type="net.suncaper.mybatisdemo.domain.Employee">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="dept_id" jdbcType="INTEGER" property="deptId" />
<!--在查询到dept_id后进行第二次查找，调用deptMapper的selectByPrimaryKey进行查询dept的name操作，然后进行包装-->
<!--property是查询到的映射，column是上一个查询的结果中的dept_id，select是调用DeptMapper中的方法所需要的写法，通过namespace映射到相应方法-->
    <association property="dept" column="dept_id" select="net.suncaper.mybatisdemo.mapper.DeptMapper.selectByPrimaryKey"/>
  </resultMap>
  • Test.java
    @Test
    public void test1(){
        List<Employee> employees = employeeMapper.selectByExample(null);
        for (Employee e :
                employees) {
            Dept dept = e.getDept();
            System.out.println(e.getName()+"-->"+dept!=null?dept.getName():"");
        }
    }
  • 结果
    DEBUG [main] - ====>  Preparing: select id, name from dept where id = ? 
DEBUG [main] - ====> Parameters: 2(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 5
技术部
技术部
销售部
市场部
销售部
    很明显，结果是不正确的，发现是因为没有用好()
  • Test.java
    @Test
    public void test1(){
        List<Employee> employees = employeeMapper.selectByExample(null);
        for (Employee e :
                employees) {
            Dept dept = e.getDept();
            System.out.println(e.getName()+"-->"+((dept!=null)?dept.getName():""));
        }
    }
  • 运行结果
    DEBUG [main] - ==>  Preparing: select id, name, dept_id from employee 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - ====>  Preparing: select id, name from dept where id = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select id, name from dept where id = ? 
DEBUG [main] - ====> Parameters: 3(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select id, name from dept where id = ? 
DEBUG [main] - ====> Parameters: 2(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 5
张三-->技术部
李四-->技术部
王五-->销售部
王六-->市场部
王八-->销售部
  1. 根据查询结果我们可以知道，这种方法使用了大量的sql语句，因为缓存的缘故，共有4条语句，效率很低
    为了提升这种写法的效率，我们引入延迟加载的概念。

一对一延迟加载（懒加载）

默认不是延迟加载

  1. 倘若我们将测试类的代码写为
  • Test.java
    @Test
   public void test1(){
       List<Employee> employees = employeeMapper.selectByExample(null);
       for (Employee e :
               employees) {
           System.out.println(e.getName());
       }
   }
  • 查询结果
    DEBUG [main] - ==>  Preparing: select id, name, dept_id from employee 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - ====>  Preparing: select id, name from dept where id = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select id, name from dept where id = ? 
DEBUG [main] - ====> Parameters: 3(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select id, name from dept where id = ? 
DEBUG [main] - ====> Parameters: 2(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 5
张三
李四
王五
王六
王八
  1. 依然使用了如此多的sql语句，在这里我们使用延迟加载,在EmployeeMapper.xml中的association标签中添加属性fetchType并设置值为lazy（此时就是延迟加载）
  • Mapper.xml
    <resultMap id="BaseResultMap" type="net.suncaper.mybatisdemo.domain.Employee">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="dept_id" jdbcType="INTEGER" property="deptId" />
<!--在查询到dept_id后进行第二次查找，调用deptMapper的selectByPrimaryKey进行查询dept的name操作，然后进行包装-->
    <association property="dept" column="dept_id" select="net.suncaper.mybatisdemo.mapper.DeptMapper.selectByPrimaryKey" fetchType="lazy"/>
  </resultMap>
  • 此时执行test后结果为
    DEBUG [main] - ==>  Preparing: select id, name, dept_id from employee 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 5
张三
李四
王五
王六
王八
  1. 此时延迟查询就成功了

延迟加载例子

  • Test.java
    @Test
    public void test3(){
        Employee employee = employeeMapper.selectByPrimaryKey(3);
        System.out.println(employee.getName());
    }
  • 运行结果
    DEBUG [main] - ==>  Preparing: select id, name, dept_id from employee where id = ? 
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - <==      Total: 1
王五
  • Test.java
    @Test
public void test3(){
    Employee employee = employeeMapper.selectByPrimaryKey(3);
    System.out.println(employee.getName());
    System.out.println(employee.getDept().getName());
}
  • 执行结果
    DEBUG [main] - ==>  Preparing: select id, name, dept_id from employee where id = ? 
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - <==      Total: 1
王五
DEBUG [main] - ==>  Preparing: select id, name from dept where id = ? 
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - <==      Total: 1
销售部

可以体现出延迟加载，在需要的时候再执行查询

可以在mybatis-config.xml中configuration标签下添加setting标签设置成如下代码可以实现全部懒加载

##一对多关联查询

  1. 修改Dept实体类
    private List<Employee> employeeList;
//get set方法省略
  • Test.java
    @Test
    public void test1(){
        Dept dept = deptMapper.selectByPrimaryKey(2);
        System.out.println(dept.getName());
    }
  • 运行结果
    DEBUG [main] - ==>  Preparing: select id, name from dept where id = ? 
DEBUG [main] - ==> Parameters: 2(Integer)
DEBUG [main] - <==      Total: 1
市场部
  1. 目标sql语句
    select d.id,d.name,e.id 'e_id',e.name 'e_name' 
from dept d 
join employee e 
on d.id=e.dept_id 
where d.id=3;
  2. Mapper.java
    Dept selectByPrimaryKeyWithEmp(Integer id);
  3. Mapper.xml
    <resultMap id="BaseResultMap1" type="net.suncaper.mybatisdemo.domain.Dept" extends="BaseResultMap">
<!--因为反射无法读取employeeListの具体的泛型，因此在collection中添加ofType属性-->
    <collection property="employeeList" ofType="Employee">
      <id property="id" column="e_id"/>
      <result property="name" column="e_name"/>
    </collection>
  </resultMap>
  <select id="selectByPrimaryKeyWithEmp" parameterType="int" resultMap="BaseResultMap1">
    select d.id,d.name,e.id 'e_id',e.name 'e_name'
from dept d
join employee e
on d.id=e.dept_id
where d.id=#{value};
  </select>
  4. Test.java
    @Test
   public void test1(){
       Dept dept = deptMapper.selectByPrimaryKeyWithEmp(3);
       System.out.println(dept.getName());
       System.out.println("----------------");
       List<Employee> employeeList = dept.getEmployeeList();
       if(employeeList!=null && employeeList.size()>0){
           for (Employee employee : employeeList) {
               System.out.println(employee.getName());
           }
       }
   }
  5. 运行结果
    DEBUG [main] - ==>  Preparing: select d.id,d.name,e.id 'e_id',e.name 'e_name' from dept d join employee e on d.id=e.dept_id where d.id=?; 
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - <==      Total: 2
销售部
----------------
王五
王八
    List，Set，数组都可以执行
  • 如果以后做页面，没有数据是空的很丑怎么办
    if(employeeList!=null && employeeList.size()==0){
    return null;
}