JavaWeb-动态SQL

最新推荐文章于 2024-04-16 10:00:21 发布

有此一人

最新推荐文章于 2024-04-16 10:00:21 发布

阅读量519

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_73675796/article/details/132005024

版权

5.4 动态SQL

随着用户的输入或外部条件的变化而变化的SQL语句，称作动态SQL。之前写的代码中，查询的条件是固定死的，只要有一项不满足就会查不到数据。

 <select id = "list" resultType = "com.itheima.pojo.Emp">		
    select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and entrydate between #{begin} and #{end} order by update_time desc 
    </select>

//比如下面的方法，尽管要查询的表中只有一个人的名字含有“张”，但由于后面的字段为null，无法匹配。所以查询不到结果。

@Test
    public void testList(){
        List<Emp> empList = empMapper.list("张", null, null, null);
        System.out.println(empList);
    }

此时，我们需要用到动态SQL。

5.4.1 if标签

<!-- 
 <if>:用于条件判断。使用test属性进行判断，如果条件为true，则拼接SQL。比如下面的例子
如果name不是null，就执行下面的SQL语句。
 -->

<if test = "name!=null">
 name like concat('%',#{name},'%')
</if>

<!-- 用if标签改造后：-->
<select id="list" resultType="com.itheima.pojo.Emp">
        select *
     	from emp
     	where
            <if test="name != null">
                name like concat('%', #{name}, '%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        order by update_time desc
    </select>

在这里插入图片描述

//但是如果执行下面的方法，将会报错
@Test
    public void testList(){
        List<Emp> empList = empMapper.list(null, (short)1, null, null);
        System.out.println(empList);
    }

可以看看哪里报了个错：

在这里插入图片描述

报错的位置靠近“and gender = 1”，我们再来看下xml代码，发现如果执行了上面的方法，那么SQL语句中就多了个and，因为我们将name赋值为null，在xml的if标签中，就会跳过 name like concat(‘%’, #{name}, ‘%’)，转而执行 and gender = #{gender}，显然在and前面没有连接的字段，出现了语法错误。但是我们又不能将and删去，删去之后 ==List empList = empMapper.list(“Tom”, (short)1, null, null);==又会报错。此时需要想办法解决。

<select id="list" resultType="com.itheima.pojo.Emp">
        select *
     	from emp
     	where
            <if test="name != null">
                name like concat('%', #{name}, '%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        order by update_time desc
    </select>

5.4.2 where标签

XML提供了where标签替换where关键字来解决上面的问题。

where标签的作用：

根据where中的子标签动态判断条件，如果子标签里的条件都不成立，就不会生成where关键字。如果有一个条件成立，就会生成where关键字。
去掉sql语句中多余的 and 或 or

<select id="list" resultType="com.itheima.pojo.Emp">
        select *
     	from emp
     	<where>
            <if test="name != null">
                name like concat('%', #{name}, '%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
    	</where>
        order by update_time desc
    </select>

5.4.3 set标签

对于下面的代码，在执行更新操作时，如果有些字段没有赋值的话，在表中的对应字段会变成null。

 @Test
    public void testUpdate2(){
  //构造员工对象
  	Emp emp = new Emp();
  	emp.setId(19);
  	emp.setUsername("Tom222");
    emp.setName("汤姆222");
    emp.setGender((short)1);
    emp.setUpdateTime(LocalDateTime.now());

  //执行更新员工操作
     empMapper.update2(emp);
   }

在这里插入图片描述

在XML中使用if标签就可以很好地解决。

 <update id="update2">
        update emp
        set
            <if test="username != null">username = #{username},</if>
            <if test="name != null">name = #{name},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="image != null">image = #{image},</if>
            <if test="job != null">job = #{job},</if>
            <if test="entrydate != null">entrydate = #{entrydate},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="updateTime != null">update_time = #{updateTime}</if>
        where id = #{id}
    </update>

但是又遇到一个问题，如果赋值语句是下面的的话，又会出现语法错误

@Test
    public void testUpdate2(){
  //构造员工对象
  	Emp emp = new Emp();
  	emp.setId(19);
  	emp.setUsername("Tom222333");
  //执行更新员工操作
     empMapper.update2(emp);
   }

在这里插入图片描述

结果发现 ==where id === 的前面多了个逗号。

在这里插入图片描述

我们再来看一眼XML配置文件中的代码。我们这次只修改了第一个字段，所以只执行了第一个if标签中的SQL语句，后面if标签中的SQL语句就直接略过了，因此会执行到 username = #{username},，就是这里多了个逗号。

<update id="update2">
        update emp
        set
            <if test="username != null">username = #{username},</if>
            <if test="name != null">name = #{name},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="image != null">image = #{image},</if>
            <if test="job != null">job = #{job},</if>
            <if test="entrydate != null">entrydate = #{entrydate},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="updateTime != null">update_time = #{updateTime}</if>
        where id = #{id}
    </update>

XML也提供了set标签来代替set关键字解决这个问题。

set标签作用：

将所有字段进行包裹
去除多余的逗号（用在update中）

 <update id="update2">
        update emp
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="name != null">name = #{name},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="image != null">image = #{image},</if>
            <if test="job != null">job = #{job},</if>
            <if test="entrydate != null">entrydate = #{entrydate},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="updateTime != null">update_time = #{updateTime}</if>
        </set>
        where id = #{id}
    </update>

5.4.4 foreach标签（批量操作）

SQL语句

delete from emp where id in (18,19,20);

接口

    public void deleteByIds(List<Integer> ids);

XML

<!--批量删除员工 (18,19,20)-->
    <!--
        collection: 遍历的集合
        item: 遍历出来的元素
        separator: 分隔符
        open: 遍历开始前拼接的SQL片段
        close: 遍历结束后拼接的SQL片段
    -->
    <delete id="deleteByIds">
        delete  from emp where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

单元测试

 //批量删除员工 - 18, 19, 20
   @Test
   public void testDeleteByIds(){
       List<Integer> ids = Arrays.asList(18, 19, 20);
       empMapper.deleteByIds(ids);
   }

5.4.5 sql、include标签

sql标签：定义可重用的SQL片段
include标签：通过属性refid，指定包含的sql片段

面对以下的代码，如果要修改表名或者字段名的话，则在两个select标签中都要修改，这就导致代码的复用性差。

在这里插入图片描述

XML文件提供了解决方案。

 <select id="list" resultType="com.itheima.pojo.Emp">
     <!-- 将refid属性指定的sql片段引用进来 -->
        <include refid="commonSelect"/>
        <where>
            <if test="name != null">
                name like concat('%', #{name}, '%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
        order by update_time desc
    </select>

<!-- 给sql片段指定唯一标识，用于include引用 -->
<sql id="commonSelect">
        select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time
        from emp
    </sql>

在这里插入图片描述

有此一人

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
JavaWeb-动态SQL

报错的位置靠近“and gender = 1”，我们再来看下xml代码，发现如果执行了上面的方法，那么SQL语句中就多了个and，因为我们将name赋值为null，在xml的if标签中，就会跳过。我们这次只修改了第一个字段，所以只执行了第一个if标签中的SQL语句，后面if标签中的SQL语句就直接略过了，因此会执行到。面对以下的代码，如果要修改表名或者字段名的话，则在两个select标签中都要修改，这就导致代码的。对于下面的代码，在执行更新操作时，如果有些字段没有赋值的话，在表中的对应字段会变成null。
复制链接

扫一扫