mybatis学习 (五) 动态sql 入门

逃离我推掉我的手 2022-05-29 00:20 307阅读 0赞

使用动态sql可以很方便的进行sql语句的选择拼接和迭代参数, 不需要在java代码中进行操作

### if ###

有这样一个需求， 当用户查询时，若查询值为空则显示全部内容， 若有查询值， 则按照此值来查找，下面使用if来实现  
接口方法如下：

public List<Student> getStudent(@Param("stuName") String stuName);

xml如下, 当stuName不为空时才进行模糊查， 若为空则查全部

<select id="getStudent" parameterType="string" resultType="student">
                SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
                FROM student WHERE 1 = 1
                <if test="stuName != null and stuName != ''">
                    AND stu_name like concat('%',#{stuName},'%')
                </if>
            </select>

### choose ,when, otherwise ###

类似java 的switch ， 它里面when相当与case , otherwise相当于default

现在有一个需求，若学生id不为空则按id查， 若id为空姓名不为空则按学生姓名查， 否则都不查

<select id="getStudent" parameterType="student" resultType="student">
                SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
                FROM student WHERE 1=1
                <choose>
                    <when test="stuId != null">
                        AND stu_id = #{stuId}
                    </when>
                    <when test="stuName != null">
                        AND stu_name like concat('%',#{stuName},'%')
                    </when>
                    <otherwise>
                        AND  1=0
                    </otherwise>
                </choose>
            </select>

### where ###

看上面的语句中都有一个WHERE 1=1 , 这是必要的 , 看下面语句，把WHERE 1=1去掉

如果都不成立 语句变成

> SELECT \* FROM student where

如果只有第二个成立

> SELECT \* FROM BLOG WHERE AND title like \#\{title\}

显然这样就不对了 , 用上1=1就是解决这个毛病的， 而where则是可以取代1=1的功能，如下

<select id="findActiveBlogLike"
         resultType="Blog">
      SELECT * FROM BLOG 
      WHERE 
     <if test="stuId != null">
         and stu_id = #{stuId}
     </if>
     <if test="stuName != null">
         AND stu_name like #{stuName}
     </if>
    </select>

where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。  
用法如下

<select id="getStudent" parameterType="student" resultType="student">
                SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
                FROM student
                <where>
                    <if test="stuId != null">
                        and stu_id = #{stuId}
                    </if>
                    <if test="stuName != null">
                        AND stu_name like #{stuName}
                    </if>
                </where>
            </select>

### trim ###

用来添加前缀和去掉指定前缀，也就是说第一个AND存在并不影响， prefixOverrides会自动的将他去掉

<select id="getStudent" parameterType="student" resultType="student">
                SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
                FROM student
                <trim prefix="WHERE" prefixOverrides="AND">
                    <if test="stuId != null">
                        AND stu_id = #{stuId}
                    </if>
                    <if test="stuName != null">
                        AND stu_name like #{stuName}
                    </if>
                </trim>
            </select>

### set ###

用于更新，被set包裹到的地方会自动去掉末尾的 ‘, ’ ，所以不用担心下面语句都好问题

<update id="updateStudent" parameterType="student"  >
            UPDATE student
            <set>
                <if test="stuName != null">
                    stu_name=#{stuName},
                </if>
                <if test="stuAge != null">
                    stu_age=#{stuAge},
                </if>
            </set>
            WHERE stu_id=#{stuId}
        </update>

可以用trim代替set, 如下： 加上前缀’SET’, 去掉后缀’,’

<trim prefix="SET"  suffixOverrides=",">
                <if test="stuName != null">
                    stu_name=#{stuName},
                </if>
                <if test="stuAge != null">
                    stu_age=#{stuAge},
                </if>
            </trim>
            WHERE stu_id=#{stuId}

### foreach ###

foreach 可以遍历一个集合，数组，Map ， 有时候通过多选框选择多个学生信息进行删除操作， 就可以将学生的id放集合中传入到sql中。 其中当collection为Map时 index表示key， item表示value  
接口方法

public Integer deleteByStuId(@Param("stuIds") List<Integer> stuIds);

xml

<delete id="deleteByStuId" parameterType="list">
            DELETE FROM student WHERE stu_id in
            <foreach item="item" index="index" collection="stuIds"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </delete>

测试

List<Integer> stuIds = new ArrayList<Integer>();
         stuIds.add(5);
         stuIds.add(7);
         stuIds.add(8);
         studentMapper.deleteByStuId(stuIds);
         sqlSession.commit();

### bind ###

可以从OGNL表达式中创建一个变量并绑定到上下文中。  
如下需要进行模糊查询姓’王’ 的学生

<select id="getStudentByName" parameterType="string" resultType="student">
                <bind name="pattern" value="'%' + stuName + '%'" />
                SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
                FROM student
                WHERE stu_name like #{pattern}
            </select>