if语法
<select id="findexistbookset" resulttype="int"
parametertype="accountsadjustaccountset002dto">
select count(1) from bok_bookset
where status='e'
and bookset_name = #{booksetname}
<if test="pagemodel eq modifymodel">
and bookset_id != #{booksetid}
</if>
</select>
select
nvl(sum(prin_amt),0) prin_amt
from
sec_org_deal
where
status in ('a','e')
and entrust_id = #{entrustid}
<if test="workflowid != null and workflowid != ''">
and workflow_id != #{workflowid}
</if>
choose, when, otherwise语法
select asset_id assetid,
sec_id secid,
isin_id isinid,
sec_abbr secabbr,
sec_name secname,
int_rule_id intruleid,
reset_rule_id resetruleid,
update_time updatetime
from
<choose>
<when test="tempstorage == '01'">
sec_info_tmp t
</when>
<otherwise>
<if test="hisno != null and hisno != ''">
sec_info_his t
</if>
<if test="hisno == null or hisno == ''">
sec_info t
</if>
</otherwise>
</choose>
where, set语法，解决首尾问题
select
subject_no id,
subject_id object,
subject_name text,
'0'||subject_level as col
from
bok_subject
<where>
<if test="booksetid != null and booksetid != ''">
and bookset_id = #{booksetid}
</if>
and status = 'e'
</where>
update wfl_agent
<set>
<if test="startdate != null and startdate != ''">
start_date = #{startdate,jdbctype=date},
</if>
<if test="agenttype != null and agenttype != ''">
agent_type = #{agenttype,jdbctype=varchar},
</if>
<if test="agentid != null and agentid != ''">
agent_id = #{agentid,jdbctype=varchar},
</if>
<if test="workflowid != null and workflowid != ''">
workflow_id = #{workflowid,jdbctype=varchar}
</if>
</set>
where user_id = #{userid,jdbctype=varchar}
and start_date = #{startdatetoupdate,jdbctype=date}
最强大的foreach语句 collection是参数，一般是list 或者数组 item是迭代的元素
用于批量insert
<insert id="insertsjyzprojassetinfo" parametertype="java.util.list">
insert into sjyz_proj_asset_info (
proj_asset_info_asset_id
,proj_asset_info_asset_name
,proj_asset_info_financing_size
,proj_asset_info_ccy
,inv_rate_quo_update_time
)
values
<foreach collection="list" item="info" index="index" open="" close="" separator=",">
(
#{info.proj_asset_info_asset_id,jdbctype=varchar}
,#{info.proj_asset_info_asset_name,jdbctype=varchar}
,#{info.proj_asset_info_financing_size,jdbctype=decimal}
,#{info.proj_asset_info_ccy,jdbctype=varchar}
,#{info.inv_rate_quo_update_time,jdbctype=timestamp}
)
</foreach>
</insert>
用于查询，多个参数实现in
select null as hisno,
t.workflow_id workflowid,
t.asset_id as assetid,
t.sec_id as secid,
t.sec_abbr secabbr,
t.sec_type sectype
from sec_info_tmp t
where
t.create_user = #{loginuserid}
<if test="assetid != null and assetid != ''">
and t.sec_id like '%'||#{assetid}||'%'
</if>
<if test="couponspecies != null and couponspecies.size() > 0">
and t.coupon_species in
<foreach item="item" collection="couponspecies" index="index"
open="(" separator="," close=")">
'${item}'
</foreach>
</if>
select asset_id
from proj_asset_info
where status in ('a','p')
<if test="assetids != null and assetids.length > 0">
and asset_id in
<foreach item="item" collection="assetids" index="index"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
include语法 静态包含
<select id="selectsecinfolist" resulttype="java.lang.integer">
<include refid="secinfolist"/>
</select>
<sql id = "secinfolist">
select distinct
secinfo.hisno,
secinfo.workflowid,
secinfo.assetid,
secinfo.secid,
secinfo.secabbr,
secinfo.sectype,
secinfo.vdate
secinfo order by secinfo.assetid, secinfo.workflowid desc
</sql>