iBATIS一对多/多对多N+1问题解决方案

• 博客分类：
• j2ee

iBATIS SQL QQ XML J#

对于iBATIS一对多/多对多的问题，传统的办法是在一对多/多对多关联的属性上再做一次子查询，这个解决办法很简单易懂，但是有个缺点，会导致N+1 selects，导致查询的性能瓶颈，更好的解决办法是sql做一个表连接，然后主表的resultMap配置上加“groupBy='...'”属性，这样一次查询就搞定，避免了N+1问题，下面请看代码：

Xml代码

1. <?xml version="1.0" encoding="UTF-8" ?> 
2. <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"  
3.  
4. "http://www.ibatis.com/dtd/sql-map-2.dtd"> 
5. <sqlMap namespace="sys"> 
6.     <typeAlias alias="menu" type="com.cuishen.sqlmaptest.vo.Menu" /> 
7.     <resultMap id="sub-menu-map" class="menu"> 
8.         <result property="id" column="subid" /> 
9.         <result property="name" column="subname" /> 
10.         <result property="url" column="suburl" /> 
11.         <result property="layer" column="sublayer" /> 
12.         <result property="grade" column="subgrade" /> 
13.         <result property="position" column="subposition" /> 
14.         <result property="parentId" column="subparentid" /> 
15.     </resultMap> 
16.     <resultMap id="menu-map" class="menu" groupBy="id"> 
17.         <result property="id" column="id" /> 
18.         <result property="name" column="name" /> 
19.         <result property="url" column="url" /> 
20.         <result property="layer" column="layer" /> 
21.         <result property="grade" column="grade" /> 
22.         <result property="position" column="position" /> 
23.         <result property="parentId" column="parentid" /> 
24.         <result property="subMenus" resultMap="sys.sub-menu-map" /> 
25.     </resultMap> 
26.  
27.     <select id="getTopMenu" parameterClass="java.util.HashMap" resultMap="sys.menu-map"> 
28.     select  
29.     t.id as id,t.name as name,t.url as url,t.layer as layer,t.grade as grade,t.position as position, 
30.     t.parentid as parentid,s.id as subid,s.name as subname,s.url as suburl,s.layer as sublayer, 
31.     s.grade as subgrade,s.position as subposition,s.parentid as subparentid 
32.     from menu t left join menu s 
33.     on t.id = s.parentid 
34.     where t.layer=1 order by t.position, s.position 
35.     </select> 
36. </sqlMap> 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" 

"http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="sys">
	<typeAlias alias="menu" type="com.cuishen.sqlmaptest.vo.Menu" />
	<resultMap id="sub-menu-map" class="menu">
		<result property="id" column="subid" />
		<result property="name" column="subname" />
		<result property="url" column="suburl" />
		<result property="layer" column="sublayer" />
		<result property="grade" column="subgrade" />
		<result property="position" column="subposition" />
		<result property="parentId" column="subparentid" />
	</resultMap>
	<resultMap id="menu-map" class="menu" groupBy="id">
		<result property="id" column="id" />
		<result property="name" column="name" />
		<result property="url" column="url" />
		<result property="layer" column="layer" />
		<result property="grade" column="grade" />
		<result property="position" column="position" />
		<result property="parentId" column="parentid" />
		<result property="subMenus" resultMap="sys.sub-menu-map" />
	</resultMap>

	<select id="getTopMenu" parameterClass="java.util.HashMap" resultMap="sys.menu-map">
	select 
	t.id as id,t.name as name,t.url as url,t.layer as layer,t.grade as grade,t.position as position,
	t.parentid as parentid,s.id as subid,s.name as subname,s.url as suburl,s.layer as sublayer,
	s.grade as subgrade,s.position as subposition,s.parentid as subparentid
	from menu t left join menu s
	on t.id = s.parentid
	where t.layer=1 order by t.position, s.position
	</select>
</sqlMap>

com.cuishen.sqlmaptest.vo.Menu如下：

Java代码

1. package com.cuishen.sqlmaptest.vo; 
2.  
3. import java.util.List; 
4.  
5. /**
6. * POJO - 菜单
7. * @author cuishen
8. */ 
9. public class Menu implements java.io.Serializable { 
10.     private static final long serialVersionUID = 7172793340860021199L; 
11.  
12.     private Long id; 
13.     private String name; 
14.     private String url; 
15.     private Short layer; 
16.     private Short grade; 
17.     private Short position; 
18.     private Long parentId; 
19.     private List subMenus; 
20.  
21.     public Menu() { } 
22.  
23.     public Long getId() { 
24.         return this.id; 
25.     } 
26.  
27.     public void setId(Long id) { 
28.         this.id = id; 
29.     } 
30.  
31.     //... 
32.     //getter、setter方法 
33.     //... 
34.  
35.     public List getSubMenus() { 
36.         return subMenus; 
37.     } 
38.  
39.     public void setSubMenus(List subMenus) { 
40.         this.subMenus = subMenus; 
41.     } 
42. } 

package com.cuishen.sqlmaptest.vo;

import java.util.List;

/**
 * POJO - 菜单
 * @author cuishen
 */
public class Menu implements java.io.Serializable {
	private static final long serialVersionUID = 7172793340860021199L;

	private Long id;
	private String name;
	private String url;
	private Short layer;
	private Short grade;
	private Short position;
	private Long parentId;
	private List subMenus;

	public Menu() { }

	public Long getId() {
		return this.id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	//...
	//getter、setter方法
	//...

	public List getSubMenus() {
		return subMenus;
	}

	public void setSubMenus(List subMenus) {
		this.subMenus = subMenus;
	}
}

测试类如下：

Java代码

1. package com.cuishen.sqlmaptest; 
2.  
3. import java.sql.SQLException; 
4. import java.util.List; 
5.  
6. import com.ibatis.sqlmap.client.SqlMapClient; 
7.  
8. import com.cuishen.sqlmaptest.vo.Menu; 
9.  
10. /**
11. * iBATIS 1:N & M:N（N+1 selects）解决方案
12. * @author cuishen
13. */ 
14. public class Test { 
15.     public static void main(String args[]) throws SQLException { 
16.         SqlMapClient sqlMapClient = SqlMapClientFactory.getSqlMapClient(); 
17.         List topMenus = sqlMapClient.queryForList("getTopMenu", null); 
18.         for(int i = 0; i < topMenus.size(); i++) { 
19.             Menu menu = (Menu)topMenus.get(i); 
20.             List subMenus = (List)menu.getSubMenus(); 
21.             System.out.println("top menu name >> " + menu.getName()); 
22.             for(int j = 0; j < subMenus.size(); j++) { 
23.                 Menu subMenu = (Menu)subMenus.get(j); 
24.                 System.out.println(">> sub menu name >> " + subMenu.getName());              
25.             } 
26.         } 
27.     } 
28. } 

package com.cuishen.sqlmaptest;

import java.sql.SQLException;
import java.util.List;

import com.ibatis.sqlmap.client.SqlMapClient;

import com.cuishen.sqlmaptest.vo.Menu;

/**
 * iBATIS 1:N & M:N（N+1 selects）解决方案
 * @author cuishen
 */
public class Test {
	public static void main(String args[]) throws SQLException {
		SqlMapClient sqlMapClient = SqlMapClientFactory.getSqlMapClient();
		List topMenus = sqlMapClient.queryForList("getTopMenu", null);
		for(int i = 0; i < topMenus.size(); i++) {
			Menu menu = (Menu)topMenus.get(i);
			List subMenus = (List)menu.getSubMenus();
			System.out.println("top menu name >> " + menu.getName());
			for(int j = 0; j < subMenus.size(); j++) {
				Menu subMenu = (Menu)subMenus.get(j);
				System.out.println(">> sub menu name >> " + subMenu.getName());				
			}
		}
	}
}

是不是很简单，但是有三点需要注意：

1. 注意这个配置：<result property="subMenus" resultMap="sys.sub-menu-map" />，resultMap是sys.sub-menu-map，要加命名空间sys，而不是sub-menu-map，否则iBATIS可能会报错！

2. 一对多/多对多映射的集合属性subMenus必须用简单的getter/setter，不能在getter/setter里做其他处理，否则会导致iBATIS报错！！

3. 在iBATIS2.3.0.677上测试通过，更老旧的版本可能不支持该解决方案！！


从iBATIS3.0开始该解决方案的配置改为：

Xml代码

1. <resultMap id="blogResult" type="Blog"> 
2.     <id property=”id” column="blog_id" /> 
3.     <result property="title" column="blog_title"/> 
4.     <collection property="posts" ofType="Post"> 
5.         <id property="id" column="post_id"/> 
6.         <result property="subject" column="post_subject"/> 
7.         <result property="body" column="post_body"/> 
8.     </collection> 
9. </resultMap> 

<resultMap id="blogResult" type="Blog">
	<id property=”id” column="blog_id" />
	<result property="title" column="blog_title"/>
	<collection property="posts" ofType="Post">
		<id property="id" column="post_id"/>
		<result property="subject" column="post_subject"/>
		<result property="body" column="post_body"/>
	</collection>
</resultMap>

更详细的信息请参阅iBATIS-3-User-Guide