一、动态sql的简述
什么是动态sql:在不同条件下拼接不同的sql
Mybatis框架的动态sql技术是一种根据特定条件动态拼接SQl语句的功能,他存在的意义是为了解决拼接SQL语句字符串时的痛点问题。比如我们在用淘宝之类的软件在进行商品属性选择的时候,我们会发现我们可以选择的商品的属性有很多条件,其中一些条件可以选择也可以不选择,那么如果使用传统的方式进行查询,反而在拼接sql的时候会造成一些列的问题。
二、动态sql的使用案例
-
<if标签
根据 username 和 sex 来查询数据。如果username为空,那么将只根据sex来查询;反之只根据username来查询
首先先不使用动态sql:<select id="selectUserByUsernameAndSex" parameterType="com.qcby.entity.User" resultType="com.qcby.entity.User"> select * from user where username = #{username} and sex = #{sex} </select>
上面的查询语句,我们可以发现,如果 #{username} 为空,那么查询结果也是空,如何解决这个问题呢?使用 if 来判断
使用动态sql:<select id="selectUserByUsernameAndSex" parameterType="com.qcby.entity.User" resultType="com.qcby.entity.User"> select * from user where <if test="username != null and username = ''"> username=#{username} </if> <if test="sex != null and sex !=''"> and sex=#{sex} </if> </select>
从上边的案例当中我们可以看出如果 sex 等于 null,那么查询语句为 select * from user where username=#{username},如果
username等于null,那么查询语句为select * from user where and sex=#{sex} 那这显然是不对的。
2. where if标签<select id="selectUserByUsernameAndSex" parameterType="com.qcby.entity.User" resultType="com.qcby.entity.User"> select * from user <where> <if test="username != null"> username=#{username} </if> <if test="sex != null"> and sex=#{sex} </if> </where> </select>
但是我们加上了 标签,如果username为空怎么办?sex当中的and会不会受影响?
从上图的案例当中我们可以看到如果标签返回的内容是以AND 或OR 开头的,则它会剔除掉。
3. set if标签 ------ 用来组装update语句
Dao层/** * 修改 * @param user * @return */ public int update(User user);
mapping
<update id="update" parameterType="com.qcby.entity.User"> update user <set> <if test="username !=null and username!=''"> username = #{username} , </if> <if test="address != null and address != ''"> address = #{address} </if> </set> where id = #{id} </update>
测试
@Test public void update(){ User user = new User(); user.setId(6); user.setSex("女"); int code = mapper.updateByDo(user); session.commit(); System.out.println(code); }
- choose、when和otherwise标签
这个标签相当于是我们java当中的if…elseif…else
choose标签是这个标签组合当中的父标签when>和othrtwise标签都在choose标签内部。
when 标签就相当于是我们的 if 和 elseif
othrtwise标签相当于是我们的 else
<select id="selectUserByChoose" resultType="com.qcby.entity.User" parameterType="com.qcby.entity.User"> select * from user <where> <choose> <when test="id !='' and id != null"> id=#{id} </when> <when test="username !='' and username != null"> and username=#{username} </when> <otherwise> and sex=#{sex} </otherwise> </choose> </where> </select>
- trim标签
trim标记是一个格式化的标记,可以完成set或者是where标记的功能.
prefix:前缀
prefixoverride:去掉第一个and或者是or
①、用 trim 改写上面第二点的 where + if 语句
<select id="selectUserByUsernameAndSex" parameterType="com.qcby.entity.User" resultType="com.qcby.entity.User"> select * from user <!-- <where>--> <!-- <if test="username != null">--> <!-- username=#{username}--> <!-- </if>--> <!-- <if test="sex != null">--> <!-- and sex=#{sex}--> <!-- </if>--> <!-- </where>--> <trim prefix="where" prefixOverrides="and | or"> <if test="username != null"> and username=#{username} </if> <if test="sex != null"> and sex=#{sex} </if> </trim> </select>
②、用 trim 改写上面第三点的 if+set 语句
<update id="update" parameterType="com.qcby.entity.User"> update user <!-- <set>--> <!-- <if test="username !=null and username!=''">--> <!-- username = #{username} ,--> <!-- </if>--> <!-- <if test="address != null and address != ''">--> <!-- address = #{address}--> <!-- </if>--> <!-- </set>--> <trim prefix="set" suffixOverrides=","> <if test="username != null and username != ''"> username = #{username}, </if> <if test="sex != null and sex != ''"> sex = #{sex}, </if> </trim> where id = #{id} </update>
6、foreach标签
有些时候我们的数据是以数组的形式出现的,比如我们进行批量删除和批量添加的时候
①:批量删除<!-- 批量删除的sql语句:delete from user where id in (1,2,3,4,5); --> <delete id="deleteMoreByArray"> delete from user where id in <foreach collection="ids" item="id" separator="," open="(" close=")"> #{id} </foreach> </delete> <!-- collection:当前要循环的数组或者集合 --> <!-- item: 我们指定要循环的数组的每一个元素 --> <!-- separator:每一个元素应该用什么来做分割 --> <!-- open:当前循环是以什么开始 --> <!-- close是以什么结束 -->
/** * 通过数组批量删除 * @param ids * @return */ int deleteMoreByArray(@Param("ids") Integer[] ids);
@Test public void deleteMoreByArray(){ int result = mapper.deleteMoreByArray(new Integer[]{1,2,3}); session.commit(); System.out.println(result); }
②:批量添加
<insert id="insertMoreByList" > insert into user(id,username,birthday,sex,address) values <foreach collection="users" item="user" separator=","> (null,#{user.username},#{user.birthday},#{user.sex},#{user.address}) </foreach> </insert>
/** * 通过集合批量添加 * @param users * @return */ int insertMoreByList(@Param("users") List<User> users);
@Test public void insertMoreByList(){ User user1 = new User("a1","男","北京"); User user2 = new User("a2","男","北京"); User user3 = new User("a3","男","北京"); List<User> users = Arrays.asList(user1,user2,user3); int result = mapper.insertMoreByList(users); session.commit(); System.out.println(result); }
- choose、when和otherwise标签