iBatis双向一对多映射中解决N+1查询有关问题

iBatis双向一对多映射中解决N+1查询问题
为了方便，用一个小例子说明问题。有两个实体类，Department和Employee。
Department为一，Employee为多。

package cn.com.legendapl.ibatis.domain;import org.apache.commons.lang.builder.ToStringBuilder;import org.apache.commons.lang.builder.ToStringStyle;public class Employee implements java.io.Serializable {private static final long serialVersionUID = 8830655291098555343L;private Integer id;private String name;private String title;private Department department;// getter and setter and constrctor}

package cn.com.legendapl.ibatis.domain;import java.util.Set;import org.apache.commons.lang.builder.ToStringBuilder;import org.apache.commons.lang.builder.ToStringStyle;public class Department implements java.io.Serializable {private static final long serialVersionUID = 133006271347210670L;private Integer id;private String name;private String location;private Set<Employee> employees;// getter and setter and constrctor}

两个实体类分别对应数据库的两张表

mysql> desc t_dep;+-----------+-------------+------+-----+---------+----------------+| Field     | Type        | Null | Key | Default | Extra          |+-----------+-------------+------+-----+---------+----------------+| _id       | int(11)     | NO   | PRI | NULL    | auto_increment || _name     | varchar(30) | NO   |     | NULL    |                || _location | varchar(50) | YES  |     | NULL    |                |+-----------+-------------+------+-----+---------+----------------+3 rows in set (0.01 sec)mysql> desc t_emp;+---------+-------------+------+-----+---------+----------------+| Field   | Type        | Null | Key | Default | Extra          |+---------+-------------+------+-----+---------+----------------+| _id     | int(11)     | NO   | PRI | NULL    | auto_increment || _name   | varchar(30) | NO   |     | NULL    |                || _title  | varchar(5)  | YES  |     | NULL    |                || _dep_id | int(11)     | YES  |     | NULL    |                |+---------+-------------+------+-----+---------+----------------+

在“一”的一方配置时映射时

<resultMap id="dep" groupBy="id"><result property="id" column="_id"/><result property="name" column="_name"/><result property="location" column="_location"/><result property="employees" resultMap="department.emp"/></resultMap><resultMap id="emp"><result property="id" column="e_id"/><result property="name" column="e_name"/><result property="title" column="e_title"/></resultMap><select id="query" parameterresultMap="dep">selectd._id,d._name,d._location,e._id as e_id,e._name as e_name,e._title as e_titlefromt_dep as dleft joint_emp as eond._id = e._dep_id<dynamic prepend="where"><isNotEmpty property="id" prepend="and"> d._id = #id# </isNotEmpty></dynamic>   </select>

在名为dep的resultMap中配置一项groupBy="id"，这样，ibatis在处理结果集时，把id相同的几项“看成”一项来处理。
注意：groupBy属性的配置，是指的映射到的模型的property name 而不是查询的结果集的列名。

dao 实现中

@Repository("departmentDao")public class DepartmentDaoIbatisImpl implements DepartmentDao {@Resource(name="sqlMapClientTemplate")private SqlMapClientTemplate sqlMapClientTemplate;public Department findDepartmentById(Integer id) {Map<String, Object> map = new HashMap<String, Object>(1);map.put("id", id);Department dep = (Department) sqlMapClientTemplate.queryForObject("department.query", map);for (Employee emp : dep.getEmployees()) {emp.setDepartment(dep);}return dep;}}

查看更多 下一篇