Java EE学习笔记（八）

动态SQL

1、动态SQL中的元素

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

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

2、<if>元素

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

     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>  

2)、本章项目文件结构

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

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
                          "http://mybatis.org/dtd/mybatis-3-config.dtd">
                          
<configuration>
    <!-- 宏定义 -->
    <properties resource="db.properties" />
    
    <!--1.配置环境 ，默认的环境id为mysql -->
    <environments default="mysql">
        
        <!--1.2.配置id为mysql的数据库环境 -->
        <environment id="mysql">
            
            <!-- 使用JDBC的事务管理 -->
            <transactionManager type="JDBC" />
            
            <!--数据库连接池 -->
            <dataSource type="POOLED">
                
                <!-- 数据库驱动 -->
                <property name="driver" value="${jdbc.driver}" />
                
                <!-- 连接数据库的url -->
                <property name="url" value="${jdbc.url}" />
                
                <!-- 连接数据库的用户名 -->
                <property name="username" value="${jdbc.username}" />
                
                <!-- 连接数据库的密码 -->
                <property name="password" value="${jdbc.password}" />
                
            </dataSource>
            
        </environment>
        
    </environments>
    
    <!--2.配置Mapper的位置 -->
    <mappers>
        <mapper resource="com/itheima/mapper/CustomerMapper.xml" />
    </mappers>
</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

<?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.itheima.mapper.CustomerMapper">
    <!-- <if>元素使用,
        test属性多用于条件判断语句中，用于判断真假，大部分进行非空判断
        若传入的查询条件非空就进行动态SQL组装，也就是条件成立时执行查询语句，注意用and连接查询语句
     -->
    <select id="findCustomerByNameAndJobsID" parameterType="com.itheima.po.Customer" 
             resultType="com.itheima.po.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>

⑤单元测试：

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

⑥运行结果：

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

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

<!--<choose>(<when>、<otherwise>)元素使用 -->
    <select id="findCustomerByNameOrJobsID" parameterType="com.itheima.po.Customer"
                resultType="com.itheima.po.Customer">
        select * from t_customer where 1=1
        <choose>
            <!--<choose>元素相当于switch， <when>元素相当于case语句
                语法和switch...case...default一样
             -->
            <when test="username !=null and username !=''">
                and username like concat('%',#{username}, '%')
            </when>
            
            <when test="jobs !=null and jobs !=''">
                and jobs= #{jobs}
            </when>
            
            <!-- <otherwise>元素相当于default关键字，若前面的条件都不成立，默认执行下面这一条件 -->
            <otherwise>
                and phone is not null
            </otherwise>
            
        </choose>
    </select>

②单元测试：

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

③运行结果：（注意当前查询语句）

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

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

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

    <!-- <where>元素 
        只有<where>元素内的条件成立（执行过程和switch-case语句一样，只要条件一成立直接返回查询该字段）时，才会在拼接SQL中加入where关键字，否则将不会添加；
        即使where之后的内容有多余的"AND"或者是"OR"，<where>元素也会自动将它们删除
        若没有where关键字，即<where>元素内没有一个条件成立，则将查询整张表
    -->
    <select id="findCustomerByNameAndJobsID" parameterType="com.itheima.po.Customer" 
                resultType="com.itheima.po.Customer"> 
        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> 
    </select>

②执行结果：

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

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

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

①修改映射文件：CustomerMapper.xml（运行结果和上面一样）

     <!-- <trim>元素
        prefix属性：语句的前缀，这里使用where来连接后面的SQL片段
            prefixOverrides属性：需要去除的那些特殊字符串（这里定义了要去除的SQL中的and）
     -->
    <select id="findCustomerByNameAndJobsID" parameterType="com.itheima.po.Customer"
                    resultType="com.itheima.po.Customer">
        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>
    </select>

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

5、<set>元素

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

①修改映射文件：CustomerMapper.xml

     <!-- <set>元素
        <set>和<if>来组装update语句，其中<set>元素会动态前置SET关键字，
        同时也会消除SQL语句中最后一个多余的逗号
     -->
    <update id="updateCustomerID" 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>

②单元测试：

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

③执行结果：

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

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

6、<foreach>元素

1)、需求：提高批量查询的效率！

①修改映射文件：CustomerMapper.xml

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

②测试单元：

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

③查询结果：

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

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

②单元测试：

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

③运行结果：

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

①工具类：MybatisUtils.java

package com.itheima.utils;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
 * 工具类
 */
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory = null;
    // 初始化SqlSessionFactory对象
    static {
        try {
            // 使用MyBatis提供的Resources类加载mybatis的配置文件
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            // 构建sqlSession的工厂
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取SqlSession对象的静态方法
    public static SqlSession getSession() {
        return sqlSessionFactory.openSession();
    }
}

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

package com.itheima.po;
/**
 * 客户持久化类
 */
public class Customer {
    private Integer id;       // 主键id
    private String username; // 客户名称
    private String jobs;      // 职业
    private String phone;     // 电话
    
    public Integer getId() {
        return id;
    }
    
    public void setId(Integer id) {
        this.id = id;
    }
    
    public String getUsername() {
        return username;
    }
    
    public void setUsername(String username) {
        this.username = username;
    }
    
    public String getJobs() {
        return jobs;
    }
    
    public void setJobs(String jobs) {
        this.jobs = jobs;
    }
    
    public String getPhone() {
        return phone;
    }
    
    public void setPhone(String phone) {
        this.phone = phone;
    }
    
    @Override
    public String toString() {
        return "Customer [id=" + id + ", username=" + username + ", jobs=" + jobs + ", phone=" + phone + "]";
    }
}

个人总结：

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