如:输入参数为简单类型String
<select id="queryIfByBase" resultType="shop">
select * from shop where 1=1
<!-- 如果输入参数不为null并且不为空就追加条件 -->
<if test="_parameter != null and _parameter != ''">
and shopname like '%${value}%'
</if>
</select>
_parameter:内置属性,表示接口中方法的参数。
___________________
如:输入参数为对象类型com.pojo.Shop
<select id="queryIfByReference" resultType="shop">
select * from shop where 1=1
<if test="_parameter != null">
<if test="shopName != null and shopName != ''">
and shopname like '%${shopName}%'
</if>
<if test="shopAddress != null and shopAddress != ''">
and shopaddress like '%${shopAddress}%'
</if>
</if>
</select>
shopName和shopAddress:表示对象Shop中的属性名。
7.动态sql(choose,when,otherwise)
动态choose,when,otherwise:类似于switch语法,匹配某个条件即停止向后判断,都不满足则执行otherwise条件。
<select id="queryChooseWhenOtherwise" resultType="shop">
select * from shop where 1=1
<choose>
<when test="shopName != null and shopName != ''">
and shopname like '%${shopName}%'
</when>
<when test="shopAddress != null and shopAddress != ''">
and shopaddress like '%${shopAddress}%'
</when>
<otherwise>
and contact like '%139%'
</otherwise>
</choose>
</select>
8.动态sql(where)
动态where:动态拼接and或or关键字。
<select id="queryWhere" resultType="shop">
select * from shop
<where>
<if test="_parameter != null">
<if test="shopName != null and shopName != ''">
and shopname like '%${shopName}%'
</if>
<if test="shopAddress != null and shopAddress != ''">
and shopaddress like '%${shopAddress}%'
</if>
</if>
</where>
</select>
9.动态sql(set)
动态set:动态拼接逗号(,)。
<update id="updateSet">
update shop
<set>
<if test="_parameter.shopName != null and _parameter.shopName != ''">
shopname=#{_parameter.shopName},
</if>
<if test="shopAddress != null and shopAddress != ''">
shopaddress=#{shopAddress},
</if>
<if test="contact != null and contact != ''">
contact=#{contact},
</if>
</set>
where shopid=#{shopId}
</update>
10.动态sql(foreach)
动态foreach:主要用于构建in条件,它可以在SQL语句中进行迭代一个集合。
属性详解:
collection:若接口中方法的参数类型是List则为list,数组为array,Map为键,实体为属性。
item:当前迭代的元素。
index:当前迭代的次数。
open:开始字符串。
close:结束字符串。
separator:分隔符。
迭代List:
<select id="queryForeachByList" resultType="shop">
select * from shop where shopid in
<foreach collection="list" item="sid" open="(" separator="," close=")">
#{sid}
</foreach>
</select>
迭代数组:
<select id="queryForeachByArray" resultType="shop">
select * from shop where shopid in
<foreach collection="array" item="sid" open="(" separator="," close=")">
#{sid}
</foreach>
</select>
迭代Map:
<select id="queryForeachByMap" resultType="shop">
select * from shop where shopid in
<foreach collection="shopIds" item="sid" open="(" separator="," close=")">
#{sid}
</foreach>
</select>
11.重用语句块(include)
sql:指定可重用的语句块。
<!-- 指定可重用的语句块 -->
<sql id="commSql">
<if test="_parameter != null">
<if test="shopName != null and shopName != ''">
and shopname like '%${shopName}%'
</if>
<if test="shopAddress != null and shopAddress != ''">
and shopaddress like '%${shopAddress}%'
</if>
</if>
</sql>
<select id="queryIfByReference" resultType="shop">
select * from shop where 1=1
<!-- 引用语句块 -->
<include refid="commSql"/>
</select>
<select id="queryWhere" resultType="shop">
select * from shop
<where>
<!-- 引用语句块 -->
<include refid="commSql"/>
</where>
</select>
12.调用存储过程
调用存储过程:
过程定义:
create or replace procedure proc_shop
(p_shopId shop.shopid%type,p_shopName out shop.shopname%type)
as
begin
select shopname into p_shopName from shop where shopid=p_shopId;
end;
SQL映射:
<select id="callProc" statementType="CALLABLE">
call proc_shop(#{shopId},#{shopName,mode=OUT,jdbcType=VARCHAR})
</select>
DAO接口:void callProc(Map<String, String> map);