动态sql修改、list参数查询、

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<update id="updateUser" parameterType="com.dy.entity.User">     update user         <set>             <if test="name != null">name = #{name},</if>             <if test="password != null">password = #{password},</if>             <if test="age != null">age = #{age},</if>         </set>         <where>             <if test="id != null">                 id = #{id}             </if>             and deleteFlag = 0;         </where> </update>

　　

2 foreach: 你有for, 我有foreach, 不要以为就你才屌！

java中有for, 可通过for循环， 同样， mybatis中有foreach, 可通过它实现循环，循环的对象当然主要是java容器和数组。

?

1 2 3 4 5 6 7 8 9

<select id="selectPostIn" resultType="domain.blog.Post">     SELECT *     FROM POST P     WHERE ID in     <foreach item="item" index="index" collection="list"         open="(" separator="," close=")">         #{item}     </foreach> </select>

　　

3 choose: 我选择了你，你选择了我！

Java中有switch, mybatis有choose。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

<select id="findActiveBlogLike"      resultType="Blog">     SELECT * FROM BLOG WHERE state = ‘ACTIVE’     <choose>         <when test="title != null">             AND title like #{title}         </when>         <when test="author != null and author.name != null">             AND author_name like #{author.name}         </when>         <otherwise>             AND featured = 1         </otherwise>     </choose> </select>

　　

4 动态SQL解析原理

我们在使用mybatis的时候，会在xml中编写sql语句。比如这段动态sql代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

<update id="update" parameterType="org.format.dynamicproxy.mybatis.bean.User">     UPDATE users     <trim prefix="SET" prefixOverrides=",">         <if test="name != null and name != ''">             name = #{name}         </if>         <if test="age != null and age != ''">             , age = #{age}         </if>         <if test="birthday != null and birthday != ''">             , birthday = #{birthday}         </if>     </trim>     where id = ${id} </update>

　　

6 where, 有了我，SQL语句拼接条件神马的都是浮云！

咱们通过where改造一下上面的例子：

?

1 2 3 4 5 6 7 8 9

<select id="findUserById" resultType="user">     select * from user         <where>             <if test="id != null">                 id=#{id}             </if>             and deleteFlag=0;         </where> </select>

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

<select id="findCountIdByDeskId" resultType="Integer">         SELECT count(id) FROM t_item         <trim prefix="WHERE" prefixOverrides="AND|OR">             <if test="deskId != null and deskId != ''">                 desk_id =  #{deskId}             </if> <!--             <if test="queueStatus != null"> --> <!--                 queue_status =  ${queueStatus} --> <!--             </if> -->             <if test="hisPatientType != null and hisPatientType != ''">                 his_patient_type =  #{hisPatientType}             </if>             AND queue_status =  ${queueStatus}             AND create_date > CURDATE()             AND status = 0         </trim>     </select>