Mybatis聯(lián)合查詢的實現(xiàn)方法

更新時間：2022年01月07日 11:35:40 作者：WorkHaH

本文主要介紹了 Mybatis聯(lián)合查詢的實現(xiàn)方法，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下

1、級聯(lián)屬性封裝結(jié)果集

實現(xiàn)

這個要求很明顯就要用到兩個表，想要把部門信息封裝到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));
    }

結(jié)果

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));
    }

結(jié)果

要求二

現(xiàn)在的要求是輸入 id 把 department 表對應(yīng)的部門信息查詢出來，并且查詢該部門下的所有員工信息

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、級聯(lián)屬性封裝結(jié)果集

方法

<!--   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));
    }

結(jié)果

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));
    }

結(jié)果

到此這篇關(guān)于 Mybatis聯(lián)合查詢的實現(xiàn)方法的文章就介紹到這了,更多相關(guān) Mybatis聯(lián)合查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: