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

最新推荐文章于 2025-06-29 13:38:11 发布

原创最新推荐文章于 2025-06-29 13:38:11 发布 · 6.9k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #resultMap #多参数

spring+mybatis 专栏收录该内容

11 篇文章

订阅专栏

前提：

三个表：公司，部门，员工。(部门和员工表中均有 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);
	}
}

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