mybatis 关联查询 含有集合的嵌套查询 并传多个参数

前提:

三个表:公司,部门,员工   。(部门和员工表中 均有 companyId,departmentId 两字段)

要求:

查询部门信息和本部门中所有员工的信息, 


java实体类:

public class Department {
	/** 部门id */
	private int departmentId;
	/** 部门名 */
	private String departmentName;
	/** 部门经理 */
	private String departmentManagerName;
	/** 部门所属公司ID */
	private int companyId;
	/** 关联属性 ,用于封装部门对应的员工信息 */
	private List<Employee> employees;

DAO:

public interface DepartmentDao {
	/**
	 * 根据公司ID、部门ID 查询部门信息 和 本部门所有员工信息
	 * @param comId
	 * @param deptId
	 * @return Department类
	 */
	public Department findDeptInfo(@Param("companyId") int comId,@Param("departmentId") int deptId);

mapper.xml:

<sql id="departmentAllField">departmentId,departmentName,departmentManagerName,companyId</sql>
<sql id="employeeAllField">employeeId,employeeName,sex,age,address,phoneNumber,companyId,departmentId,job</sql>

<select id="findDeptInfo" resultMap="deptMap"> 
	select <include refid="departmentAllField" /> from department where companyId=#{companyId} and departmentId=#{departmentId}; 
</select>

<resultMap type="com.demo.model.Department" id="deptMap">
	<!-- 传递两个字段 -->
	<collection property="employees" javaType="ArrayList" 
	 column="{companyId = companyId,departmentId=departmentId}" ofType="com.demo.model.Employee" select="findEmpsByDept"/>
</resultMap>

<select id="findEmpsByDept" resultType="com.demo.model.Employee">
	select <include refid="employeeAllField" /> from employee
	 where companyId=#{companyId} and departmentId=#{departmentId}; 
</select>

Test类:

public class TestFindDeptInfo {
	static AbstractApplicationContext ac;
	SqlSessionFactory factory;
	SqlSession session;
	DepartmentDao deptDao;
	
	@Before
	public void init(){
		String conf = "applicationContext.xml";
		ac = new ClassPathXmlApplicationContext(conf);
		factory = ac.getBean("ssf",SqlSessionFactory.class );
		session = factory.openSession();
	}
	@Test	
	public void testDao1(){
		deptDao = session.getMapper(DepartmentDao.class);
		Department dept = deptDao.findDeptInfo(6201, 1);
		System.out.println(dept);
	}
}



以上为基本思路,不提供详细代码。代码运行成功。

©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页