Mybatis联合查询的实现方法
作者:WorkHaH 时间:2021-11-27 23:26:44
数据库表结构
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_name
from employee e,
department d
where e.d_id = d.id
and 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_name
from employee e,
department d
where e.d_id = d.id
and 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.gender
from department d
left join employee e on d.id = e.d_id
where 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 employee
where d_id = #{did}
</select>
DepartmentMapper.xml
<!-- public Department getDepartment3(int id); -->
<select id="getDepartment3" resultMap="dep2">
select *
from department
where 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));
}
结果
来源:https://www.cnblogs.com/workhah/p/15759172.html
标签:Mybatis,联合查询
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
jar包手动添加到本地maven仓库的步骤详解
2023-11-23 05:09:37
![](https://img.aspxhome.com/file/2023/5/59785_0s.png)
mybatis-plus分页查询三种方法小结
2023-11-13 08:44:26
![](https://img.aspxhome.com/file/2023/6/63446_0s.png)
Java基础之练习打印三角形
2023-08-25 05:39:33
![](https://img.aspxhome.com/file/2023/7/58187_0s.png)
Java JDBC连接数据库常见操作总结
2021-12-01 23:01:40
![](https://img.aspxhome.com/file/2023/6/63746_0s.png)
java 实现MD5加密算法的简单实例
2023-07-19 21:53:56
Mybatis如何配置连接池
2021-10-22 03:06:47
java 中如何实现 List 集合去重
2023-10-07 07:54:06
![](https://img.aspxhome.com/file/2023/9/58549_0s.webp)
Android ListView的Item点击效果的定制
2023-06-21 11:46:01
java链式创建json对象的实现
2023-11-12 12:36:51
详解java Collections.sort的两种用法
2023-11-28 09:30:31
![](https://img.aspxhome.com/file/2023/3/60473_0s.png)
SpringBoot配置shiro安全框架的实现
2023-09-07 19:55:38
在Flutter中制作翻转卡片动画的完整实例代码
2023-06-23 23:31:21
Spring Cloud动态配置刷新RefreshScope使用示例详解
2022-05-23 15:05:32
Spring Boot 详细分析Conditional自动化配置注解
2021-11-25 21:56:14
![](https://img.aspxhome.com/file/2023/9/61799_0s.png)
Netty实现简易版的RPC框架过程详解
2023-05-23 23:19:58
![](https://img.aspxhome.com/file/2023/7/61537_0s.jpg)
Struts2学习笔记(8)-Result常用类型
2023-06-05 11:10:19
Java实现的zip工具类完整实例
2021-06-22 07:16:17
Java实现五子棋(附详细源码)
2023-10-19 15:20:54
![](https://img.aspxhome.com/file/2023/4/63384_0s.jpg)
bool当成函数参数错误理解
2021-07-30 09:27:18
Java设计模式中的命令模式
2023-11-20 04:26:46