mybatis动态SQL

动态SQL

if：根据条件判断是否拼接内部sql
where：根据where其后是否有sql，判断拼接 where

<select id="findUser" resultType="com.shang.pojo.User">
   select * from user 
   <where>
     <if test="id!=null and id!=''">
	    id = #{id}
	 </if>
     <if test="name!=null and name!=''">
	    and name = #{name}
	 </if>
   </where>
</select>
复制代码

choose：类似于Java中的switch。只进入一个满足when的条件，如果所有when都不满足，则进入otherwise

<select id="findUser" resultType="com.shang.pojo.User">
   select * from user
   <choose>
      <when test="id!=null and id!='' and name!=null and name!=''">
	      id = #{id} and name = #{name}
	  </when>
	  <when test="id!=null and id!=''">
	      id = #{id}
	  </when>
	  <otherwise>
	      name = #{name}
	  </otherwise>
   </choose>
</select>
复制代码

set与where有相似之处，其后如果存在条件，则拼接set。适用于update

<update id="updateUser">
   update user 
   <set>
	   <if test="name!=null and name!=''">
		  name = #{name},
	   </if>
	   <if test="age!=null and age!=''">
		  age = #{age}
	   </if>
   </set>
   where id = #{id}
</update>
复制代码

foreach：可以遍历List、Map、Array

• collection：指定需要遍历的元素
• item：遍历之后的每一项
• separator：定义foreach里面语句的分隔符
• index：map中代表key，数组中代表数组下标

<select id="findAll" resultType="com.shang.pojo.User">
    select * from user where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
       #{id}
    </foreach>
</select>

<insert id="batchInsertUser">
    insert into user(id,name,age)
    values
    <foreach collection="users" item="user" separator=",">
       (#{user.id},#{user.name},#{user.age})
    </foreach>
</insert>

<update id="updateUser">
    update user
	<set>
		<foreach collection="map" item="val" index="key" separator=",">
       		${key}=#{val}
    	</foreach>
	</set>
	where id = #{id}
</update>
复制代码

bind：用来定义变量

<select id="findUserById" resultType="com.shang.pojo.User">
    <bind name="userName" value="name+'%'"/>
    select * from user where name = #{userName}
</select>
复制代码