八、动态SQL

最新推荐文章于 2023-10-06 22:44:46 发布

饭团煲

最新推荐文章于 2023-10-06 22:44:46 发布

阅读量216

点赞数

分类专栏： SSM 文章标签： sql 数据库 eclipse mybatis

本文链接：https://blog.csdn.net/m0_62639288/article/details/131597499

版权

SSM 专栏收录该内容

27 篇文章 1 订阅

订阅专栏

三、<choose><when><otherwise>元素

一、动态SQL中的元素

1、动态SQL有什么作用？

开发人员在使用JDBC或其他类似的框架进行数据库开发时，通常都要根据需求去手动拼装SQL，这是一个非常麻烦且痛苦的工作，而MyBatis提供的对SQL语句动态组装的功能，恰能很好的解决这一麻烦工作。

2、动态SQL主要元素

动态SQL是MyBatis的强大特性之一，MyBatis3采用了功能强大的基于OGNL的表达式来完成动态SQL。动态SQL主要元素如下表所示：

二、<if>元素

在MyBatis中，<if>元素是最常用的判断语句，它类似于Java中的if语句，主要用于实现某些简单的条件选择。

在实际应用中，我们可能会通过多个条件来精确地查询某个数据。例如，要查找某个客户的信息，可以通过姓名和职业来查找客户，也可以不填写职业直接通过姓名来查找客户，还可以都不填写而查询出所有客户，此时姓名和职业就是非必须条件。类似于这种情况，在 MyBatis中就可以通过<if>元素来实现。下面就通过一个具体的案例,来演示这种情况下<if>元素的使用,具体实现步骤如下。

( 1）在Eclipse 中，创建一个名为chapter08的 Web项目，将第6章MyBatis 入门程序中的JAR包和文件复制到chapter08中，并将配置文件中的数据库信息修改为外部引入的形式，同时创建一个com.itheima.utils包，在该包下引入第7章编写的工具类MybatisUtils，这样就完成了项目的创建和基本配置。搭建后的项目文件结构如图所示。

(2)修改映射文件 CustomerMapper.xml，在映射文件中使用<if>元素编写根据客户姓名和职业组合条件查询客户信息列表的动态SQL

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.qingcheng.ssm.mapper.CustomerMapper">

        
   <select id="findCustomerByNameAndJobs" parameterType="customer"
                    resultType="customer">
       select * from t_customer where 1=1
       
       <if test="username != null and username !=''">
           and username like concat('%',#{username},'%')
       </if>
       <if test="jobs != null and jobs !=''">
           and jobs=#{jobs}
       </if>
       
   </select>

</mapper>

(3）在测试类 MybatisTest 中，编写测试方法 findCustomerByNameAndJobsTest()

@Test
   public void findCustomerByNameAndJobs() {

        SqlSession sqlSession = MybatisUtils.getSession();
       Customer customer = new Customer();
       customer.setUsername("Jack");
customer.setJobs("teacher");
       List<Customer> customers = sqlSession.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameAndJobs", customer);
       for(Customer customer1:customers) {
           System.out.println(customer1);
       }
       //6.关闭Sqlsession
       sqlSession.close();
   }

使用JUnit4执行findCustomerByNameAndJobsTest()方法后,控制台的输出结果

从上图可以看出，已经查询出了username为 jack，并且 jobs 为 teacher的客户信息。如果将封装到Customer对象中的jack和 teacher两行代码注释(即传入的客户姓名和职业都为空)，然后再次使用JUnit4执行 findCustomerByNameAndJobsTest()方法后，控制台的输出结果

从上图可以看到，当未传递任何参数时，程序会将数据表中的所有数据查出。这就是<if>元素的使用。

三、<choose>、<when>、<otherwise>元素

在使用<if>元素时，只要 test属性中的表达式为true，就会执行元素中的条件语句，但是在实际应用中,有时只需要从多个选项中选择一个去执行。例如下面的场景:
“当客户名称不为空，则只根据客户名称进行客户筛选;
当客户名称为空，而客户职业不为空，则只根据客户职业进行客户筛选。
当客户名称和客户职业都为空，则要求查询出所有电话不为空的客户信息。”
此种情况下，使用<if>元素进行处理是非常不合适的。如果使用的是 Java语言，这种情况显然更适合使用switch..case..default语句来处理。那么在 MyBatis 中有没有类似的语句呢?答案是肯定的。针对上面情况，MyBatis中可以使用<choose>、<when>、<otherwise>元素进行处理。下面让我们看一下如何使用<choose>、<when>、<otherwise>元素组合去实现上面的情况。

( 1)在映射文件 CustomerMapper.xml 中，使用<choose>、<when>、<otherwise>元素执行上述情况的动态SQL代码如下所示。

<select id="findCustomerByNameOrJobs2" parameterType="customer"
   resultType="customer">
       select * from t_customer where 1=1
       <choose>
           <when test="username != null and username !=''">
               and username like concat('%',#{username},'%')
           </when>
           <when test="jobs != null and jobs !=''">
               and jobs=#{jobs}
           </when>
           <otherwise>
               and phone is not null
           </otherwise>
       </choose>
   </select>

在上述代码中，使用了<choose>元素进行SQL拼接，当第一个<when>元素中的条件为真，则只动态组装第一个<when>元素内的SQL片段，否则就继续向下判断第二个<when>元素中的条件是否为真，以此类推。当前面所有 when元素中的条件都不为真时，则只组装<otherwise>元素内的SQL片段。

(2)在测试类MybatisTest 中，编写测试方法 findCustomerByNameOrJobsTest()，其代码如下所示。

@Test
   public void findCustomerByNameOrJobsTest() {

        SqlSession sqlSession = MybatisUtils.getSession();
       Customer customer = new Customer();
       customer.setUsername("Jack");
customer.setJobs("teacher");
       List<Customer> customers = sqlSession.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameOrJobs", customer);
       for(Customer customer2:customers) {
           System.out.println(customer2);
       }
       //6.关闭Sqlsession
       sqlSession.close();
   }

四、<where>、<trim>元素

在前两个小节的案例中，映射文件中编写的SQL后面都加入了“where 1=1”的条件，那么到底为什么要这么写呢？如果将where后“1=1”的条件去掉，那么MyBatis所拼接出来的SQL将会如下所示：

select * from t_customer where and username like concat('%',?, '%')

上面SQL 中，where后直接跟的是 and，这在运行时肯定会报SQL语法错误，而加入了条件“1=1”后，既保证了 where后面的条件成立，又避免了where后面第一个词是 and或者or之类的关键词。那么在MyBatis 中，有没有什么办法不用加入“1=1”这样的条件，也能使拼接后的SQL成立呢?

针对这种情况，MyBatis 提供了<where>元素来处理这样的问题。
以第一小节的案例为例，将映射文件中的“where 1=1”条件删除，并使用<where>元素替换后的代码如下所示。

select * from t_customer
<where>
<if test="username !=null and username !=''">
and username like concat('%',#{username}, '%')
</if>
<if test="jobs !=null and jobs !=''">
and jobs= #{jobs}
</if>
</where>

<where>会自动判断SQL语句，只有<where>内的条件成立时，才会在拼接SQL中加入where关键字，否则将不会添加；还会去除多余的“AND”或“OR”。

除了使用<where>元素外，还可以通过<trim>元素来定制需要的功能，上述代码还可以修改为如下形式:

select * from t_customer
<trim prefix="where" prefixOverrides="and">
<if test="username !=null and username !=''">
and username like concat('%',#{username}, '%')
</if>
<if test="jobs !=null and jobs !=''">
and jobs= #{jobs}
</if>
</trim>

<trim>的作用是去除特殊的字符串，它的prefix属性代表语句的前缀，prefixOverrides属性代表需要去除的哪些特殊字符串，功能和<where>基本是等效的。

五、<set>元素

在Hibernate 中，如果想要更新某一个对象，就需要发送所有的字段给持久化对象，然而实际应用中，大多数情况下都是更新的某一个或几个字段。如果更新的每一条数据都要将其所有的属性都更新一遍，那么其执行效率是非常差的。有没有办法让程序只更新需要更新的字段呢?
为了解决上述情况中的问题，MyBatis中提供了<set>元素来完成这一工作。<set>元素主要用于更新操作，其主要作用是在动态包含的SQL语句前输出一个SET关键字，并将SQL语句中最后一个多余的逗号去除。

以入门案例中的更新操作为例,使用<set>元素对映射文件中更新客户信息的SQL 语句进行修改的代码如下所示。

<update id="updateCustomer" parameterType="com.itheima.po.Customer">
update t_customer
<set>
<if test="username !=null and username !=''">
username=#{username},
</if>
<if test="jobs !=null and jobs !=''">
jobs=#{jobs},
</if>

<if test="phone !=null and phone!=''">
phone=#{phone},
</if>
</set>
where id=#{id}
</update>

在上述配置的SQL语句中，使用了<set>和<if>元素相结合的方式来组装update语句。其中<set>元素会动态前置SET关键字，同时也会消除SQL语句中最后一个多余的逗号;<if>元素用于判断相应的字段是否传入值，如果传入的更新字段非空，就将此字段进行动态SQL组装并更新此字段,否则此字段不执行更新。

@Test
   public void updateCustomer() throws IOException {
       SqlSession sqlSession = MybatisUtils.getSession();
       Customer customer = new Customer();
       customer.setId(3);
       customer.setPhone("11111111");
       int rows = sqlSession.update("com.itheima.mapper.CustomerMapper.updateCustomer",customer);
       //5.反馈插入信息
       if(rows>0) {
           System.out.println("您成功修改了"+rows+"条数据");
       }else {
           System.out.println("执行修改操作失败！");
       }
       //提交事务
       sqlSession.commit();
       //6.关闭Sqlsession
       sqlSession.close();
   }

注意！！在映射文件中使用<set>和<it>元素组合进行update语句动态SQL组装时,如果<set>元素内包含的内容都为空，则会出现SQL 语法错误。所以在使用<set>元素进行字段信息更新时,要确保传入的更新字段不能都为空。

六、<foreach>元素

在实际开发中，有时可能会遇到这样的情况:假设在一个客户表中有1000条数据，现在需要将id值小于100的客户信息全部查询出来，这要怎么做呢?有人也许会说，“我可以一条一条查出来”，那如果查询200、300甚至更多也一条一条查吗?这显然是不可取的。有的人会想到，可以在Java方法中使用循环，将查询方法放在循环语句中，然后通过条件循环的方式查询出所需的数据。这种查询方式虽然可行，但每执行一次循环语句，都需要向数据库中发送一条查询SQL，其查询效率是非常低的。那么还有其他更好的方法吗?我们能不能通过SQL语句来执行这种查询呢?
其实，MyBatis中已经提供了一种用于数组和集合循环遍历的方式，那就是使用<foreach>元素，我们完全可以通过<foreach>元素来解决上述类似的问题。

<foreach>元素通常在构建IN条件语句时使用，其使用方式如下。

<select id="findCustomerByIds" parameterType="List"
resultType="com.itheima.po.Customer">
select * from t_customer where id in
<foreach item="id" index="index" collection="list" select * from t_customer where id in(1,2)
open="(" separator="," close=")">
#{id}
</foreach>
</select>

关于上述示例中<foreach>元素中使用的几种属性的描述具体如下：

item：配置的是循环中当前的元素。
index：配置的是当前元素在集合的位置下标。
collection：配置的list是传递过来的参数类型（首字母小写），它可以是一个array、list（或collection）、Map集合的键、POJO包装类中数组或集合类型的属性名等。
open和close：配置的是以什么符号将这些集合元素包装起来。
separator：配置的是各个元素的间隔符。

为了验证上述配置,可以在测试类MybatisTest中,编写测试方法 findCustomerByldsTest(),其代码如下所示。

@Test
   public void findCustomerByIdsTest() {

        SqlSession sqlSession = MybatisUtils.getSession();
       List<Integer> ids = new ArrayList<Integer>();
       ids.add(1);
       ids.add(2);
       List<Customer> customers = sqlSession.selectList("com.itheima.mapper.CustomerMapper.findCustomerByIds", ids);
       for(Customer customer1:customers) {
           System.out.println(customer1);
       }
       //6.关闭Sqlsession
       sqlSession.close();
       }

}

在上述代码中，执行查询操作时传入了一个客户编号集合ids。使用JUnit4执行findCustomerByldsTest()方法后，控制台的输出结果如图所示。

从图8-9可以看出，使用<foreach>元素已对传入的客户编号集合进行了动态SQL组装，最终成功批量查询出了对应的客户信息。

在使用<foreach>时最关键也是最容易出错的就是collection属性，该属性是必须指定的，而且在不同情况下，该属性的值是不一样的。主要有以下3种情况：

如果传入的是单参数且参数类型是一个数组或者List的时候，collection属性值分别为array和list（或collection）。
如果传入的参数是多个的时候，就需要把它们封装成一个Map了，当然单参数也可以封装成Map集合，这时候collection属性值就为Map的键。
如果传入的参数是POJO包装类的时候，collection属性值就为该包装类中需要进行遍历的数组或集合的属性名。

七、<bind>元素

在进行模糊查询的SQL语句的时候，如果使用“${}”进行字符串拼接，则无法防止SQL注入问题；如果改用concat函数进行拼接，则只针对MySQL数据库有效；如果改用“||”进行字符串拼接，则只针对Oracle数据库有效。这样，映射文件中的SQL就要根据不同的情况提供不同形式的实现，这显然是比较麻烦的，且不利于项目的移植。为了减少这种麻烦，就可以使用MyBatis的<bind>元素来解决这一问题。

MyBatis的<bind>元素可以通过OGNL表达式来创建一个上下文变量，其使用方式如下：

<select id="findCustomerByName" parameterType="com.itheima.po.Customer"
resultType="com.itheima.po.Customer">


<bind name="pattern_username" value="'%'+_parameter.getUsername()+'%'" />
select * from t_customer
where
username like #{pattern_username}
</select>

为了验证上述配置是否能够正确执行，可以在测试类MybatisTest中，编写测试方法findCustomerByNameTest()进行测试,其代码如下所示。

/**
*<bind>元素的使用:根据客户名模糊查询客户信息
*/
@Test
public void findCustomerByNameTest(){
   //通过工具类生成SqlSession对象
   Sqlsession session- MybatisUtils.getSession ();
   //创建 Customer对象，封装查询的条件
   Customer customer =new Customer ();
   customer.setUsername ("j");
   //执行SqlSession的查询方法,返回结果集
   List<Customer> customers = session.selectList ("com.itheima.mapper"
           + ".customerMapper.findCustomerByName",customer);
   //输出查询结果信息
   for (Customer customer2 : customers) {
   //打印输出结果
   System.out.println (customer2);
   }
   /关闭SqlSession
   session.close();
}

使用JUnit4 执行findCustomerByNameTest()方法后，控制台的输出结果如图所示。

饭团煲

关注

0
点赞
踩
0

收藏

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

以入门案例中的更新操作为例,使用元素对映射文件中更新客户信息的SQL 语句进行修改的代码如下所示。动态SQL中的元素1、动态SQL有什么作用？2、动态SQL主要元素二、元素三、元素四、、元素五、元素六、元素七、元素
复制链接

扫一扫