MyBatis之关联查询

最新推荐文章于 2024-09-22 09:01:06 发布

原创最新推荐文章于 2024-09-22 09:01:06 发布 · 305 阅读

1 ·

CC 4.0 BY-SA版权

MyBatis 专栏收录该内容

9 篇文章

订阅专栏

MyBatis

基于association查询（用于多对一或一对一）

创建实体类：Dept.java/Emp.java

public class Dept implements Serializable{
	private String deptAddress;
	private String deptName;
	private Integer deptId;

省略set和get方法

public class Emp implements Serializable{
	private String empId;
	private String empName;
	private String empSex;
        private Dept dept;
省略set和get方法

配置DeptMapper.xml/EmpMapper.xml

<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.entity.DeptMapper">
	<!-- 表字段和对应实体属性命名一致时可以不配置 -->
	<resultMap id="deptResultMap" type="Dept">
		<id property="deptId" column="dept_id" />
		<result property="deptName" column="dept_name" />
		<result property="deptAddress" column="dept_address" />
        </resultMap>

<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.itcast.entity.EmpMapper">

	<!-- 表字段和对应实体属性命名一致时可以不配置 -->
	<resultMap id="empResultMap" type="Emp">
		<id property="empId" column="emp_id" />
		<result property="empName" column="emp_name" />
		<result property="empSex" column="emp_sex" />
		<!-- association配置对一关联 -->
            <association property="dept" column="dept_id" javaType="Dept" resultMap="cn.itcast.entity.DeptMapper.deptResultMap" />
	</resultMap>

        <!--根据部门名称查询员工（包括员工所在部门）信息 -->
	<select id="selectEmpDeptList" parameterType="Emp" resultMap="empResultMap">
	<!-- 访问emp.dept.deptName, 前面不用写emp, 直接写 dept.deptName-->
		select e.*,d.* from emp e inner join dept d on
		e.dept_id=d.dept_id where d.dept_name like #{dept.deptName}
        </select>
</mapper>

编写EmpDaoImpl.java实现查询

public class EmpDaoImpl {
	SqlSession session;
	public List<Emp> selectEmpDeptList(Emp emp){
		List<Emp> emps=null;
		try{
		session=MyBatisUtil.getSession();
		emps=session.selectList("cn.itcast.entity.EmpMapper.selectEmpDeptList",emp);
		//session.commit();
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			//session.rollback();
		}finally{
			MyBatisUtil.closeSession();
		}
		return emps;
	}
}

基于collection查询（用于一对多或多对多）

编写 Dept.java/Emp.java实体类

public class Dept implements Serializable{
	private String deptAddress;
	private String deptName;
	private Integer deptId;
        private List<Emp> emps;
        //省略set和get方法

public class Emp implements Serializable{
	private String empId;
	private String empName;
	private String empSex;
        //省略set和get方法

配置DeptMapper.xml

<mapper namespace="cn.itcast.entity.DeptMapper">

	<!-- 表字段和对应实体属性命名一致时可以不配置 -->
	<resultMap id="deptResultMap" type="Dept">
		<id property="deptId" column="dept_id" />
		<result property="deptName" column="dept_name" />
		<result property="deptAddress" column="dept_address" />
	        <!-- collection中resultMap引用的是其它文件的map 需要命名空间+id,例如：cn.itcast.entity.EmpMapper.empResultMap -->
                <collection property="emps"  ofType="Emp" resultMap="cn.itcast.entity.EmpMapper.empResultMap"/>
	</resultMap>
	
	<select id="selectDeptEmpList" parameterType="Dept" resultMap="deptResultMap">
	    select d.*, e.* from dept d inner join emp e on	d.dept_id=e.dept_id where d.dept_name like #{deptName}
        </select>
</mapper>

配置EmpMapper.xml文件

<mapper namespace="cn.itcast.entity.EmpMapper">
	<!-- 表字段和对应实体属性命名一致时可以不配置 -->
	<resultMap id="empResultMap" type="Emp">
		<id property="empId" column="emp_id" />
		<result property="empName" column="emp_name" />
		<result property="empSex" column="emp_sex" />
	</resultMap>
</mapper>

编写数据库操作类DeptDaoImpl.java

public class DeptDaoImpl {
	//同时查询部门及部门员工信息
	public List<Dept> selectDeptEmpList(Dept dept){
		SqlSession session=null;
		List<Dept> deps=null;
		try{
		session=MyBatisUtil.getSession();
		deps=session.selectList("cn.itcast.entity.DeptMapper.selectDeptEmpList",dept);
		session.commit();
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			session.rollback();
		}finally{
			MyBatisUtil.closeSession();
		}
		return deps;
	}
}

一对多双向关联查询示例

编写实体类:Dept.java/Emp.java

public class Dept implements Serializable{
	private String deptAddress;
	private String deptName;
	private Integer deptId;
	private List<Emp> emps;
        //省略set和get方法

public class Emp implements Serializable{
	private String empId;
	private String empName;
	private String empSex;
	private Dept dept;
        //省略set/get方法

编写DeptMapper.xml/EmpMapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.entity.DeptMapper">

	<!-- 表字段和对应实体属性命名一致时可以不配置 -->
	<resultMap id="deptResultMap" type="Dept">
		<id property="deptId" column="dept_id" />
		<result property="deptName" column="dept_name" />
		<result property="deptAddress" column="dept_address" />
	</resultMap>

	<!-- 一对多时，“多”的关联属性可独立配置resultMap，采用extends继承基本属性的resultMap -->
	<resultMap id="deptExtResultMap" type="Dept" extends="deptResultMap">
		<!-- collection中resultMap引用的是其它文件的map 需要命名空间+id,例如：cn.itcast.entity.EmpMapper.empResultMap -->
		<collection property="emps" ofType="Emp"
			resultMap="cn.itcast.entity.EmpMapper.empResultMap" />
	</resultMap>

<!--用于部门和员工关联查询，返回部门信息（包含部门员工信息）列表，采用extends继承基本属性的resultMap -->
	<select id="selectDeptEmpList" parameterType="Dept" resultMap="deptExtResultMap">
		select d.*, e.* from dept d inner join emp e on d.dept_id=e.dept_id
		where d.dept_name like #{deptName}
	</select>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.itcast.entity.EmpMapper">

	<!-- 表字段和对应实体属性命名一致时可以不配置 -->
	<resultMap id="empResultMap" type="Emp">
		<id property="empId" column="emp_id" />
		<result property="empName" column="emp_name" />
		<result property="empSex" column="emp_sex" />
		<!--注意association元素的resultMap的值为没有配置“多”的属性映射的deptResultMap，如下  -->
		<association property="dept" column="dept_id" resultMap="cn.itcast.entity.DeptMapper.deptResultMap"/>
	</resultMap>

	<!-- 用于员工和部门关联查询，返回员工信息（包含部门信息）列表 -->
	<select id="selectEmpDeptList" parameterType="Emp" resultMap="empResultMap">
		select e.*,d.* from emp e inner join dept d on
		e.dept_id=d.dept_id where d.dept_name like #{dept.deptName}
	</select>
	
</mapper>

编写数据操作类：DeptDaoImpl.java/EmpDaoImpl.java

public List<Dept> selectDeptEmpList(Dept dept){
		SqlSession session=null;
		List<Dept> deps=null;
		try{
		session=MyBatisUtil.getSession();
		deps=session.selectList("cn.itcast.entity.DeptMapper.selectDeptEmpList",dept);
		session.commit();
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			session.rollback();
		}finally{
			MyBatisUtil.closeSession();
		}
		return deps;
	}

public List<Emp> selectEmpDeptList(Emp emp){
		SqlSession session=null;
		List<Emp> emps=null;
		try{
		session=MyBatisUtil.getSession();
		emps=session.selectList("cn.itcast.entity.EmpMapper.selectEmpDeptList",emp);
		session.commit();
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			session.rollback();
		}finally{
			MyBatisUtil.closeSession();
		}
		return emps;
	}

编写测试类:DeptTest.java/EmpTest.java

//测试部门和员工的关联查询，并遍历装载部门类型的结果集
	@Test
	public void selectDeptEmpList() {
		Dept paramDept=new Dept();
		paramDept.setDeptName("%研%");
		List<Dept> depts=deptDaoImpl.selectDeptEmpList(paramDept);
		for(Dept dept:depts){
		System.out.println("dept:"+dept);
		}
	}

public void selectEmpDeptList() {
		Emp emp=new Emp();
		Dept dept=new Dept();
		dept.setDeptName("%研%");
		emp.setDept(dept);
		List<Emp> emps=empDaoImpl.selectEmpDeptList(emp);
		for(Emp emp1:emps){
			System.out.println("emp="+emp1);
			System.out.println("dept＝"+emp1.getDept());
		}
	}