本篇内容主要讲解“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属性
方法一
<!-- public Employee getEmployee(int id); --><select id="getEmployee" resultMap="emp1">select e.*, d.id did, d.department_namefrom employee e,department dwhere e.d_id = d.idand e.id = #{id}</select><resultMap id="emp1" type="employee"><id column="id" property="id"/><result column="last_name" property="lastName"/><result column="email" property="email"/><result column="gender" property="gender"/><result column="did" property="dept.id"/><result column="department_name" property="dept.departmentName"/></resultMap>方法二
<!-- public Employee getEmployee(int id); --><select id="getEmployee" resultMap="emp2">select e.*, d.id did, d.department_namefrom employee e,department dwhere e.d_id = d.idand e.id = #{id}</select><resultMap id="emp2" type="employee"><id column="id" property="id"/><result column="last_name" property="lastName"/><result column="email" property="email"/><result column="gender" property="gender"/><association property="dept" javaType="department"><id column="did" property="id"/><result column="department_name" property="departmentName"/></association></resultMap>测试
@Test public void test1() { SqlSession sqlSession = MyTest.getSqlSession(); EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class); System.out.println(mapper.getEmployee(1)); }结果
2、分步查询
方法
DepartmentMapper.xml
<!-- public Department getDepartment2(int id); --><select id="getDepartment2" resultType="department">select * from department where id = #{id}</select>EmployeeMaper.xml
<!-- public Employee getEmployee2(int id); --><!-- 分步查询 --><select id="getEmployee2" resultMap="emp3">select * from employee where id = #{id}</select><resultMap id="emp3" type="employee"><id column="id" property="id"/><result column="last_name" property="lastName"/><result column="email" property="email"/><result column="gender" property="gender"/><association property="dept" select="com.workhah.mapper.department.DepartmentMapper.getDepartment2" column="d_id"/></resultMap>测试
@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<Employee> employees; setter和getter.......}3、级联属性封装结果集
方法
<!-- public Department getDepartment(int id); --><select id="getDepartment" resultMap="dep1">select d.*, e.id eid, e.last_name, e.email, e.genderfrom department dleft join employee e on d.id = e.d_idwhere d.id = #{id}</select><resultMap id="dep1" type="department"><id column="id" property="id"/><result column="department_name" property="departmentName"/><collection property="employees" ofType="employee"><id column="eid" property="id"/><result column="last_name" property="lastName"/><result column="email" property="email"/><result column="gender" property="gender"/></collection></resultMap>测试
@Test public void test2() { SqlSession sqlSession = MyTest.getSqlSession(); DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class); System.out.println(mapper.getDepartment(1)); }结果
4、分步查询
EmployeeMaper.xml
<!-- public List<Employee> getEmployeeByDid(int did); --><select id="getEmployeeByDid" resultType="employee">select *from employeewhere d_id = #{did}</select>DepartmentMapper.xml
<!-- public Department getDepartment3(int id); --><select id="getDepartment3" resultMap="dep2">select *from departmentwhere id = #{id}</select><resultMap id="dep2" type="department"><id column="id" property="id"/><result column="depart_name" property="departName"/><collection property="employees" ofType="employee"select="com.workhah.mapper.employee.EmployeeMapper.getEmployeeByDid" column="id"/></resultMap>测试
@Test public void test2() { SqlSession sqlSession = MyTest.getSqlSession(); DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class); System.out.println(mapper.getDepartment3(1)); }结果
到此，相信大家对“Mybatis联合查询怎么实现”有了更深的了解，不妨来实际操作一番吧！这里是编程网网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
