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 class="Department" 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 class="cn.com.legendapl.ibatis.domain.Employee" id="emp">
	<result property="id" column="e_id"/>
	<result property="name" column="e_name"/>
	<result property="title" column="e_title"/>
</resultMap>

<select id="query" parameterClass="java.util.Map" resultMap="dep">
	select
		d._id,
		d._name,
		d._location,
		e._id as e_id,
		e._name as e_name,
		e._title as e_title
	from
		t_dep as d
	left join
		t_emp as e
	on
		d._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;
	}
}

评论

4 楼 Bactryki 2014-04-23  

3 楼 xyz872389734 2012-07-18  

2 楼 a2397772 2012-04-25  

1 楼 wowo365 2011-11-01