ibatis 一对多关系映射等使用的一些技巧2011-03-01 10:431.在<result/>标签中,使用select属性
例如:用户和角色
<sqlMap namespace="user">
<resultMap id="userResult" class="ur">
<result property="id" column="id" />
<result property="name" column="name" />
<result property="account" column="account" />
<result property="password" column="password" />
<result property="deptId" column="deptId" />
...........
<result property="roles" column="id" select="user.selectUserRole" />
<result property="fullName" column="{userName=name,deptCode=deptcode}" select="user.getUserFullName" />
</resultMap>
<select id="selectUserRole" parameterClass="string" resultMap="roleResult" cacheModel="urCache">
select a.* from u_role a join u_associate b on a.id=b.rid and
b.lid=#value# order by a.sortNo, a.name
</select>
<select id="getUserFullName" parameterClass="java.util.HashMap" resultClass="string">
select
#userName#||getTreePathFunction('u_department',#deptCode#,'name','code') as
fullName from dual
</select>
</sqlMap>
注:{userName=name,deptCode=deptcode} 是为select="user.getUserFullName"所传递的参数。getTreePathFunction是
创建的一个oralce的function。
2.<resultMap/>的灵活使用
例如:用户和订单
<sqlMap namespace="customer">
<typeAlias alias="customer" type="com.test.domain.Customer"/>
<resultMap id="result_base" class="customer">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="address" column="address"/>
..........
</resultMap>
<resultMap id="result" class="customer" extends="result_base">
<result property="orderlist" column="id" select="orders.findByCustomerId"/>
</resultMap>
<select id="getById" parameterClass="long" resultMap="result_base">
select * from customer where id = #value#
</select>
<select id="getWithCashById" parameterClass="long" resultMap="result">
select * from customer where id = #value#
</select>
</sqlMap>
注:<resultMap/>标签有个extends属性,当只需要用户信息时,返回resultMap="result_base",如果还需要查看
该用户的订单信息时,可以返回resultMap="result",如果任何时候都返回resultMap="result",效率太低。
3.ibatis中N+1问题
例如:客户和信用卡:
<sqlMap namespace="UserCards">
<resultMap id="UserResult" class="User">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="cards" select="UserCards.getCardsByUserId" column="id"/>
</resultMap>
<resultMap id="CardResult" class="Card">
<result property="id" column="id" nullValue="0" />
<result property="cardName" column="cardName" />
<result property="balance" column="balance" nullValue="0" />
<result property="userId" column="userId" nullValue="0" />
</resultMap>
<select id="getUserById" resultMap="UserResult" parameterClass="int">
select * from user where id = #value#
</select>
</sqlMap>
上述写法会引起 N+1问题:
修改成:
<resultMap class="User" id="UserResult" groupBy="id">
<result property="id" column="id" nullValue="0"/>
<result property="name" column="name"/>
<result property="cards" resultMap="UserCards.CardResult" />
</resultMap>
<select id="getAllUsers" resultMap="UserResult">
select * from user a left join card b on a.id = b.userId
</select>
就可以解决该问题:
区别主要体现在groupBy="id"和<result property="cards" resultMap="UserCardsN1.CardResult" />及select用了表连接
控制台输出信息
例如:用户和角色
<sqlMap namespace="user">
<resultMap id="userResult" class="ur">
<result property="id" column="id" />
<result property="name" column="name" />
<result property="account" column="account" />
<result property="password" column="password" />
<result property="deptId" column="deptId" />
...........
<result property="roles" column="id" select="user.selectUserRole" />
<result property="fullName" column="{userName=name,deptCode=deptcode}" select="user.getUserFullName" />
</resultMap>
<select id="selectUserRole" parameterClass="string" resultMap="roleResult" cacheModel="urCache">
select a.* from u_role a join u_associate b on a.id=b.rid and
b.lid=#value# order by a.sortNo, a.name
</select>
<select id="getUserFullName" parameterClass="java.util.HashMap" resultClass="string">
select
#userName#||getTreePathFunction('u_department',#deptCode#,'name','code') as
fullName from dual
</select>
</sqlMap>
注:{userName=name,deptCode=deptcode} 是为select="user.getUserFullName"所传递的参数。getTreePathFunction是
创建的一个oralce的function。
2.<resultMap/>的灵活使用
例如:用户和订单
<sqlMap namespace="customer">
<typeAlias alias="customer" type="com.test.domain.Customer"/>
<resultMap id="result_base" class="customer">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="address" column="address"/>
..........
</resultMap>
<resultMap id="result" class="customer" extends="result_base">
<result property="orderlist" column="id" select="orders.findByCustomerId"/>
</resultMap>
<select id="getById" parameterClass="long" resultMap="result_base">
select * from customer where id = #value#
</select>
<select id="getWithCashById" parameterClass="long" resultMap="result">
select * from customer where id = #value#
</select>
</sqlMap>
注:<resultMap/>标签有个extends属性,当只需要用户信息时,返回resultMap="result_base",如果还需要查看
该用户的订单信息时,可以返回resultMap="result",如果任何时候都返回resultMap="result",效率太低。
3.ibatis中N+1问题
例如:客户和信用卡:
<sqlMap namespace="UserCards">
<resultMap id="UserResult" class="User">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="cards" select="UserCards.getCardsByUserId" column="id"/>
</resultMap>
<resultMap id="CardResult" class="Card">
<result property="id" column="id" nullValue="0" />
<result property="cardName" column="cardName" />
<result property="balance" column="balance" nullValue="0" />
<result property="userId" column="userId" nullValue="0" />
</resultMap>
<select id="getUserById" resultMap="UserResult" parameterClass="int">
select * from user where id = #value#
</select>
</sqlMap>
上述写法会引起 N+1问题:
修改成:
<resultMap class="User" id="UserResult" groupBy="id">
<result property="id" column="id" nullValue="0"/>
<result property="name" column="name"/>
<result property="cards" resultMap="UserCards.CardResult" />
</resultMap>
<select id="getAllUsers" resultMap="UserResult">
select * from user a left join card b on a.id = b.userId
</select>
就可以解决该问题:
区别主要体现在groupBy="id"和<result property="cards" resultMap="UserCardsN1.CardResult" />及select用了表连接
控制台输出信息