bind (模糊查询,前后缀补充)
bind 标签是通过 OGNL 表达式去定义一个上下文的变量
SysUser sysUser1 = new SysUser();
sysUser1.setUserName("Kong");
List<SysUser> sysUsers = sysUserMapper.selectByUserLike(sysUser1);
select *
from sys_user
<bind name="userNameLike" value="'%'+userName+'%'"/>
where user_name like #{userNameLike}
<where>
where 1=1 的条件拼装
<if>
<if test="userCode != null and userCode != ''">
user_code = #{userCode,jdbcType=VARCHAR}
</if>
<if test="userName != null and userName.contains('Kon')">
and user_name = #{userName}
</if>
set
会自动去掉语句后面多余的逗号
<update id="updateInterface" parameterType="IntFTvForm">
UPDATE tab_omin_cm_cc_intf_tv
<set>
C_URL_PARAM = #{cUrlParam},
C_TABLE_NAME = #{cTableName},
</set>
WHERE C_INTF_TV_ID = #{cIntfTvId}
</update>
<foreach>
collection: 必填, 集合/数组/Map的名称
item: 变量名。 即从迭代的对象中取出的每一个值
index: 索引的属性名。 当迭代的对象为 Map 时, 该值为 Map 中的 Key
open: 循环开头的字符串
close: 循环结束的字符串
separator: 每次循环的分隔符
批量新增
Integer batchInsert(List<SysUser> sysUsers);
<insert id="batchInsert" >
insert into sys_user ( user_code, user_name, user_password, user_state)
values
<foreach collection="collection" item="user" separator=",">
(
#{user.userCode},
#{user.userName},
#{user.userPassword},
#{user.userState})
</foreach>
</insert>
IN
List<SysUser> selectByIdsAndUserName( List<Integer> ids, String userName);
<select id="selectByIdsAndUserName" resultType="com.du.bean.manytomany.SysUser">
select *
from sys_user
<where>
<foreach collection="ids" open="user_id in (" close=")" item="id" separator=",">
#{id}
</foreach>
and user_name=#{userName}
</where>
</select>
choose when otherwise
一个 choose 标签至少有一个 when, 最多一个otherwise
SELECT
<choose>
<when test="sqlColumns.size() > 0">
<foreach collection="sqlColumns" item="column" separator=",">
${column}
</foreach>
</when>
<otherwise>
*
</otherwise>
</choose>
FROM
${tableName}
<where>
<if test="sqlParams.size() > 0">
<foreach collection="sqlParams" item="map" separator=" ">
${map.c_logic} ${map.c_param_key} ${map.c_param_operator}
<choose>
<when test="map.c_value_prefix != null and map.c_value_suffix == null">
<bind name="pattern" value="map.c_value_prefix + map.value" />
#{pattern}
</when>
<when test="map.c_value_prefix == null and map.c_value_suffix != null">
<bind name="pattern" value="map.value + map.c_value_suffix" />
#{pattern}
</when>
<when test="map.c_value_prefix != null and map.c_value_suffix != null">
<bind name="pattern" value="map.c_value_prefix + map.value + map.c_value_suffix" />
#{pattern}
</when>
<otherwise>
#{map.value}
</otherwise>
</choose>
</foreach>
</if>
</where>
ORDER BY version DESC, c_updated_date DESC
Trim
prefix: 当 trim 元素包含有内容时, 增加 prefix 所指定的前缀
prefixOverrides: 当 trim 元素包含有内容时, 去除 prefixOverrides 指定的 前缀
suffix: 当 trim 元素包含有内容时, 增加 suffix 所指定的后缀
suffixOverrides: 当 trim 元素包含有内容时, 去除 suffixOverrides 指定的后缀
<update id="upadteBlogByConditionTrim" parameterType="Blog">
UPDATE
`blog`
<trim prefix="set" suffixOverrides=",">
author_id = #{authorId},
</trim>
WHERE `id` = #{id}
</update>
<select id="selectBlogByConditionTrim" parameterType="Blog" resultMap="blogResultMap">
select * from blog
<trim prefix="where" prefixOverrides="and | or">
lower(title) like lower(#{title})
</trim>
</select>