Mybatis 动态SQL编写

Mybatis学习

mybatis http://www.mybatis.org/mybatis-3/zh/index.html

动态SQL

• MyBatis的加强大在于他的动态SQL
• 动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
• if（简单的条件判断）
• choose(when,otherwize)，想到与Java中的switch，与Jstl中的choose类似
• trim(对包含的内容加prefix或suffix等)
• where(主要简化sql语句中的where条件判断，智能处理and or，不必担心多余导致语法错误)
• set（用于update 更新）
• foreach（sql 中的 in 语句，查询时特别管用）

if

if就是简单的条件判断，利用if语句我们可以实现某些简单的条件选择。动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分

<select id="findUser" parameterType="User" resultMap="User">
    select  id ,username,age from  user where 1=1
    <if test="username!=null and username!=''">
        and username=#{username}
    </if>
</select>

choose(when,otherwize)

when元素表示当when中的条件满足的时候就输出其中的内容，跟JAVA中的switch效果差不多的是按照条件的顺序，当when中有条件满足的时候，
就会跳出choose，即所有的when和otherwise条件中，只有一个会输出，当所有的我很条件都不满足的时候就输出otherwise中的内容

<select id="chooseTest" parameterType="User" resultMap="User">
    select  id,username,age where i=1
    <choose>
        <when test="username!=null and username!=''">
            and username=#{username}
        </when>
        <otherwise>
            and age=#{age}
        </otherwise>
        
    </choose>     
</select>

trim(prefix,suffix),代替where

trim元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是prefix和suffix；可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是prefixOverrides和suffixOverrides；正因为trim有这样的功能，所以我们也可以非常简单的利用trim来代替where元素的功能

<select id="trimTest" parameterType="User" resultMap="User">
    select id,username,age form user
    <trim prefix="where" prefixOverrides="and | or">
        <if test="username!=null and username.length()>0">
            username like CONCAT(CONCAT('%',#{usrname}),'%')
        </if>
        <if test="age!=null and age!=''">
            and age=#{age}
        </if>
    </trim>
</select>

trim代替set标签

if/trim代替set(判断参数) - 将实体类不为空的属性更新

<update id="updateUser" parameterType="User" >
    update  user
    <trim prefix="set" suffixOverrides=",">
        <if test="username!=null">
            username=#{username},
        </if>
        <if test="age!=null">
            age=${age}
        </if>
    </trim>
    <where>
        id=${id}
    </where>
</update>

set

set元素主要是用在更新操作的时候，它的主要功能和where元素其实是差不多的，主要是在set标签包含的语句前输出一个set。如果包含的语句是以逗号结束的话将会把该逗号忽略。如果set包含的内容为空的话则会出错。有了set元素我们就可以动态的更新那些修改了的字段，而不用每次更新全部字段

<update id="updateUser" parameterType="User">
    update user 
    <set>
        <if test="username!=null">
          username=#{username}  
        </if>
    </set>
    where id=${id}
</update>

foreach

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有item，index，collection，open，separator，close

• item 表示集合中的每一个元素进行迭代时的别名
• index 指定一个名字，用于表示迭代过程中每次迭代的位置
• open 表示语句以什么开始
• separator 表示在每次进行迭代之间以什么符号作为分隔符
• close 表示以什么结束
• collection 最关键的也是最容易出错的，该属性必须指定且在不同情况下属性值不一样
  • 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
  • 如果传入的是单参数结参数类型是一个array数组的时候，collection的属性值为array
  • 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key
• list类型

<select id="foreachList" resultMap="User">
    select * from user where id in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

// public List<User> foreachList(List<Integer> ids);

• array数组类型

<select id="foreachArray" resultMap="User">
    select * from user where id in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

// public List<User> foreachArray(int[] ids);

• Map 封装

<select id="foreacherMap" resultType="User">
    select * from user where username like CONCAT(CONCAT('%',#{username}),'%') and  id in
    <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

<!--代码实现-->
@Test  
public void foreachMap() {  
    SqlSession session = Util.getSqlSessionFactory().openSession();  
    UserMapper userMapper = session.getMapper(UserMapper.class);  
    final List<Integer> ids = new ArrayList<Integer>();  
    ids.add(1);  
    ids.add(2);  
    ids.add(3);  
    Map<String, Object> params = new HashMap<String, Object>();  
    params.put("ids", ids);  
    params.put("username", "张三");  
    List<User> users = userMapper.foreachMap(params);  
    for (User user : users)  
        System.out.println(user);  
    session.close();  
}  

Mybatis防止SQL注入

Mybatis是启用SQL预编译功能的，底层实现原理：是JDBC中的PreparedStatement类在起作用，PreparedStatement是我们很熟悉的Statement的子类，它的对象包含了编译好的SQL语句。这种“准备好”的方式不仅能提高安全性，而且在多次执行同一个SQL时，能够提高效率。原因是SQL已编译好，再次执行时无需再编译。

• #{}：相当于JDBC中的PreparedStatement,是经过预编译的，是安全的
• ${}：输出变量的值，会直接参与SQL编译,是未经过预编译的，仅仅是取变量的值，存在SQL注入，是非安全的

<select id="getUserById" parameterType="int" resultType="User">
    select id,username,age from user
    where id=#{id}
</select>

这里，parameterType表示了输入的参数类型，resultType表示了输出的参数类型。回应上文，如果我们想防止SQL注入，理所当然地要在输入参数上下功夫.当输入参数是打印出的sql是：

SELECT id,username,age FROM user WHERE id=?

不管输入什么参数，打印出的SQL都是这样的。这是因为MyBatis启用了预编译功能，在SQL执行前，会先将上面的SQL发送给数据库进行编译；执行时，直接使用编译好的SQL，替换占位符“?”就可以了。因为SQL注入只能对编译过程起作用，所以这样的方式就很好地避免了SQL注入的问题

---

以下将 #{} 换成 ${},给参数"id"赋值2，打印sql如下

SELECT id,username,age FROM user WHERE id=2

差异

// id=3,username=admin or 1=1

select * from user where id=#{id} and username=${username}

<!--执行如下-->
select * from where id=? and username=admin or 1=1

//以上执行时会执行or这样就存在安全问题

//这里用户username=id

select * from user order by ${username}

<!--执行如下-->
select * from user order by id

//显然，这样是无法阻止SQL注入的