Mybatis系列七：映射文件-自定义结果集

女爷i 2022-04-24 08:54 250阅读 0赞

一、案例1：

<select id="getEmpById"  resultMap="MySimpleEmp">
    		select * from tbl_employee where id=#{id}
    	</select>
    	
    	
    	<resultMap type="com.atguigu.mybatis.bean.Employee" id="MySimpleEmp">
    		
    		<id column="id" property="id"/>
    		
    		<result column="last_name" property="lastName"/>
    		
    		<result column="email" property="email"/>
    		<result column="gender" property="gender"/>
    	</resultMap>

二、案例2：查询Employee的同时查询出员工对应的部门Dept  
创建表tbl\_dept和实体Department  
![在这里插入图片描述][20190420190507633.png]

public class Department {
    	
    	private Integer id;
    	private String departmentName;
    	private List<Employee> emps;
    	
    	
    	public List<Employee> getEmps() {
    		return emps;
    	}
    	public void setEmps(List<Employee> emps) {
    		this.emps = emps;
    	}
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public String getDepartmentName() {
    		return departmentName;
    	}
    	public void setDepartmentName(String departmentName) {
    		this.departmentName = departmentName;
    	}
    	@Override
    	public String toString() {
    		return "Department [id=" + id + ", departmentName=" + departmentName
    				+ "]";
    	}
    }

在tbl\_employee中添加外键d\_id并在实体Employee添加dept属性  
![在这里插入图片描述][20190420190715207.png]

public class Employee {
    	
    	private Integer id;
    	private String lastName;
    	private String email;
    	private String gender;
    	private Department dept;
    	
    	public Employee() {
    		super();
    	}
    	
    	public Employee(Integer id, String lastName, String email, String gender) {
    		super();
    		this.id = id;
    		this.lastName = lastName;
    		this.email = email;
    		this.gender = gender;
    	}
    	
    
    	public Department getDept() {
    		return dept;
    	}
    
    	public void setDept(Department dept) {
    		this.dept = dept;
    	}
    
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public String getLastName() {
    		return lastName;
    	}
    	public void setLastName(String lastName) {
    		this.lastName = lastName;
    	}
    	public String getEmail() {
    		return email;
    	}
    	public void setEmail(String email) {
    		this.email = email;
    	}
    	public String getGender() {
    		return gender;
    	}
    	public void setGender(String gender) {
    		this.gender = gender;
    	}
    	@Override
    	public String toString() {
    		return "Employee [id=" + id + ", lastName=" + lastName + ", email="
    				+ email + ", gender=" + gender + "]";
    	}
    }

创建EmployeeMapperPlus接口

public interface EmployeeMapperPlus {
    	
    	public Employee getEmpById(Integer id);
    	
    	public Employee getEmpAndDept(Integer id);
    	
    	public Employee getEmpByIdStep(Integer id);
    	
    	public List<Employee> getEmpsByDeptId(Integer deptId);
    
    }

创建DepartmentMapper接口

public interface DepartmentMapper {
    	
    	public Department getDeptById(Integer id);
    	
    	public Department getDeptByIdPlus(Integer id);
    
    	public Department getDeptByIdStep(Integer id);
    }

1、方式一：联合查询：级联属性封装结果集

<select id="getEmpAndDept" resultMap="MyDifEmp">
    		SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,
    		d.id did,d.dept_name dept_name FROM tbl_employee e,tbl_dept d
    		WHERE e.d_id=d.id AND e.id=#{id}
    	</select>
    
    	
    	<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyDifEmp">
    		<id column="id" property="id"/>
    		<result column="last_name" property="lastName"/>
    		<result column="gender" property="gender"/>
    		<result column="did" property="dept.id"/>
    		<result column="dept_name" property="dept.departmentName"/>
    	</resultMap>

2、方式二：使用association定义关联的单个对象的封装规则

<select id="getEmpAndDept" resultMap="MyDifEmp2">
    		SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,
    		d.id did,d.dept_name dept_name FROM tbl_employee e,tbl_dept d
    		WHERE e.d_id=d.id AND e.id=#{id}
    	</select>
    	
    	
    	<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyDifEmp2">
    		<id column="id" property="id"/>
    		<result column="last_name" property="lastName"/>
    		<result column="gender" property="gender"/>
    		
    		
    		<association property="dept" javaType="com.atguigu.mybatis.bean.Department">
    			<id column="did" property="id"/>
    			<result column="dept_name" property="departmentName"/>
    		</association>
    	</resultMap>

3、方式三：使用association进行分步查询

<select id="getEmpByIdStep" resultMap="MyEmpByStep">
    		select * from tbl_employee where id=#{id}
    	</select>
    	
    	
    	 <resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmpByStep">
    	 	<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.atguigu.mybatis.dao.DepartmentMapper.getDeptById"
    	 		column="d_id">
     		</association>
    	 </resultMap>
    	
    	 
    	  <setting name="lazyLoadingEnabled" value="true"/>
    	  <setting name="aggressiveLazyLoading" value="false"/>

在DepartmentMapper.xml中：

<select id="getDeptById" resultType="com.atguigu.mybatis.bean.Department">
    		select id,dept_name departmentName from tbl_dept where id=#{id}
    	</select>

测试：

@Test
    	public void test() throws IOException{
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession openSession = sqlSessionFactory.openSession();
    		try{
    			EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
    
    			Employee empById = mapper.getEmpById(1);
    			System.out.println(empById);
    
    			Employee empAndDept = mapper.getEmpAndDept(1);
    			System.out.println(empAndDept);
    			System.out.println(empAndDept.getDept());
    
    			Employee employee = mapper.getEmpByIdStep(1);
    			System.out.println(employee);
    			System.out.println(employee.getDept());
    		}finally{
    			openSession.close();
    		}
    	}

三、案例3：查询部门的时候将部门对应的所有员工信息也查询出来

1、方式一：嵌套结果集的方式

<select id="getDeptByIdPlus" resultMap="MyDept">
    		SELECT d.id did,d.dept_name dept_name,
    				e.id eid,e.last_name last_name,e.email email,e.gender gender
    		FROM tbl_dept d
    		LEFT JOIN tbl_employee e
    		ON d.id=e.d_id
    		WHERE d.id=#{id}
    	</select>
    
    	
    	<resultMap type="com.atguigu.mybatis.bean.Department" id="MyDept">
    		<id column="did" property="id"/>
    		<result column="dept_name" property="departmentName"/>
    		
    		<collection property="emps" ofType="com.atguigu.mybatis.bean.Employee">
    			
    			<id column="eid" property="id"/>
    			<result column="last_name" property="lastName"/>
    			<result column="email" property="email"/>
    			<result column="gender" property="gender"/>
    		</collection>
    	</resultMap>

2、方式二：分步查询

<select id="getDeptByIdStep" resultMap="MyDeptStep">
    		select id,dept_name from tbl_dept where id=#{id}
    	</select>
    
    	
    	<resultMap type="com.atguigu.mybatis.bean.Department" id="MyDeptStep">
    		<id column="id" property="id"/>
    		<id column="dept_name" property="departmentName"/>
    		<collection property="emps"
    			select="com.atguigu.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
    			column="id" fetchType="lazy"></collection>
    	</resultMap>
    
    
    	
    	 如：
    	<collection property="emps"
    			select="com.atguigu.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
    			column="{deptId=id}" fetchType="lazy"></collection>

在EmployeeMapperPlus.xml中：

<select id="getEmpsByDeptId" resultType="com.atguigu.mybatis.bean.Employee">
    		select * from tbl_employee where d_id=#{deptId}
    	</select>

测试：

@Test
    	public void test() throws IOException{
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession openSession = sqlSessionFactory.openSession();
    		
    		try{
    			DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
    
    			Department department = mapper.getDeptByIdPlus(1);
    			System.out.println(department);
    			System.out.println(department.getEmps());
    
    			Department deptByIdStep = mapper.getDeptByIdStep(1);
    			System.out.println(deptByIdStep.getDepartmentName());
    			System.out.println(deptByIdStep.getEmps());
    		}finally{
    			openSession.close();
    		}
    	}

四、案例4：鉴别器的使用

<select id="getEmpByIdStep" resultMap="MyEmpDis">
    		select * from tbl_employee where id=#{id}
    	</select>
    
    	
    	 <resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmpDis">
    	 	<id column="id" property="id"/>
    	 	<result column="last_name" property="lastName"/>
    	 	<result column="email" property="email"/>
    	 	<result column="gender" property="gender"/>
    	 	
    	 	<discriminator javaType="string" column="gender">
    	 		
    	 		<case value="0" resultType="com.atguigu.mybatis.bean.Employee">
    	 			<association property="dept" 
    			 		select="com.atguigu.mybatis.dao.DepartmentMapper.getDeptById"
    			 		column="d_id">
    		 		</association>
    	 		</case>
    	 		
    	 		<case value="1" resultType="com.atguigu.mybatis.bean.Employee">
    		 		<id column="id" property="id"/>
    			 	<result column="last_name" property="lastName"/>
    			 	<result column="last_name" property="email"/>
    			 	<result column="gender" property="gender"/>
    	 		</case>
    	 	</discriminator>
    	 </resultMap>

测试：

[20190420190507633.png]: /images/20220217/d581955d8bcb476585b22e178a1784bd.png
[20190420190715207.png]: /images/20220217/89b81e878a5741ccb77d09d75dc527ef.png