mysql动态生成where条件 mysql动态标签

目录

前言：

一、动态SQL_<if><where>

二、动态SQL_<set>

三、动态SQL_<choose><when><otherwise>

四、动态SQL_<foreach> 遍历Array数组进行批量删除

五、动态SQL_ <foreach>遍历Collection集合进行批量插入

六、动态SQL_ <foreach>遍历Collection集合进行批量插入

---

前言：

一个查询的方法的 Sql 语句不一定是固定的。比如电商网站的查询商品，用户使用不同条件查询，Sql 语句就会添加不同的查询条件。此时就需要在方法中使用动态Sql 语句。

一、动态SQL_<if><where>

介绍：

<if> 标签内的 Sql 片段在满足条件后才会添加，用法为： <if test="条件"> 。

<where> 可以代替 sql 中的 where 1=1 和第一个 and ，更符合程序员的开发习惯

1.持久层接口查询方法

//测试MyBatis动态sql语句标签如：<if>、where、set、when、choose、otherwise、foreach
public interface UserMapper2 {

//    1.查询满足条件的所有数据，测试动态SQL的<if>标签
//    用途：测试<if><where>标签
    List<User> findByCondition(User user);
}

2.映射文件

<!--注意：这里的resultType、parameterType传入的是别名，在SqlMapConfig.xml中设置了-->

    <!--1.查询满足条件的所有数据，测试动态SQL的<if>标签-->
    <!--（1）where 1=1 条件是为了满足后面第一条if条件中的and，因为查询中第一个条件可能不符合，
            但不能影响后面的条件，所以把where 1=1作为第一个条件  如：
            select * from user where 1=1 <if></if>.....-->
    <!-- (2)<where>标签可以代替where 1=1 并且可以省去第一个条件中的and-->
    <!--(3)注意：username那里的模糊查询不能用'%${value}%',因为${}是字符串替换，用在普通类型的，而这里的参数类型为POJO类型-->
    <select id="findByCondition" resultType="User" parameterType="string">
        select * from user
        <where>
        <if test="username != null and username.length() != 0">
                username like #{username}
        </if>
        <if test="sex != null and sex.length() != 0">
            and sex = #{sex}
        </if>
        <if test="address != null and address.length() != 0">
            and address = #{address}
        </if>
        </where>
    </select>

3.测试方法

@Test
    public void testFindByCondition(){

        User user = new User();
//第一次查询，user对象是一个null对象，所以传入到sql中没有条件，调用where 1=1 查询所有数据
        List<User> users1 = userMapper2.findByCondition(user);
        users1.forEach(System.out::println);

        user.setUsername("%尚学堂%");
//第二次查询，user对象有了”username=尚学堂“的值，传入username的条件并查询满足username=尚学堂的条件
        List<User> users2 = userMapper2.findByCondition(user);
        users2.forEach(System.out::println);

        user.setAddress("北京");
//第三次查询，user对象有了”username=尚学堂、address=北京“的值，传入username、address的条件并查询满足条件的数据
        List<User> users3 = userMapper2.findByCondition(user);
        users3.forEach(System.out::println);
    }

4.注意：

（1）if 中的条件不能使用 &&/|| ，而应该使用 and/or

2

（2）if 中的条件可以直接通过属性名获取参数 POJO 的属性值，并且该值可以调用方法。

3

（3）where 后为什么要加 1=1 ？

         任意条件都可能拼接到Sql 中。如果有多个条件，从第二个条件开始前都需要加And 关键字。加上 1=1 这个永久成立的条件，就不需要考虑后面的条件哪个是第一个条件，后面的条件前都加And 关键字即可。

5.知识点整理：

（1）MyBatis的<if>标签使用“test”属性添加条件

（2）MyBatis的<if>中，多个条件同时满足需要用“and”连接，不能用&&

（3）MyBatis映射文件中，<where>的作用是“代替sql中where 1=1 和第一个and”

二、动态SQL_<set>

介绍：

<set> 标签用在update语句中。借助 <if> ，可以只对有具体值的字段进行更新。 <set> 会自动添加set关键字，并去掉最后一个if语句中多余的逗号。

1.持久层接口更新方法

// 2.动态更新数据，按给定的id主键值进行动态修改数据，可以只更新一行数据中的其中一个字段值，测试动态SQL的<set>标签 // 用途：测试<set>标签 void updateUser(User user);

2.映射文件

<!--2.动态更新数据，可以只更新一行数据中的其中一个字段值，测试动态SQL的<set>标签-->
    <!--<set>标签可以代替set（update user (set)<set>...）和最后一个条件后的逗号,
        如果没有<set>标签的话，最后一个if条件中要写上逗号（即address = #{address},）-->
    <update id="updateUser" parameterType="User">
        update user
        <set>
            <if test="username != null and username.length() !=0">
                username = #{username},
            </if>
            <if test="sex != null and sex.length() !=0">
                sex = #{sex},
            </if>
            <if test="address != null and address.length() !=0">
                address = #{address}
            </if>
        </set>
        <where>
            id = #{id}
        </where>
    </update>

3.测试方法

@Test
    public void testUpdateUser(){
        User user = new User();
        user.setId(2);
        user.setUsername("我是程序员");
        userMapper2.updateUser(user);
//        增删改操作记得要手动提交事务
        session.commit();
    }

4.知识点整理：

（1）MyBatis映射文件中，<set>的作用是“代替set关键字，并去掉最后一个if语句中多余的逗号”

三、动态SQL_<choose>、<when>、<otherwise>

介绍：

这些标签表示多条件分支，类似 JAVA 中的 switch...case 。 <choose> 类似

switch ， <when> 类似 case ， <otherwise> 类似 default ，用法如下：

1.持久层接口查询方法

//    3.查询满足条件的所有数据，测试动态SQL的<choose><when><otherwise>标签
//    按给定的内容进行模糊查询（内容小于5）或精确查询（内容小于10），否则返回id=1的数据（内容大于10）
//    用途：测试<choose><when><otherwise>标签，一般三个连着使用，相当于java中的Switch-case-default
    List<User> findByUsername(String username);

2.映射文件

<!--3.查询满足条件的所有数据，测试动态SQL的<choose><when><otherwise>标签-->
    <!--<choose><when><otherwise>相当于java的选择条件句，
            <choose>相当于Switch
             <when> 相当于case
        <otherwise> 相当于default-->
    <select id="findByUsername" parameterType="string" resultType="User">
        select * from user
        <where>
        <choose>
            <when test="username.length() < 5">
                <bind name="username" value="'%'+username+'%'"/>
                username like #{username}
            </when>
            <when test="username.length() < 10">
                username = #{username}
            </when>
            <otherwise>
                id = 1
            </otherwise>
        </choose>
        </where>
    </select>

这段代码的含义为：用户名 <5 时使用模糊查询，用户名 >=5 并且<10时使用精确查询，否则查询 id 为 1 的用户

3.测试方法

@Test
    public void testFindByUsername(){
//        精确查询username属性中含有北京的数据
        List<User> user = userMapper2.findByUsername("北京");
        user.forEach(System.out::println);
    }

4.知识点整理：

1.MyBatis 映射文件中， <choose> 的作用为“表示多条件分支”

2. 在 MyBatis 映射文件中 , <when> 需要被嵌套“ <choose> ”

四、动态SQL_<foreach> 遍历Array数组进行批量删除

介绍：

<foreach> 类似JAVA中的for循环，可以遍历集合或数组。 <foreach> 有如下属性：

collection ：遍历的对象类型
open ：表示该语句以什么开始，常用 “(”；
close ： 表示以什么结束，常用 “)”。
separator ：表示在每次进行迭代之间以什么符号作为分隔符，常用 “,”；
item ：表示集合中每一个元素进行迭代时的别名，随便起的变量名，遍历 map 时表示键值对的值。
index ：遍历 List 、数组时表示遍历的索引位置，遍历 map 时表示键值对的键。

遍历数组：

我们使用 <foreach> 遍历数组进行批量删除。

1.持久层接口删除方法

// 4.批量删除指定id的一些数据，测试动态SQL的<foreach>标签，遍历Array // 按给定的id值进行批量删除数据，传入一个数组，数组中包含要删掉的id值 // 系统的删除语句：delete from user where id in(12,13,14,16,17,18) // 用途：测试<foreach>标签，遍历Array数组 void deleteBatch(int[] ids);

2.映射文件

<!--4.批量删除指定id的一些数据，测试动态SQL的<foreach>标签，遍历Array-->
    <!-- collection:遍历的对象类型
               open:开始的sql语句
              close:结束的sql语句
          separator:遍历每项间的分隔符（自定义分隔符形式形式，如逗号、、、）
               item（名字自定义）:表示本次遍历获取的元素，遍历List、Set、Array时表示每项元素，遍历Map时表示键值对的值
              index:遍历List、Array时表示遍历的索引，遍历Map时表示键值对的键-->
    <delete id="deleteBatch" parameterType="int">
        delete from user
        <where>
            <foreach  collection="array" open="id IN(" close= ")" separator="," item="id">
                #{id}
            </foreach>
        </where>
    </delete>

3.测试方法

@Test
    public void testDeleteBatch(){
        int[] ids = {12,13,14,16,17,18};
        userMapper2.deleteBatch(ids);
        session.commit();
    }

4.知识点整理：

1. MyBatis 的 <foreach> 中，使用属性表示遍历的数据类型" item "

2. 在 MyBatis 映射文件中，遍历数组时需要将 collection 的值设为"array"

五、动态SQL_<foreach> 遍历Collection集合进行批量插入

介绍:

<foreach>  
  遍历 
  List 
  和 
  Set 
  的方法是一样的，这里使用  
  <foreach>  
  遍历 
  List集合 
  进 
 
 
行批量添加

1.持久层接口插入方法

//      5.批量插入数据，测试动态SQL的<foreach>标签，遍历Collection
//       系统插入语句：insert into user(username,sex,address) values(),(),()
//    批量新增方法，测试<foreach>标签，遍历Collection集合
    void insertBatch(List<User> user);

2.映射文件

<!--5.批量插入数据，测试动态SQL的<foreach>标签，遍历Collection-->
    <!--Collection：遍历的对象类型     item：遍历获取的元素（名字自定义，可以调用字段名进行传参） separator：分隔符-->
    <insert id="insertBatch" parameterType="User">
        insert into user(username,sex,address) values
        <foreach collection="list" item="user" separator=",">
            (#{user.username},#{user.sex},#{user.adddress})
        </foreach>
    </insert>

3.测试方法

@Test
    public void testInsertBatch(){
        User user1 = new User("程序员1","男","深圳");
        User user2 = new User("程序员2","女","上海");
        List<User> users = new ArrayList();
        users.add(user1);
        users.add(user2);

//        批量插入
        userMapper2.insertBatch(users);
        session.commit();
    }

4.知识点整理：

（1）在MyBatis中映射文件中，遍历List时<foreach>的item属性表示“遍历的每项元素”

（2）在MyBatis的<foreach>标签中，使用属性遍历每项间的分隔符“separator”

六、动态SQL_<foreach> 遍历Collection集合进行批量插入

介绍：

使用 <foreach>标签 遍历Map进行多条件查询。

1.持久层接口查询方法

//    6.多条件查询：批量查询方法，测试<foreach>标签，遍历Map集合
    /**
     * 多条件查询：批量查询方法，测试<foreach>标签，遍历Map集合
     * @param map 查询的条件键值对   键：属性名  值：出入要查询的属性名
     *            系统的查询语句：select * from user where username = ? and sex = ?
     * @return
     *              @Param：为参数起别名
     */
    List<User> findUser(@Param("queryMap") Map<String,Object> map);

2.映射文件

<!--6.多条件查询：批量查询方法，测试<foreach>标签，遍历Map集合-->
    <!--注意：1.collection：传入的不是遍历的对象类型，List、Set、Array传入的类型分别是list、set、array，
             但Map集合传入的不是map，而是持久层接口中定义的参数名 如：@Param("queryMap")
             2.separator：传入的是and不是逗号，因为查询给定的条件是and连接，修改是逗号连接
             3.index：Map类型的键，起名为key    item：Map类型的值，起名为value
             4.特别注意：sql语句key=value（键=值），键只能用${}，作为字符串拼接到sql语句中，因为键是属性名
                                               值可以用#{}，作为占位符-->
    <select id="findUser" parameterType="map" resultType="User">
        select * from user
        <where>
            <foreach collection="queryMap" separator="and" index="key" item="value">
                ${key} = #{value}
            </foreach>
        </where>
    </select>

3.测试方法

@Test
    public void testFindUser(){
        Map<String,Object> queryMap = new HashMap();
        queryMap.put("sex","男");
        queryMap.put("address","北京");
        List<User> users = userMapper2.findUser(queryMap);
        users.forEach(System.out::println);
    }

4.知识点整理：

（1）MyBatis 的 <foreach> 中，遍历 Map 时 index 属性表示“键值对的键”

（2） MyBatis 的 <foreach> 中，遍历 Map 时 item 属性表示“键值对的值”