ibatis3 Dynamic SQL

1.sql 将常用sql抽取出来，在需要的地方includ

<sql id="bankColumns"> id,bankAccount,bankName</sql>

<select id="selectAll" resultMap="BankMap">
select
<include refid="bankColumns" />
from bank
</select>


2.if 用于判断
<select id="select" parameterType="Bank" resultMap="BankMap">
select
<include refid="bankColumns" />
from bank
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="bankName != null"> bankName = #{bankName}</if>
<if test="bankAccount != null">and bankAccount = #{bankAccount}</if>
</trim>
<!--
<where> <choose> <when test="bankName != null"> and bankName =
#{bankName} </when> <when test="bankAccount != null"> and bankAccount
= #{bankAccount} </when> <otherwise> and 1=1 </otherwise> </choose>
</where>
-->
</select>

3.choose用于选择
<select id="select" parameterType="Bank" resultMap="BankMap">
select
<include refid="bankColumns" />
from bank
<where>
<choose>
<when test="bankName != null">
and bankName = #{bankName}
</when>
<when test="bankAccount != null"> and bankAccount
= #{bankAccount} </when>
<otherwise> and 1=1 </otherwise>
</choose>
</where>
</select>

4.foreach用于遍历循环
<select id="selectBank" resultMap="BankMap" parameterType="list"
useCache="true" flushCache="false">
select
<include refid="bankColumns" />
from bank where id in

<foreach index="index" item="item" collection="list" open="("
separator="," close=")">
#{item}
</foreach>
</select>

note:
数组类型，在配置文件用数组的组成类型。
如果传入的参数是数组, 系统会自动将参数包装成map,name为“array”
如果传入的参数是list,系统会自动将参数包装成map,name为“list”
如果传入的参数是map, 系统会自动将参数包装成map,name为“list”
作为collection的参数


5.set 用于更新
<update id="update" parameterType="Bank">
update bank
<set>
<if test="bankName != null"> bankName = #{bankName},</if>
<if test="bankAccount != null"> bankAccount = #{bankAccount},</if>
</set>
where id=#{id}
</update>

note：where,set动态产生sql关键字，
where 会去掉where 后面的and 和or.
set 会去掉set后面的,号