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的属性值就是拼接的查询字符串

             其中_parameter.getUsername()表示传递进来的参数

          -->

         <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底层去实现，而且还不容易出错，开发效率得到极大提高，十分推荐！

巴特西

Java EE学习笔记（八）

动态SQL

1、动态SQL中的元素

2、<if>元素

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

4、<when>、<trim>元素

5、<set>元素

6、<foreach>元素

7、<bind>元素

个人总结：

最新文章

热门文章