前提:
三个表:公司,部门,员工 。(部门和员工表中 均有 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);
}
}
以上为基本思路,不提供详细代码。代码运行成功。