MyBatis学习之动态SQL

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

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

方法	说明
<if>	判断语句，用于单条件分支判断
<choose>、<when>、<otherwise>	相当于Java中的switch...case...default语句，用于多条件分支判断
<where>、<trim>、<set>	辅助元素，用于处理一些SQL拼装、特殊字符问题
<foreach>	循环语句，常用于in语句等列举条件中
<bind>	从OGNL表达式中创建一个变量，并将其绑定到上下文，常用于模糊查询的sql中

  1. <if>元素
  在MyBatis中，<if>元素是最常用的判断语句，它类似于Java中的if语句，主要用于实现某些简单的条件选择。其基本使用示例如下：

select * from t_user 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. <choose>、<when>、<otherwise>元素

select * from t_user 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>

  映射文件中编写的SQL后面都加入了“where 1=1”的条件，如果将where后“1=1”的条件去掉，那么MyBatis所拼接出来的SQL将会如下所示，可以看出SQL语句明显存在SQL语法错误。

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

  为什么？
  加入条件“1=1”后，既保证了where后面的条件成立，又避免了where后面第一个词是and或者or之类的关键词。

  3. <where>、<trim>、<set>元素
  针对上述情况中“where 1=1”，在MyBatis的SQL中可以使用或元素进行动态处理。
  1.<where>元素处理:
  <where>会自动判断SQL语句，只有<where>元素内的条件成立时，才会在拼接SQL中加入where关键字，否则将不会添加；还会去除多余的“AND”或“OR”。

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

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

 select * from t_user
     <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>

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

<update id="updateUser"  parameterType="com.example.po.User">
        update t_user 
        <set>
            <if test="username !=null and username !=''">
                  username=#{username},
            </if>
            <if test="jobs !=null and jobs !=''">
                  jobs=#{jobs},
            </if>
        </set>
        where id=#{id}
</update>

  4. <foreach>元素
  <foreach>主要属性：

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

<select id="findUserByIds" parameterType="List"
                         resultType="com.example.po.User">
           select * from t_user where id in
            <foreach item="id" index="index" collection="list" 
                            open="(" separator="," close=")">
                   #{id}
            </foreach>
     </select>

  在使用<foreach>时最关键也是最容易出错的就是collection属性，该属性是必须指定的，而且在不同情况下，该属性的值是不一样的。主要有以下3种情况：
  1.如果传入的是单参数且参数类型是一个数组或者List时，collection属性值分别为array和list（或collection）。
  2.如果传入的参数是多个时，就需要把它们封装成一个Map，单参数也可以封装成Map集合，这时collection属性值就为Map的键。
  3.如果传入的参数是POJO包装类时，collection属性值就为该包装类中需要进行遍历的数组或集合的属性名。

  5. <bind>元素
  模糊查询的SQL语句：

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

  上述SQL语句有什么不妥？
  1.如果使用“${}”进行字符串拼接，则无法防止SQL注入问题；
  2.如果改用concat函数进行拼接，则只针对MySQL数据库有效；
  3.如果改用“||”进行字符串拼接，则只针对Oracle数据库有效。

  可以看出，映射文件中的SQL就要根据不同的情况提供不同形式的实现，这显然是比较麻烦的，且不利于项目的移植。为了减少这种麻烦，就可以使用MyBatis的元素来解决这一问题。
  MyBatis的<bind>元素可以通过OGNL表达式来创建一个上下文变量，其使用方式如下：

 <select id="findUserByName" parameterType="com.example.po.User"
                 resultType="com.example.po.User">
          <!--_parameter.getUsername()表示传递进来的参数（也可以直接写成对应的参数变量名，如username） -->
          <bind name="pattern_username" value="'%'+_parameter.getUsername()+'%'" />
           select * from t_user 
           where 
           username like #{pattern_username}
           <!-- 需要的地方直接引用<bind>元素的name属性值即可-->
     </select>

  ☆☆☆Eclipse实现在表中修改update的sql语句，改为动态sql，实现除id外，某字段赋值为空时，对该字段不做修改(类的成员变量名与数据表的字段名不同)。
  1.在Eclipse中，创建项目，将所需要的JAR包复制到项目中的WebContent的WEB-INF的lib下，并发布到类路径下。
  
        2.在src目录下，创建db.properties，log4j.properties文件，以及mybatis-config.xml文件。
        db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/d1?useSSL=false
jdbc.username=root
jdbc.password=root

        log4j.properties

# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger=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

        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" />
 <!-- 定义别名 -->
 <typeAliases>
  <!-- <typeAlias alias="student" type="beans.Student" /> -->
  <package name="beans" />
 </typeAliases>
 
 <!--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="mapper/StudentMapper.xml" />
 </mappers>
</configuration>

        3.在src中创建beans包，在beans包中创建Student类，创建成员变量和setXXX、getXXX方法。

package beans;

import java.io.Serializable;
import java.text.DateFormat;

import java.util.Date;
public class Student implements Serializable{
 /*serialVersionUID作用： 
 序列化时为了保持版本的兼容性，即在版本升级时反序列化仍保持对象的唯一性。 */
 private static final long serialVersionUID = 1L;
 private Integer id;
 private String stuno;
 private String name;
 private Date birthday;
 private String phone;
 private float score;
 public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 public String getStuno() {
  return stuno;
 }
 public void setStuno(String stuno) {
  this.stuno = stuno;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public Date getBirthday() {
  return birthday;
 }
 public void setBirthday(Date birthday) {
  this.birthday = birthday;
 }
 public String getPhone() {
  return phone;
 }
 public void setPhone(String phone) {
  this.phone = phone;
 }
 public float getScore() {
  return score;
 }
 public void setScore(float score) {
  this.score = score;
 }
 @Override
 public String toString() {
  return "id=" + id + ", stuno=" + stuno + 
    ", name=" + name + ", birthday="
    + DateFormat.getDateInstance().format(birthday)
    + ", phone=" + phone + ", score=" + score;
 } 
}

        4.在src中创建tools包，在tools包中创建工具类，方便使用。

package tools;

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 DBTools {
 private static SqlSessionFactory sqlSessionFactory = null;
 // 初始化SqlSessionFactory对象
 static {
  try {
   // 使用MyBatis提供的Resources类加载MyBatis的配置文件
   Reader reader = 
     Resources.getResourceAsReader("mybatis-config.xml");
   // 构建SqlSessionFactory工厂
   sqlSessionFactory = 
     new SqlSessionFactoryBuilder().build(reader);
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
 // 获取SqlSession对象的静态方法
 public static SqlSession getSession() {
  return sqlSessionFactory.openSession();
 }
}

        5.在src中创建mapper包，在mapper包中创建映射文件StudentMapper.xml,以及在mybatis-config.xml中配置Mapper的位置(在上面mybatis-config.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">
<!-- namespace表示命名空间 -->
<mapper namespace="mapper.StudentMapper">
<resultMap type="beans.Student" id="resultMap">
 <id property="id" column="id2"/>
 <result property="stuno" column="stuno2"/>
 <result property="name" column="name2"/> 
 <result property="birthday" column="birthday2" jdbcType="DATE" javaType="java.util.Date"/>
 <result property="phone" column="phone2"/>
 <result property="score" column="score2"/>
</resultMap>
 <!-- 更新客户信息 -->
 <update id="updateStudent" parameterType="beans.Student">
  update t_stu2
  <set>
   <if test="stuno !=null and stuno !=''">
    stuno2=#{stuno},
   </if>
   <if test="name !=null and name !=''">
    name2=#{name},
   </if>
   <if test="birthday !=null and birthday !=''">
    birthday2=#{birthday},
   </if>
   <if test="phone !=null and phone !=''">
    phone2=#{phone},
   </if>
   <if test="score !=null and score !=''">
    score2=#{score},
   </if>
  </set>
  where id2=#{id}
 </update>
</mapper>

        6.在src中创建test包，在test包包中创建测试类Test类。

package test;
import java.text.ParseException;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import beans.Student;
import tools.DBTools;
public class test {
 @Test
 public void updateStudentTest() throws ParseException{
  SqlSession sqlSession = DBTools.getSession();
  // SqlSession执行更新操作
  // 创建Customer对象，并向对象中添加数据
  Student student = new Student();
  student.setId(28);
  student.setStuno("1001");
  student.setName("wangwu");
  student.setScore(90);
  // 执行sqlSession的更新方法，返回的是SQL语句影响的行数
  int rows = sqlSession.update("mapper.StudentMapper.updateStudent", student);
  // 通过返回结果判断更新操作是否执行成功
  if(rows > 0){
   System.out.println("您成功修改了"+rows+"条数据！");
  }else{
   System.out.println("执行修改操作失败！！！");
  }
  // 提交事务
  sqlSession.commit();
  // 关闭SqlSession
  sqlSession.close();
  }
}

        运行结果：
        
        表中的变化如下，可以看到对birthday、phone未赋值，表的数据使用动态sql未变为null。
        
　　这就是MyBatis动态SQL的简单使用，如果转载以及CV操作，请务必注明出处，谢谢！