Mybatis联合查询的实现方法
作者:WorkHaH 发布时间:2021-11-27 23:26:44
标签: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_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
0
投稿
猜你喜欢
- 1、@Valid与@Validated的区别1.1 基本区别@Valid:Hibernate validation校验机制@Validate
- 前言反射是我们框架的灵魂,反射也是我们框架的一个底层基石,没有反射也就没有框架,如果我们学好了反射,对我们阅读框架底层是有很大班助的——阿俊
- 前言链表是一种数据结构,和数组同级。比如,Java中我们使用的ArrayList,其实现原理是数组。而LinkedList的实现原理就是链表
- 这两天因为要做一个随机的地图生成系统,所以一直在研究随机迷宫生成算法,好吧,算是有一点小小的成果。随机迷宫生成我自己的理解简而言之分为以下几
- 一、前言我们经常会接触各种池化的技术或者概念,包括对象池、连接池、线程池等,池化技术最大的好处就是实现对象的重复利用,尤其是创建和使用大对象
- 简介MyBatis-Plus (opens new window)(简称 MP)是一个 MyBatis (opens new window)
- Spring AOP底层原理代理模式一、什么是 AOPAOP 就是面向切面编程,是 OOP(面向对象编程)的延续。利用 AOP 可以对业务逻
- 序言:使用MyBatis3提供的注解可以逐步取代XML,例如使用@Select注解直接编写SQL完成数据查询,使用@SelectProvid
- Java常用类包装类由于Java语言中的基本类型不是面向对象,并不具备对象的性质,实际使用存在很多不便。Java在java.lang包中提供
- 学过Spring的小伙伴对于IOC一定不陌生,IOC:控制反转(Inversion of Control,英文缩写为IoC)是一个重要的面向
- 实际开发中订单往往都包含着订单状态,用户每进行一次操作都要切换对应的状态,而每次切换判断当前的状态是必须的,就不可避免的引入一系列判断语句,
- 1.新建springBoot项目在前面有两种方式2.加入thymeleaf模板引擎SpringBoot推荐使用thymeleaf模板引擎语法
- java身份证合法性校验并获取身份证号有效信息,供大家参考,具体内容如下java身份证合法性校验/**身份证前6位【ABCDEF】为行政区划
- package com.letv.cloud.spider;import java.util.HashSet;import java.uti
- JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式,易于阅读和编写,同时也易于机器解析和生成。同X
- * 什么是 * Spring MVC中的 * (Interceptor)类似于Servlet中的过滤器(Filter),它主要用于拦截用户
- OutputDebugString属于windows API的,所以只要是包含了window.h这个头文件后就可以使用了。可以把调
- 在之前的文章中已经为大家介绍了java并发编程的工具:BlockingQueue接口、ArrayBlockingQueue、DelayQue
- 前言为什么用动静态库我们在实际开发中,经常要使用别人已经实现好的功能,这是为了开发效率和鲁棒性(健壮性);因为那些功能都是顶尖的工程师已经写
- 前言在现实项目中,数据量一般都不小,如果一次性全部请求出来,肯定是影响性能,而且大量数据展示到页面上观感也不好。这时我们就需要用到分页,给定