mybatis的一对多和多对一的连接查询
实体类：
package com.entity;
import java.util.List;
public class Dept {
private Integer deptId;
private String deptName;
private List<Employee> employees;
@Override
public String toString() {
return "Dept{" +
"deptId=" + deptId +
", deptName='" + deptName + '\'' +
", employees=" + employees +
'}';
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public List<Employee> getEmployees() {
return employees;
}
public void setEmployees(List<Employee> employees) {
this.employees = employees;
}
}
package com.entity;
import java.math.BigDecimal;
public class Employee {
private Integer employeeId;
private String employeeName;
private Integer employeeGender;
private BigDecimal employeeSalary;
private Integer deptId;
private Dept dept;
@Override
public String toString() {
return "Employee{" +
"employeeId=" + employeeId +
", employeeName='" + employeeName + '\'' +
", employeeGender=" + employeeGender +
", employeeSalary=" + employeeSalary +
", deptId=" + deptId +
", dept=" + dept +
'}';
}
public Integer getEmployeeId() {
return employeeId;
}
public void setEmployeeId(Integer employeeId) {
this.employeeId = employeeId;
}
public String getEmployeeName() {
return employeeName;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public Integer getEmployeeGender() {
return employeeGender;
}
public void setEmployeeGender(Integer employeeGender) {
this.employeeGender = employeeGender;
}
public BigDecimal getEmployeeSalary() {
return employeeSalary;
}
public void setEmployeeSalary(BigDecimal employeeSalary) {
this.employeeSalary = employeeSalary;
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
}
多对一的几种方式：
1.
<mapper namespace="com.dao.EmployeeDao">
<!--<insert id="insert">-->
<!--<selectKey order="BEFORE" keyColumn="employeeId,employeeGender" keyProperty="employeeId,employeeGender" resultType="com.entity.Employee">-->
<!--select employee_id employeeId,employee_gender employeeGender from employee where employee_id=13-->
<!--</selectKey>-->
<!--insert into subtable(employee_id,employee_name,employee_gender) values (#{employeeId},"mdzz",#{employeeGender})-->
<!--</insert>-->
<resultMap id="empResultMap" type="com.entity.Employee">
<id property="employeeId" column="employee_id"></id>
<result property="employeeName" column="employee_name"></result>
<result property="employeeGender" column="employee_gender"></result>
<result property="employeeSalary" column="employee_salary"></result>
<result property="deptId" column="dept_id"></result>
<result property="dept.deptId" column="dept_id"></result>
<result property="dept.deptName" column="dept_name"></result>
</resultMap>
<select id="queryAll" resultMap="empResultMap">
select employee_id,employee_name,employee_gender,employee_salary,e.dept_id,dept_name,d.dept_id from employee e left outer join dept d on e.dept_id = d.dept_id;
</select>
</mapper>
2.
<resultMap id="empResultMap" type="com.entity.Employee">
<id property="employeeId" column="employee_id"></id>
<result property="employeeName" column="employee_name"></result>
<result property="employeeGender" column="employee_gender"></result>
<result property="employeeSalary" column="employee_salary"></result>
<result property="deptId" column="dept_id"></result>
<!--<result property="dept.deptId" column="dept_id"></result>-->
<!--<result property="dept.deptName" column="dept_name"></result>-->
<association property="dept" resultMap="deptResultMap"></association>
</resultMap>
<resultMap id="deptResultMap" type="com.entity.Dept">
<id property="deptId" column="dept_id"></id>
<result property="deptName" column="dept_name"></result>
</resultMap>
<select id="queryAll" resultMap="empResultMap">
select employee_id,employee_name,employee_gender,employee_salary,e.dept_id,dept_name,d.dept_id from employee e left outer join dept d on e.dept_id = d.dept_id;
</select>
2较之1的好处是单独把Dept类的resultMap提出来，可以复用
3.
<resultMap id="empResultMap" type="com.entity.Employee">
<id property="employeeId" column="employee_id"></id>
<result property="employeeName" column="employee_name"></result>
<result property="employeeGender" column="employee_gender"></result>
<result property="employeeSalary" column="employee_salary"></result>
<result property="deptId" column="dept_id"></result>
<!--<result property="dept.deptId" column="dept_id"></result>-->
<!--<result property="dept.deptName" column="dept_name"></result>-->
<association property="dept">
<id property="deptId" column="dept_id"></id>
<result property="deptName" column="dept_name"></result>
</association>
</resultMap>
<select id="queryAll" resultMap="empResultMap">
select employee_id,employee_name,employee_gender,employee_salary,e.dept_id,dept_name,d.dept_id from employee e left outer join dept d on e.dept_id = d.dept_id;
</select>
只不过是第2中方法把<association>的resultMap省略，直接在里面配置管理实体的映射配置，无法做到2那样的resultMap复用
4.不推荐，n+1查询，耗费资源。解决方法有：内嵌resultMap或者使用内部缓存（cache）解决一部分
<resultMap id="empResultMap" type="com.entity.Employee">
<id property="employeeId" column="employee_id"></id>
<result property="employeeName" column="employee_name"></result>
<result property="employeeGender" column="employee_gender"></result>
<result property="employeeSalary" column="employee_salary"></result>
<result property="deptId" column="dept_id"></result>
<association property="dept" column="dept_id" select="getDept"></association>
</resultMap>
<select id="queryAll" resultMap="empResultMap">
select employee_id,employee_name,employee_gender,employee_salary,dept_id from employee;
</select>
<select id="getDept" resultType="com.entity.Dept">
select dept_id,dept_name from dept where dept_id=#{deptId};
</select>
也就是使用<association>的property指定实体类的字段，column是这个字段所代表的表的主键，即子表的外键（employee的外键dept_id），select指定<select>的id。
再一个注意点就是<select id="getDept">中的select语句的where条件。
一对多：写法和多对一类似
1.resultMap+collection
<mapper namespace="com.dao.DeptDao">
<select id="queryAll" resultMap="deptResultMap">
select employee_id,employee_name,employee_gender,employee_salary,e.dept_id,dept_name,d.dept_id from employee e right outer join dept d on e.dept_id = d.dept_id;
</select>
<resultMap id="deptResultMap" type="com.entity.Dept">
<id property="deptId" column="dept_id"></id>
<result property="deptName" column="dept_name"></result>
<collection property="employees" resultMap="empResultMap"></association>
</resultMap>
<resultMap id="empResultMap" type="com.entity.Employee">
<id property="employeeId" column="employee_id"></id>
<result property="employeeName" column="employee_name"></result>
<result property="employeeGender" column="employee_gender"></result>
<result property="employeeSalary" column="employee_salary"></result>
<result property="deptId" column="dept_id"></result>
</resultMap>
</mapper>
另一种：n+1查询
<select id="queryAll" resultMap="deptResultMap">
select dept_id,dept_name from dept;
</select>
<resultMap id="deptResultMap" type="com.entity.Dept">
<id property="deptId" column="dept_id"></id>
<result property="deptName" column="dept_name"></result>
<collection property="employees" select="getEmp" column="dept_id"></collection>
</resultMap>
<select id="getEmp" resultMap="basicEmpResultMap">
select employee_id,employee_name,employee_gender,employee_salary,dept_id from employee;
</select>
<resultMap id="basicEmpResultMap" type="com.entity.Employee">
<id property="employeeId" column="employee_id"></id>
<result property="employeeName" column="employee_name"></result>
<result property="employeeGender" column="employee_gender"></result>
<result property="employeeSalary" column="employee_salary"></result>
<result property="deptId" column="dept_id"></result>
</resultMap>
public interface DeptDao {
List<Dept> queryAll();
}
public class DeptDaoTest {
@Test
public void testQueryAll() {
DeptDao deptDao = MapperFactory.generateMapper(DeptDao.class);
List<Dept> depts = deptDao.queryAll();
System.out.println(depts);
}
}
