Mybatis联合查询怎么实现

本篇内容主要讲解"Mybatis联合查询怎么实现"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"Mybatis联合查询怎么实现"吧!

数据库表结构

department

employee

要求一

现在的要求是输入 id 把 employee 表的对应员工数据查询出来，并且查询出该员工的所处部门信息

public class Employee {    private Integer id;    private String lastName;    private String email;    private String gender;    private Department dept;        setter和getter.......}

public class Department {    private Integer id;    private String departmentName;    setter和getter.......}

1、级联属性封装结果集

实现

这个要求很明显就要用到两个表，想要把部门信息封装到Employee对象的dept字段需要用到resultMap属性

方法一

         select e.*, d.id did, d.department_name        from employee e,                department d        where e.d_id = d.id        and e.id = #{id}                                                

方法二

        select e.*, d.id did, d.department_name        from employee e,                department d        where e.d_id = d.id        and e.id = #{id}                                                                                

测试

         @Test    public void test1() {        SqlSession sqlSession = MyTest.getSqlSession();        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);        System.out.println(mapper.getEmployee(1));    }

结果

2、分步查询

方法

DepartmentMapper.xml

        select * from department where id = #{id}

EmployeeMaper.xml

        select * from employee where id = #{id}                                        

测试

         @Test    public void test1() {        SqlSession sqlSession = MyTest.getSqlSession();        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);        System.out.println(mapper.getEmployee2(1));    }

结果

要求二

现在的要求是输入 id 把 department 表对应的部门信息查询出来，并且查询该部门下的所有员工信息

public class Employee {    private Integer id;    private String lastName;    private String email;    private String gender;        setter和getter.......}

public class Department {    private Integer id;    private String departmentName;    private List employees;    setter和getter.......}

3、级联属性封装结果集

方法

        select d.*, e.id eid, e.last_name, e.email, e.gender        from department d                left join employee e on d.id = e.d_id        where d.id = #{id}                                                                                                

测试

         @Test    public void test2() {        SqlSession sqlSession = MyTest.getSqlSession();        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);        System.out.println(mapper.getDepartment(1));    }

结果

4、分步查询

EmployeeMaper.xml

        select *        from employee        where d_id = #{did}

DepartmentMapper.xml

        select *        from department        where id = #{id}                        

测试

         @Test    public void test2() {        SqlSession sqlSession = MyTest.getSqlSession();        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);        System.out.println(mapper.getDepartment3(1));    }

结果

到此，相信大家对"Mybatis联合查询怎么实现"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！