Mybatis联合查询的实现方法

Mybatis联合查询的实现方法

目录1、级联属性封装结果集实现2、分步查询方法3、级联属性封装结果集4、分步查询

数据库表结构

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

whehttp://re 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}

select="com.workhah.mapper.employee.EmployeeMapper.getEmployeeByDid" column="id"/>

测试

@Test

public void test2() {

SqlSession sqlSession = MyTest.getSqlSession();

DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);

System.out.println(mapper.getDepartment3(1));

}

结果

select="com.workhah.mapper.employee.EmployeeMapper.getEmployeeByDid" column="id"/>

测试

@Test

public void test2() {

SqlSession sqlSession = MyTest.getSqlSession();

DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);

System.out.println(mapper.getDepartment3(1));

}

结果

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。