Mybatis学习-关联查询（部门和员工多对一的关系查询collection）

最新推荐文章于 2022-05-10 13:01:53 发布

明德致远

最新推荐文章于 2022-05-10 13:01:53 发布

阅读量1.9k

点赞数

分类专栏： mybatis学习文章标签： Mybatis学习-关联查询（部门和员工多对一的关系查询collect

本文链接：https://blog.csdn.net/weixin_41865602/article/details/89319836

版权

mybatis学习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

以下是有关collection多对一的使用（以部门和员工为例）

部门的实体类

private Integer id;				//部门编号	
private String departmentName;	//部门名称
private List<Employee> employees;//部门和员工是多对一的关系
..

员工的实体类

private Integer id;
// 不能被修改
private String lastname;
private String email;
// 从前端传入的是 String 类型, 所以需要注意转换
private Date birth;
// 不能被修改
private Date createtime;
//添加头像
private String emp_image;
// 单向 n-1 的关联关系
private Department department;
..

DepartmentMapper.xml文件嵌套结果集的形式

<!--//根据di查询出部门信息
Department getDeptByidPlus(Integer id);  -->
<resultMap type="com.ldp.mybatis.bean.Department" id="dept_emp">
	<!-- 定义主键 -->
	<id column="id" property="id"/>
	<!-- 普通字段 -->
	<result column="departmentName" property="departmentName"/>
	<!-- 下面是关联查询
		collection：多对一使用该标签
		property="employees"在部门pojo中属性名名称
		ofType:代表集合中的对象
	 -->
	<collection property="employees" ofType="com.ldp.mybatis.bean.Employee">
		<!-- 主键 -->
		<id column="eid" property="id"/>
		<!-- 普通字段 -->
		<result column="lastname" property="lastname"/>
		<result column="email" property="email"/>
		<result column="birth" property="birth"/>
		<result column="createtime" property="createtime"/>
		<result column="emp_image" property="emp_image"/>
		<!-- 查询出来的是 department_id 所以属性中的也需要department.id点一下-->
		<result column="department_id" property="department.id"/>
	</collection>
	
</resultMap>
<select id="getDeptByidPlus" parameterType="int" resultMap="dept_emp">
	SELECT 
	  d.`id`,
	  d.`departmentName`,
	  e.`id` eid,
	  e.`birth`,
	  e.`createtime`,
	  e.`department_id`,
	  e.`email`,
	  e.`emp_image`,
	  e.`lastname`
	FROM
	  `department` d LEFT JOIN employee e
	  ON d.`id`=e.`department_id`
	  WHERE d.`id`=#{id};
</select>

/**
* 测试多对一
* @throws IOException
*/

@Test
public void test01() throws IOException {
	//首先获得SqlSessionFactory对象
	SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
	//获得sqlSession
	SqlSession sqlSession = sqlSessionFactory.openSession(true);
	try {
		DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);
		//进行查询
		Department department = departmentMapper.getDeptByidPlus(1);
		System.out.println(department);
		List<Employee> list=department.getEmployees();
		for (Employee employee : list) {
			System.out.println(employee);
		}
		
	} finally {
		sqlSession.close();
	}
}

测试的结果

DEBUG 04-15 19:38:54,444 ==>  Preparing: SELECT d.`id`, d.`departmentName`, e.`id` eid, e.`birth`, e.`createtime`, e.`department_id`, e.`email`, e.`emp_image`, e.`lastname` FROM `department` d LEFT JOIN employee e ON d.`id`=e.`department_id` WHERE d.`id`=?;   (BaseJdbcLogger.java:145) 
DEBUG 04-15 19:38:54,542 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 04-15 19:38:54,592 <==      Total: 4  (BaseJdbcLogger.java:145) 
Department [id=1, departmentName=行政部]
Employee [id=7, lastname=刘东平7, email=1846882398@qq.com, birth=Wed Mar 27 00:00:00 CST 2019, createtime=Wed Mar 27 20:01:59 CST 2019, emp_image=C:/upload/14/2/f0312f6e638248058eaebae3f4d8952f.jpg, department=Department [id=1, departmentName=null]]
Employee [id=201, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 11:44:26 CST 2019, createtime=Mon Apr 15 11:34:26 CST 2019, emp_image=null, department=Department [id=1, departmentName=null]]
Employee [id=202, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 14:40:48 CST 2019, createtime=Mon Apr 15 14:40:48 CST 2019, emp_image=null, department=Department [id=1, departmentName=null]]
Employee [id=203, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 14:42:17 CST 2019, createtime=Mon Apr 15 14:42:17 CST 2019, emp_image=null, department=Department [id=1, departmentName=null]]

利用collection分步查询

com.ldp.mybatis.dao.EmployeeMapper 下面定义一个根据部门id查询员工信息的配置信息

<select id="getBydeptId" parameterType="int" resultType="com.ldp.mybatis.bean.Employee">
	select * from employee where department_id=#{department_id};
</select>

在com.ldp.mybatis.dao.DepartmentMapper.xml 下面的定义信息

<!-- //collection进分步查询出数据
Department getDeptBySept(Integer id); -->
<resultMap type="com.ldp.mybatis.bean.Department" id="dept_emp_sept">
		<!-- 定义主键 -->
	<id column="id" property="id"/>
	<!-- 普通字段 -->
	<result column="departmentName" property="departmentName"/>
	<!-- 下面是关联查询
		collection：多对一使用该标签
		property="employees"在部门pojo中属性名名称
		ofType:代表集合中的对象
	 -->
	 <collection property="employees" select="com.ldp.mybatis.dao.EmployeeMapper.getBydeptId" column="id"></collection>
</resultMap>
<!-- 定义的查询方法 -->
<select id="getDeptBySept" parameterType="int" resultMap="dept_emp_sept">
	select * from department where id=#{id}
</select>

/**
* 测试多对一(利用分步查询的方式)
* @throws IOException
*/

@Test
public void test02() throws IOException {
	//首先获得SqlSessionFactory对象
	SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
	//获得sqlSession
	SqlSession sqlSession = sqlSessionFactory.openSession(true);
	try {
		DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);
		//进行查询
		Department department = departmentMapper.getDeptBySept(1);
		System.out.println(department);
		List<Employee> list=department.getEmployees();
		for (Employee employee : list) {
			System.out.println(employee);
		}
		
	} finally {
		sqlSession.close();
	}
}

查询结果:会有两条SQL语句

DEBUG 04-15 20:02:35,029 ==>  Preparing: select * from department where id=?   (BaseJdbcLogger.java:145) 
DEBUG 04-15 20:02:35,119 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 04-15 20:02:35,441 <==      Total: 1  (BaseJdbcLogger.java:145) 
DEBUG 04-15 20:02:35,444 ==>  Preparing: select * from employee where department_id=?;   (BaseJdbcLogger.java:145) 
DEBUG 04-15 20:02:35,446 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 04-15 20:02:35,456 <==      Total: 4  (BaseJdbcLogger.java:145) 
Department [id=1, departmentName=行政部]
Employee [id=7, lastname=刘东平7, email=1846882398@qq.com, birth=Wed Mar 27 00:00:00 CST 2019, createtime=Wed Mar 27 20:01:59 CST 2019, emp_image=C:/upload/14/2/f0312f6e638248058eaebae3f4d8952f.jpg, department=null]
Employee [id=201, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 11:44:26 CST 2019, createtime=Mon Apr 15 11:34:26 CST 2019, emp_image=null, department=null]
Employee [id=202, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 14:40:48 CST 2019, createtime=Mon Apr 15 14:40:48 CST 2019, emp_image=null, department=null]
Employee [id=203, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 14:42:17 CST 2019, createtime=Mon Apr 15 14:42:17 CST 2019, emp_image=null, department=null]

collection也有缓存加载（也称按需加载）
前提条件需要在配置文件中配置以下信息

<settings>
		<setting name="mapUnderscoreToCamelCase" value="true"/> 
		<setting name="jdbcTypeForNull" value="NULL"/>
		<!--显示的指定每个我们需要更改的配置的值，即使他是默认的。防止版本更新带来的问题  -->
		<setting name="lazyLoadingEnabled" value="true"/>
		<setting name="aggressiveLazyLoading" value="false"/>
	</settings>

将测试代码修改为

DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);
		//进行查询
		Department department = departmentMapper.getDeptBySept(1);
		System.out.println(department.getDepartmentName());

查询的结果只有一条SQL语句

DEBUG 04-15 20:08:02,050 ==>  Preparing: select * from department where id=?   (BaseJdbcLogger.java:145) 
DEBUG 04-15 20:08:02,168 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 04-15 20:08:02,414 <==      Total: 1  (BaseJdbcLogger.java:145) 
行政部

扩展知识

1.在进行分步查询的时候：如果column需要传输多列的值的情况如何处理？
将多列值封装成map传输过去

column{key1=“id”,key=“lasename”}

2.配置文件之后默认是进行延迟加载的，那么是否可以手动修改懒加载状态？

fetchType=“lazy” 表示使用延迟加载
lazy:表示延迟加载
eager：表示即使加载

明德致远

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mybatis学习-关联查询（部门和员工多对一的关系查询collection）

以下是有关collection多对一的使用（以部门和员工为例）部门的实体类private Integer id; //部门编号 private String departmentName; //部门名称private List<Employee> employees;//部门和员工是多对一的关系..员工的实体类private Integer id;// 不能被修...
复制链接

扫一扫