SQL MyBatis的特性是什么，拼接SQL语句要注意什么

sql教程介绍SQL MyBatis的强大特性SQL

推荐（免费）：sql教程

动态 SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。

动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

准备

首先创建User实体类

public class User {
    
    private Integer id;
    
    private String username;
    
    private String userEmail;
    
    private String userCity;
    
    private Integer age;
}
    

创建user表

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(255) DEFAULT NULL,
    user_email varchar(255) DEFAULT NULL,
    user_city varchar(255) DEFAULT NULL,
    age int(11) DEFAULT NULL,
  PRIMARY KEY (id))

if

定义接口方法

public ListUser>
     findByUser(User user);
    

接口对应的 Mapper.xml 定义如下所示

select id="findByUser" resultType="com.example.mybatis.entity.User">
    
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where    if test="username != null and username != ''">

        username = #{
username}
        /if>
    
    if test="userEmail != null and userEmail != ''">

        and user_email = #{
userEmail}
        /if>
    
    if test="userCity != null and userCity != ''">

        and user_city = #{
userCity}
        /if>
    /select>

如果if标签上的test为true，那么if标签里面的SQL语句将会被拼接。

如果username、userEmail、userCity都不为空，那么SQL将会拼接成如下所示

select id, username, user_email userEmail, user_city userCity, age 
from user where username = ? and user_email = ? and user_city = ?

如果只有username不为空，那么SQL将会拼接成如下所示

select id, username, user_email userEmail, user_city userCity, age 
from user where username = ?

但是这种方式存在一个缺点，假设此时username为空，userEmail、userCity都不为空。

我们来分析动态 SQL 代码，现在没有给 username 赋值，即 username==null，所以 “username=#{ username} ” 这段代码不会添加到 SQL 语句中，那么最终拼接好的动态 SQL 是这样的：

select id, username, user_email userEmail, user_city userCity, age 
from user where and user_email = ? and user_city = ?

where 后面直接跟 and，很明显的语法错误，此时应该把紧跟在where后面的and删掉。为了解决这个问题，可以使用where标签。

where

将上面的SQL改成如下所示

    select id="findByUser" resultType="com.example.mybatis.entity.User">
    
        select
        id, username, user_email userEmail, user_city userCity, age        from user
        where>
    
            if test="username != null and username != ''">

                username = #{
username}
    
            /if>
    
            if test="userEmail != null and userEmail != ''">

                and user_email = #{
userEmail}
    
            /if>
    
            if test="userCity != null and userCity != ''">

                and user_city = #{
userCity}
    
            /if>
    
        /where>
    
    /select>
    

如果where标签里面的if标签有满足条件的，那么where标签就会被拼接成where语句，若if标签拼接的SQL最前面有and语句，那么这个and将会被删除。使用这种方法， 会自动删除SQL中不需要的关键字，所以一般 if 标签和 where 标签会组合起来使用。

trim

trim标签中的 prefix和 suffix属性会被用于生成实际的 SQL 语句，会和标签内部的语句拼接。

如果语句的前面或后面遇到 prefixOverrides或 suffixOverrides属性中指定的值，MyBatis 会自动将它们删除。在指定多个值的时候，别忘了每个值后面都要有一个空格，保证不会和后面的 SQL 连接在一起。

prefix：给拼接的SQL语句加一个前缀

suffix：给拼接的SQL语句加一个后缀

prefixOverrides：拼接的SQL语句前面遇到 prefixOverrides，MyBatis 会自动将它们删除

suffixOverrides：拼接的SQL语句后面遇到 suffixOverrides，MyBatis 会自动将它们删除

下面使用trim标签来实现where标签的功能

select id="findByUser" resultType="com.example.mybatis.entity.User">
    
        select
        id, username, user_email userEmail, user_city userCity, age
        from user        trim prefix="where" prefixOverrides="and">
    
            if test="username != null and username != ''">

                username = #{
username}
                /if>
    
            if test="userEmail != null and userEmail != ''">

                and user_email = #{
userEmail}
                /if>
    
            if test="userCity != null and userCity != ''">

                and user_city = #{
userCity}
                /if>
    
        /trim>
    
    /select>

如果username为空，userEmail和userCity不为空，那么if 标签拼接的SQL语句如下所示

and user_email = #{
userEmail}
 and user_city = #{
userCity}

因为trim标签设置了prefixOverrides=”and”，而上面的SQL前面有and语句，所以需要将上面的and语句删掉，又因为trim标签设置了prefix=”where”，所以需要在拼接的SQL语句前面加一个where语句

最后trim标签的SQL语句被拼接成如下所示

where user_email = #{
userEmail}
 and user_city = #{
userCity}
    

choose

有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

select id="findByUser" resultType="com.example.mybatis.entity.User">
    
        select
        id, username, user_email userEmail, user_city userCity, age
        from user        where>
    
            choose>
    
                when test="username != null and username != ''">

                    username = #{
username}
                    /when>
    
                when test="userEmail != null and userEmail != ''">

                    and user_email = #{
userEmail}
                    /when>
    
                when test="userCity != null and userCity != ''">

                    and user_city = #{
userCity}
                    /when>
    
            /choose>
    
        /where>
    
    /select>
    

set

set 标签用于 Update 操作，会自动根据参数选择生成 SQL 语句。

接口定义如下

public int updateUser(User user);
    

接口对应的 Mapper.xml 定义如下所示

update id="updateUser" parameterType="com.example.mybatis.entity.User">
    
       update user       set>
    
           if test="username != null and username != ''">

               username=#{
username}
    ,           /if>
    
           if test="userEmail != null and userEmail != ''">

               user_email=#{
userEmail}
    ,           /if>
    
           if test="userCity != null and userCity != ''">

               user_city=#{
userCity}
    ,           /if>
    
           if test="age != null">

              age=#{
age}
               /if>
    
       /set>

       where id=#{
id}
        /update>
    

foreach

foreach 标签可以迭代生成一系列值

*用于 SQL 的 in 语句 *

接口定义如下所示

public ListUser>
     getUsersByIds(ListInteger>
     ids);
    

接口对应的 Mapper.xml 定义如下所示

!--
        collection: 指定要遍历的集合
            默认情况下
                如果为Collection类型的，key为collection；
                如果为List类型的，key为list
                如果是数组类型，key为array
            可以通过@Param("ids")来指定key
        item: 将当前遍历的元素赋值给指定的变量
        open: 给遍历的结果添加一个开始字符
        close: 给遍历的结果添加一个结束字符
        separator: 每个元素之间的分隔符
    -->
    select id="getUsersByIds"
        resultType="com.example.mybatis.entity.User">
    
    select * from user
    where id in    foreach collection="list" item="id" open="(" close=")" separator=",">

        #{
id}
        /foreach>
    /select>
    

用于批量插入

接口定义如下所示

public int addUserList(ListUser>
     users);
    

接口对应的 Mapper.xml 定义如下所示

insert id="addUserList"
        parameterType="com.example.mybatis.entity.User">
    
    insert into user
    (username, user_email, user_city, age)
    values    foreach item="user"  collection="list" separator=",">

        (#{
user.username}
, #{
user.userEmail}
, #{
user.userCity}
, #{
user.age}
    )    /foreach>
    /insert>
    !--返回自增主键-->
    insert id="addUserList"
        parameterType="com.example.mybatis.entity.User"
        useGeneratedKeys="true"
        keyProperty="id">
    
    insert into user
    (username, user_email, user_city, age)
    values    foreach item="user"  collection="list" separator=",">

        (#{
user.username}
, #{
user.userEmail}
, #{
user.userCity}
, #{
user.age}
    )    /foreach>
    /insert>
    !--还可以这样写-->
    !--
    这种方式需要数据库连接属性设置allowMultiQueries=true
    这种分号分隔多个SQL还可以用于其他的批量操作，如修改、删除
-->
    insert id="addUserList"
        parameterType="com.example.mybatis.entity.User">
    
    foreach item="user"  collection="list" separator=";
    ">

        insert into user
        (username, user_email, user_city, age)
        values
        (#{
user.username}
, #{
user.userEmail}
, #{
user.userCity}
, #{
user.age}
    )    /foreach>
    /insert>
    !--如果是Oracle数据库，则需要这样写-->
    insert id="addUserList"
        parameterType="com.example.mybatis.entity.User">
    
    foreach item="user" open="begin" close="end;
    "  collection="list">

        insert into user
        (username, user_email, user_city, age)
        values
        (#{
user.username}
, #{
user.userEmail}
, #{
user.userCity}
, #{
user.age}
    );
        /foreach>
    /insert>