MyBatis-Mapper文件之动态SQL

1. if标签

<if test="OGNL表达式">
    test为真的时候
</if>

eg:

<if test="id != null">
    id=#{id}
</if>

<if test="id != null and username.trim() != ''">
    id=#{id}
</if>

<if test="id == 0 or username.trim() != ''">
    id=#{id}
</if>

2. trim标签

prefix:前缀,给拼凑的sql语句加一个前缀
prefixOverrides:前缀覆盖,去掉前缀中多余的字符
suffix:给拼凑的sql语句加一个后缀
suffixOverrides:后缀覆盖,去掉后缀中多余的字符

<trim prefix="where" prefixOverrides="and" suffix="" suffixOverrides="and">
	<if test="id != null">
		id=#{id}
	</if>
	
	<if test="id != null and username.trim() != ''">
		and   id=#{id}
	</if>
	
	<if test="id == 0 or username.trim() != ''">
		id=#{id}
	</if>
</trim>

3. choose标签

<choose>
	<when test="id != null">
		id=#{id}
	</when>
	<when test="lastName != null">
		lastName=#{lastName}
	</when>
	<otherwise>
		1=1
	</otherwise>
</choose>

4. set标签
   如果结尾有一个逗号,set标签会帮你清除。

<set>
    <if test="id != null">
        id=#{id},
    </if>

    <if test="id != null and username.trim() != ''">
        id=#{id},
    </if>
    
    <if test="id == 0 or username.trim() != ''">
        id=#{id},
    </if>
</set>

5. where标签
   如果前面有and会帮你去掉and,但不能截取sql语句后面带上的and。

<where>
    <if test="id != null">
        id=#{id}
    </if>

    <if test="id != null and username.trim() != ''">
      and id=#{id}
    </if>
    
    <if test="id == 0 or username.trim() != ''">
      and id=#{id}
    </if>
</where>

6. foreach标签

原生SQL：

select * from user where id in(1,2,3)

改成MyBatis：

<foreach collection="ids" item="id" separator="," open="(" close=")">
    #{id}
</foreach>

当然foreach中还有index属性

• 当集合为List的时候 index表示的时候集合的索引
• 当集合为map的时候 index表示map的key 而item表示的是map的value
  foreach插入多个数据

格式：insert ... values
<foreach collection="users" item="use" separator="," >
   ( #{user.name},#{user.password} ...)
</foreach>

oracle批量插入：

<insert id="...">
    <foreach collection="users" item="user" open="begin" close="end">
        insert into xxxx values(#{user.xxx},#{user.yyy}...)
    </foreach>    
</insert>