Java EE学习笔记(八)

动态SQL

1、动态SQL中的元素

1)、作用:无需手动拼装SQL,MyBatis已提供的对SQL语句动态组装的功能,使得数据库开发效率大大提高!

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

Java EE学习笔记(八)

2、<if>元素

1)、在MyBatis中,<if>元素是最常用的判断语句,它类似于Java中的if语句,主要用于实现某些简单的条件选择。其基本使用示例如下:使用<if>元素对username和jobs进行非空判断,并动态组装SQL

1      select * from t_customer where 1=1 
2      <if test="username !=null and username !=''">
3             and username like concat('%',#{username}, '%')
4      </if>
5      <if test="jobs !=null and jobs !=''">
6     and jobs= #{jobs}
7      </if>  

2)、本章项目文件结构

Java EE学习笔记(八)

①配置文件:src->mybatis-config.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 3                           "http://mybatis.org/dtd/mybatis-3-config.dtd">
 4                           
 5 <configuration>
 6     <!-- 宏定义 -->
 7     <properties resource="db.properties" />
 8     
 9     <!--1.配置环境 ,默认的环境id为mysql -->
10     <environments default="mysql">
11         
12         <!--1.2.配置id为mysql的数据库环境 -->
13         <environment id="mysql">
14             
15             <!-- 使用JDBC的事务管理 -->
16             <transactionManager type="JDBC" />
17             
18             <!--数据库连接池 -->
19             <dataSource type="POOLED">
20                 
21                 <!-- 数据库驱动 -->
22                 <property name="driver" value="${jdbc.driver}" />
23                 
24                 <!-- 连接数据库的url -->
25                 <property name="url" value="${jdbc.url}" />
26                 
27                 <!-- 连接数据库的用户名 -->
28                 <property name="username" value="${jdbc.username}" />
29                 
30                 <!-- 连接数据库的密码 -->
31                 <property name="password" value="${jdbc.password}" />
32                 
33             </dataSource>
34             
35         </environment>
36         
37     </environments>
38     
39     <!--2.配置Mapper的位置 -->
40     <mappers>
41         <mapper resource="com/itheima/mapper/CustomerMapper.xml" />
42     </mappers>
43 </configuration>

②src->db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=******

#密码自己设置

由于MyBatis默认使用log4j输出日志信息,所以如果要查看控制台的输出SQL语句,那么就需要在classpath路径下配置其日志文件。在项目的src目录下创建log4j.properties文件。

# Global logging configuration,全局的日志配置,Mybatis的日志配置和控制台输出,其中Mybatis的日志配置用于将com.itheima包下所有类的日志记录级别设置为DEBUG
log4j.rootLogger=ERROR, stdout


# MyBatis logging configuration...
log4j.logger.com.itheima=DEBUG


# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

④修改映射文件:CustomerMapper.xml

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3                    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4   
 5 <mapper namespace="com.itheima.mapper.CustomerMapper">
 6     <!-- <if>元素使用,
 7         test属性多用于条件判断语句中,用于判断真假,大部分进行非空判断
 8         若传入的查询条件非空就进行动态SQL组装,也就是条件成立时执行查询语句,注意用and连接查询语句
 9      -->
10     <select id="findCustomerByNameAndJobsID" parameterType="com.itheima.po.Customer" 
11              resultType="com.itheima.po.Customer"> 
12         select * from t_customer where 1=1 
13         <if test="username !=null and username !=''">
14             and username like concat('%',#{username},'%') 
15         </if> 
16         
17         <if test="jobs !=null and jobs !=''"> 
18             and jobs= #{jobs} 
19         </if> 
20     </select>
21 </mapper>

⑤单元测试:

 1     /**
 2      * 根据客户姓名和职业组合条件查询客户信息列表
 3      */
 4     @Test
 5     public void findCustomerByNameAndJobsTest(){
 6         
 7         // 1、通过工具类生成SqlSession对象
 8         SqlSession session = MybatisUtils.getSession();
 9         
10         // 2、创建Customer对象,封装需要组合查询的条件
11         Customer customer = new Customer();
12         customer.setUsername("jack");
13         customer.setJobs("teacher");
14         
15         // 3、执行SqlSession的查询方法,返回结果集
16        List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameAndJobsID",customer);
17         
18        // 4、输出查询结果信息
19         for (Customer customer2 : customers) {
20             // 打印输出结果
21             System.out.println(customer2);
22         }
23         
24         // 5、关闭SqlSession
25         session.close();
26     }

⑥运行结果:

Java EE学习笔记(八)

⑦若注释掉封装对象中的jack和teacher的2行代码,则执行结果为查询整张表:

Java EE学习笔记(八)

Java EE学习笔记(八)

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

1)、这三个元素的组合相当于java语言中的switch...case...default语句,其XML文件一般为以下格式:使用<choose>及其子元素依次对条件进行非空判断,并动态组装SQL

      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>

①修改映射文件:CustomerMapper.xml

 1 <!--<choose>(<when>、<otherwise>)元素使用 -->
 2     <select id="findCustomerByNameOrJobsID" parameterType="com.itheima.po.Customer"
 3                 resultType="com.itheima.po.Customer">
 4         select * from t_customer where 1=1
 5         <choose>
 6             <!--<choose>元素相当于switch, <when>元素相当于case语句
 7                 语法和switch...case...default一样
 8              -->
 9             <when test="username !=null and username !=''">
10                 and username like concat('%',#{username}, '%')
11             </when>
12             
13             <when test="jobs !=null and jobs !=''">
14                 and jobs= #{jobs}
15             </when>
16             
17             <!-- <otherwise>元素相当于default关键字,若前面的条件都不成立,默认执行下面这一条件 -->
18             <otherwise>
19                 and phone is not null
20             </otherwise>
21             
22         </choose>
23     </select>

②单元测试:

 1     /**
 2      * 根据客户姓名或职业查询客户信息列表
 3      */
 4     @Test
 5     public void findCustomerByNameOrJobsTest(){
 6         
 7         // 1、通过工具类生成SqlSession对象
 8         SqlSession session = MybatisUtils.getSession();
 9         
10         // 2、创建Customer对象,封装需要组合查询的条件
11         Customer customer = new Customer();
12         customer.setUsername("jack");
13         customer.setJobs("teacher");
14         
15         // 3、执行SqlSession的查询方法,返回结果集
16         List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameOrJobsID",customer);
17         
18         // 4、输出查询结果信息
19         for (Customer customer2 : customers) {
20             // 打印输出结果
21             System.out.println(customer2);
22         }
23         
24         // 5、关闭SqlSession
25         session.close();
26     }

③运行结果:(注意当前查询语句)

Java EE学习笔记(八)

④若注释掉customer.setUsername("jack");则执行结果如下:(注意当前查询语句)

Java EE学习笔记(八)

⑤若再注释掉customer.setJobs("teacher");则执行结果如下:(执行XML中<otherwise>元素块)

Java EE学习笔记(八)

4、<when>、<trim>元素

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

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

可以看出上面SQL语句明显存在SQL语法错误,而加入了条件“1=1”后,既保证了where后面的条件成立,又避免了where后面第一个词是and或者or之类的关键词

2)、针对上述情况中“where 1=1”,在MyBatis的SQL中就可以使用<where><trim>元素进行动态处理。

①修改映射文件:CustomerMapper.xml

 1     <!-- <where>元素 
 2         只有<where>元素内的条件成立(执行过程和switch-case语句一样,只要条件一成立直接返回查询该字段)时,才会在拼接SQL中加入where关键字,否则将不会添加;
 3         即使where之后的内容有多余的"AND"或者是"OR",<where>元素也会自动将它们删除
 4         若没有where关键字,即<where>元素内没有一个条件成立,则将查询整张表
 5     -->
 6     <select id="findCustomerByNameAndJobsID" parameterType="com.itheima.po.Customer" 
 7                 resultType="com.itheima.po.Customer"> 
 8         select * from t_customer 
 9         <where> 
10             <if test="username !=null and username !=''"> 
11                 and username like concat('%',#{username},'%') 
12             </if> 
13             
14             <if test="jobs !=null and jobs !=''"> 
15                      and jobs= #{jobs} 
16             </if> 
17         </where> 
18     </select>

②执行结果:

Java EE学习笔记(八)

③若注释掉customer.setUsername("jack");则查询结果如下:注意查询语句

Java EE学习笔记(八)

④若再注释掉customer.setJobs("teacher");则为查询整张表

Java EE学习笔记(八)

3)、除了使用<where>元素外,还可以通过<trim>元素来定制需要的功能。

①修改映射文件:CustomerMapper.xml(运行结果和上面一样)

 1      <!-- <trim>元素
 2         prefix属性:语句的前缀,这里使用where来连接后面的SQL片段
 3             prefixOverrides属性:需要去除的那些特殊字符串(这里定义了要去除的SQL中的and)
 4      -->
 5     <select id="findCustomerByNameAndJobsID" parameterType="com.itheima.po.Customer"
 6                     resultType="com.itheima.po.Customer">
 7         select * from t_customer
 8         <trim prefix="where" prefixOverrides="and">
 9             <if test="username !=null and username !=''">
10                 and username like concat('%',#{username}, '%')
11             </if>
12             <if test="jobs !=null and jobs !=''">
13                 and jobs= #{jobs}
14             </if>
15         </trim>
16     </select>

4)、总之,<where>和<trim>元素作用如下:

Java EE学习笔记(八)

5、<set>元素

1)、在Hibernate中,想要更新某个对象,就需要发送所有的字段给持久化对象,这种想更新的每一条数据都要将其所有的属性都更新一遍的方法,其执行效率非常差的。为此,在MyBatis中可以使用动态SQL中的<set>元素进行处理:使用<set>和<if>元素对username和jobs进行更新判断,并动态组装SQL。这样就只需要传入想要更新的字段即可。

①修改映射文件:CustomerMapper.xml

 1      <!-- <set>元素
 2         <set>和<if>来组装update语句,其中<set>元素会动态前置SET关键字,
 3         同时也会消除SQL语句中最后一个多余的逗号
 4      -->
 5     <update id="updateCustomerID" parameterType="com.itheima.po.Customer">
 6         update t_customer
 7         <set>
 8             <if test="username !=null and username !=''">
 9                 username=#{username},
10             </if>
11             <if test="jobs !=null and jobs !=''">
12                 jobs=#{jobs},
13             </if>
14             <if test="phone !=null and phone !=''">
15                 phone=#{phone},
16             </if>
17         </set>
18         where id=#{id}
19     </update>

②单元测试:

 1     /**
 2      * 更新客户信息
 3      */
 4     @Test
 5     public void updateCustomerTest() {        
 6         
 7         // 1、通过工具类获取SqlSession
 8         SqlSession sqlSession = MybatisUtils.getSession();
 9         
10         // 2、创建Customer对象,并向对象中添加数据
11         Customer customer = new Customer();
12         customer.setId(3);
13         customer.setPhone("99999999999");
14         
15         // 3、执行SqlSession的更新方法,返回的是SQL语句影响的行数
16         int rows = sqlSession.update("com.itheima.mapper.CustomerMapper.updateCustomerID", customer);
17         
18         // 4、通过返回结果判断更新操作是否执行成功
19         if(rows > 0){
20             System.out.println("您成功修改了"+rows+"条数据!");
21         }else{
22             System.out.println("执行修改操作失败!!!");
23         }
24         
25         // 5、提交事务
26         sqlSession.commit();
27         
28         // 6、关闭SqlSession
29         sqlSession.close();
30     }

③执行结果:

Java EE学习笔记(八)

Java EE学习笔记(八)

④若注释掉customer.setId(3);和customer.setPhone("66666666666");这2条语句,则执行时会出错:

Java EE学习笔记(八)

即:若<set>元素内包含的内容都为空,则会出现SQL语法错误。所以在使用<set>元素进行字段信息更新时,要确保传入的更新字段不能为空

6、<foreach>元素

1)、需求:提高批量查询的效率!

①修改映射文件:CustomerMapper.xml

 1      <!--<foreach>元素使用
 2         使用<foreach>元素对传入的集合进行遍历并进行了动态SQL组装
 3         item:配置的是循环中当前的元素,(本次迭代获取的元素为属性id)
 4         index:配置的是当前元素在集合的位置下标(index是当前迭代的次数)
 5         当使用字典(或者Map.Entry对象的集合)时,index是键,item是值
 6         
 7         collection(指定输入对象中的集合属性):下面栗子中配置的list是传递过来的参数类型(首字母小写),
 8         它可以是一个array、list(或collection)、Map集合的键、
 9         POJO包装类中数组或集合类型的属性名等。
10         open和close:配置的是以什么符号将这些集合元素包装起来。
11         open:拼接字符串的前缀, close:拼接字符串的后缀
12         separator:配置的是各个元素的间隔符。
13         
14         SQL查询语句:select * from t_customer where id in (1,2,3,4,5);
15         这个配置其实也就是SQL语句中有哪些属性值属于要查询的集合,用法异曲同工之妙
16      -->
17     <select id="findCustomerByIds" parameterType="List"
18         resultType="com.itheima.po.Customer">
19         select * from t_customer where id in
20         <foreach item="user_id" index="index" collection="list" open="("
21             separator="," close=")">
22             #{user_id}
23             <!-- 这里的#{user_id}表示取出集合中每一个元素对象,其中的user_id对应属性item值 -->
24         </foreach>
25     </select>

②测试单元:

 1     /**
 2      * 根据客户编号批量查询客户信息
 3      */
 4     @Test
 5     public void findCustomerByIdsTest(){
 6         
 7         // 1、通过工具类获取SqlSession
 8         SqlSession session = MybatisUtils.getSession();
 9         
10         // 2、创建List集合,封装查询id
11         List<Integer> ids=new ArrayList<Integer>();
12         ids.add(1);
13         ids.add(2);
14         
15         // 3、执行SqlSession的查询方法,返回结果集
16         List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByIds", ids);
17         
18         // 4、输出查询结果信息    
19         for (Customer customer : customers) {
20             // 打印输出结果
21             System.out.println(customer);
22         }
23         
24         // 5、关闭SqlSession
25         session.close();
26     }

③查询结果:

Java EE学习笔记(八)

2)、在使用<foreach>时最关键也是最容易出错的就是collection属性,该属性是必须指定的(按实际情况进行配置),而且在不同情况下,该属性的值是不一样的。主要有以下3种情况:

a)、如果传入的是单参数且参数类型是一个数组或者List的时候,collection属性值分别为array和list(或collection)

b)、如果传入的参数是多个的时候,就需要把它们封装成一个Map了,当然单参数也可以封装成Map集合,这时候collection属性值就为Map的键

c)、如果传入的参数是POJO包装类的时候,collection属性值就为该包装类中需要进行遍历的数组或集合的属性名

7、<bind>元素

1)、思考:记得入门案例中模糊查询的SQL语句:

select * from t_customer where username like '%${value}%'

不妥之处:

a)、如果使用“${}”进行字符串拼接,则无法防止SQL注入问题;

b)、如果改用concat函数进行拼接,则只针对MySQL数据库有效;

c)、如果改用“||”进行字符串拼接,则只针对Oracle数据库有效。

d)、总结:这样映射文件中的SQL就要根据不同的情况提供不同形式的实现,这显然是比较麻烦的,且不利于项目的移植。为了减少这种麻烦,就可以使用MyBatis的<bind>元素来解决这一问题。

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

①修改映射文件:CustomerMapper.xml

 1     <!--<bind>元素的使用:根据客户名模糊查询客户信息 -->
 2     <select id="findCustomerByName" parameterType="com.itheima.po.Customer"
 3                 resultType="com.itheima.po.Customer">
 4         <!--
 5             _parameter.getUsername()也可直接写成传入的字段属性名,即username
 6             <bind>元素定义了一个name为pattern_username的变量
 7             <bind>元素中value的属性值就是拼接的查询字符串
 8             其中_parameter.getUsername()表示传递进来的参数
 9          -->
10         <bind name="pattern_username" value="'%'+_parameter.getUsername()+'%'" />
11         select * from t_customer
12         where
13         username like #{pattern_username}
14     </select>

②单元测试:

 1     /**
 2      * bind元素的使用:根据客户名模糊查询客户信息 
 3      */
 4     @Test
 5     public void findCustomerByNameTest(){
 6         
 7         // 1、通过工具类生成SqlSession对象
 8         SqlSession session = MybatisUtils.getSession();
 9         
10         // 2、创建Customer对象,封装查询的条件
11         Customer customer =new Customer();
12         customer.setUsername("j");
13         
14         // 3、执行sqlSession的查询方法,返回结果集
15         List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByName", customer);
16         
17         // 4、输出查询结果信息    
18         for (Customer customer2 : customers) {
19             // 打印输出结果
20             System.out.println(customer2);
21         }
22         
23         // 5、关闭SqlSession
24         session.close();
25     }

③运行结果:

Java EE学习笔记(八)

3)、最后再贴一下工具类和用户类:

①工具类:MybatisUtils.java

 1 package com.itheima.utils;
 2 import java.io.Reader;
 3 import org.apache.ibatis.io.Resources;
 4 import org.apache.ibatis.session.SqlSession;
 5 import org.apache.ibatis.session.SqlSessionFactory;
 6 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 7 /**
 8  * 工具类
 9  */
10 public class MybatisUtils {
11     private static SqlSessionFactory sqlSessionFactory = null;
12     // 初始化SqlSessionFactory对象
13     static {
14         try {
15             // 使用MyBatis提供的Resources类加载mybatis的配置文件
16             Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
17             // 构建sqlSession的工厂
18             sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
19         } catch (Exception e) {
20             e.printStackTrace();
21         }
22     }
23     // 获取SqlSession对象的静态方法
24     public static SqlSession getSession() {
25         return sqlSessionFactory.openSession();
26     }
27 }

②用户类:src/com/itheima/po/Customer.java

 1 package com.itheima.po;
 2 /**
 3  * 客户持久化类
 4  */
 5 public class Customer {
 6     private Integer id;       // 主键id
 7     private String username; // 客户名称
 8     private String jobs;      // 职业
 9     private String phone;     // 电话
10     
11     public Integer getId() {
12         return id;
13     }
14     
15     public void setId(Integer id) {
16         this.id = id;
17     }
18     
19     public String getUsername() {
20         return username;
21     }
22     
23     public void setUsername(String username) {
24         this.username = username;
25     }
26     
27     public String getJobs() {
28         return jobs;
29     }
30     
31     public void setJobs(String jobs) {
32         this.jobs = jobs;
33     }
34     
35     public String getPhone() {
36         return phone;
37     }
38     
39     public void setPhone(String phone) {
40         this.phone = phone;
41     }
42     
43     @Override
44     public String toString() {
45         return "Customer [>;
46     }
47 }

个人总结:

动态SQL解决了手动拼接sql语句,为查询用户信息带来很大便利,只要编写好XML中需要查询的动态SQL语句(其用法和sql查询语句类似),剩下的就交给MyBatis底层去实现,而且还不容易出错,开发效率得到极大提高,十分推荐!