动态SQL常用标签

一、

• select

<select id="selectSysJobguidList" parameterType="SysJobguid" resultMap="SysJobguidResult">
        <include refid="selectSysJobguidVo"/>
        <where>  
            <if test="jobguidName != null  and jobguidName != ''"> and jobguid_name like concat('%', #{jobguidName}, '%')</if>
            <if test="jobguidAuthor != null  and jobguidAuthor != ''"> and jobguid_author = #{jobguidAuthor}</if>
        </where>
    </select>

• insert

 <insert id="insertSysJobguid" parameterType="SysJobguid" useGeneratedKeys="true" keyProperty="jobguidId">
        insert into sys_jobguid
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="jobguidName != null  and jobguidName != ''">jobguid_name,</if>
            <if test="jobguidAuthor != null  and jobguidAuthor != ''">jobguid_author,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="jobguidName != null  and jobguidName != ''">#{jobguidName},</if>
            <if test="jobguidAuthor != null  and jobguidAuthor != ''">#{jobguidAuthor},</if>
         </trim>
    </insert>

• update set

<update id="updateDictType" parameterType="SysDictType">
 		update sys_dict_type
 		<set>
 			<if test="dictName != null and dictName != ''">dict_name = #{dictName},</if>
 			<if test="dictType != null and dictType != ''">dict_type = #{dictType},</if>
 			update_time = sysdate()
 		</set>
 		where dict_id = #{dictId}
	</update>

• delete

    <delete id="deleteSysJobguidById" parameterType="Long">
        delete from sys_jobguid where jobguid_id = #{jobguidId}
    </delete>

二、

• if标签
  if 标签通常用于 WHERE 语句、UPDATE 语句、INSERT 语句中，通过判断参数值来决定是否使用、更新、插入某个字段的值。

• where标签
  如果where标签内容以and或or开头，会自动剔除第一个and或or

• trim标签
  trim是一个格式化的标记，可以完成set或者是where标记的功能。
  相关属性有：

Prefix：前缀。
prefixOverrides：去掉第一个指定内容。
suffix：后缀。
suffixoverride：去掉最后一个指定内容。

 <update id="updateSysJobguid" parameterType="SysJobguid">
        update sys_jobguid
        <trim prefix="SET" suffixOverrides=",">
            <if test="jobguidName != null  and jobguidName != ''">jobguid_name = #{jobguidName},</if>
            <if test="jobguidAuthor != null  and jobguidAuthor != ''">jobguid_author = #{jobguidAuthor},</if>
        </trim>
        where jobguid_id = #{jobguidId}
    </update>

insert id="insertSysJobguid" parameterType="SysJobguid" useGeneratedKeys="true" keyProperty="jobguidId">
        insert into sys_jobguid
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="jobguidName != null  and jobguidName != ''">jobguid_name,</if>
            <if test="jobguidAuthor != null  and jobguidAuthor != ''">jobguid_author,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="jobguidName != null  and jobguidName != ''">#{jobguidName},</if>
            <if test="jobguidAuthor != null  and jobguidAuthor != ''">#{jobguidAuthor},</if>
         </trim>
    </insert>

• foreach标签
  foreach用来遍历，遍历的对象可以是数组，也可以是集合。（多用于批量删除）

相关属性：
Collection：collection属性的值有三个分别是list、array、map三种。
Open：前缀。
Close：后缀。
Separator：分隔符，表示迭代时每个元素之间以什么分隔。
Item：表示在迭代过程中每一个元素的别名。
Index：用一个变量名表示当前循环的索引位置。

<delete id="deleteSysJobguidByIds" parameterType="String">
        delete from sys_jobguid where jobguid_id in 
        <foreach item="jobguidId" collection="array" open="(" separator="," close=")">
            #{jobguidId}
        </foreach>
    </delete>

• choose when… otherwise标签

1. 有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个时使用。
2. 当 choose 中所有 when的条件都不满则时，则执行 otherwise 中的 sql。
3. 类似于 Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。
4. if 是与(and)的关系，而 choose 是或（or）的关系。

<select id="getStudentListChoose" parameterType="Student" resultMap="BaseResultMap">
    SELECT * from STUDENT 
         <where>
             <choose>
					<when test="Name!=null and student!='' ">
						AND name LIKE CONCAT(CONCAT('%', #{student}),'%')
					</when>
					<when test="hobby!= null and hobby!= '' ">
						AND hobby = #{hobby}
					</when>
					<otherwise>
						AND AGE = 15
					</otherwise>
			</choose>
		</where>
</select>

• include标签
  重用语句块

 <sql id="selectSysJobguidVo">
        select jobguid_id, jobguid_name, jobguid_author, jobguid_posttime, jobguid_source, jobguid_ctr, jobguid_del, jobguid_top from sys_jobguid
    </sql>

    <select id="selectSysJobguidList" parameterType="SysJobguid" resultMap="SysJobguidResult">
        <include refid="selectSysJobguidVo"/>
        <where>  
            <if test="jobguidName != null  and jobguidName != ''"> and jobguid_name like concat('%', #{jobguidName}, '%')</if>
            <if test="jobguidAuthor != null  and jobguidAuthor != ''"> and jobguid_author = #{jobguidAuthor}</if>
        </where>
    </select>