【Mybatis】13 动态SQL

还是先准备演示环境

数据库：

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `last_name` varchar(10) DEFAULT NULL,
  `gender` int DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

【数据随便加，注意写一个同名不同性的】

 

实体类

package cn.dai.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.type.Alias;

/**
 * @author ArkD42
 * @file Mybatis
 * @create 2020 - 05 - 30 - 20:35
 */

@Alias("user")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

    private Integer id;
    private String last_name;
    private Boolean gender;
}

映射接口

package cn.dai.mapper;

import cn.dai.pojo.User;

import java.util.List;

/**
 * @author ArkD42
 * @file Mybatis
 * @create 2020 - 05 - 30 - 20:36
 */
public interface UserMapper {

    List<User> getUsersByNameAndGender(User user);
}

映射器

<?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接口名称-->
<mapper namespace="cn.dai.mapper.UserMapper">

    <select id="getUsersByNameAndGender" parameterType="user" resultType="user">
        SELECT
            *
        FROM
            t_user
        WHERE
            last_name = #{last_name}
            and gender = #{gender}
    </select>
</mapper>

测试

import cn.dai.mapper.UserMapper;
import cn.dai.pojo.User;
import cn.dai.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * @author ArkD42
 * @file Mybatis
 * @create 2020 - 05 - 30 - 21:17
 */
public class BuildTest {

    @Test
    public void queryUsersByNameAndGender(){
        SqlSession sqlSession = MybatisUtil.getSqlSession(true);
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> users = userMapper.getUsersByNameAndGender(new User(null, "阿伟", 1));

        for (User user: users) {
            System.out.println(user);
        }

        sqlSession.close();
    }
}

结果

 

 

1、IF 语句

默认情况性别的状态码就是1和0

但是不排除乱写的情况，比如注入其他的数字

 

这时候可以使用动态SQL帮助我们进行一个逻辑控制

如果注入了不符号我们期望的数值，那么将设置为默认值即可

<?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接口名称-->
<mapper namespace="cn.dai.mapper.UserMapper">

    <select id="getUsersByNameAndGender" parameterType="user" resultType="user">
        SELECT
            *
        FROM
            t_user
        WHERE
            last_name = #{last_name}
        <if test="gender==0 or gender==1">
            and gender = #{gender}
        </if>
        
    </select>
</mapper>

似乎mybatis这样查询的会是0&1的结果，如果我们希望只查询0，那么就不要写1即可

 

另外两个条件可以使用IF

    <select id="getUsersByNameAndGender" parameterType="user" resultType="user">
        SELECT
            *
        FROM
            t_user
        WHERE
        <if test="last_name != null">
            last_name = #{last_name}
        </if>
            
        <if test="gender==0 or gender==1">
            and gender = #{gender}
        </if>

    </select>

 

2、WHERE 语句

如果出现的元数据问题不是我们的性别状态，而是前面的姓名问题

姓名注入了空值，那么程序将会异常中断

 

直接打印这样的错误信息

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
### The error may exist in mapper/UserMapper.xml
### The error may involve cn.dai.mapper.UserMapper.getUsersByNameAndGender-Inline
### The error occurred while setting parameters
### SQL: SELECT             *         FROM             t_user         WHERE
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

其实道理也很简单

 

我们为了避免这样的SQL语法错误，提供了更好的动态SQL标签：使用Where

<?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接口名称-->
<mapper namespace="cn.dai.mapper.UserMapper">

    <select id="getUsersByNameAndGender" parameterType="user" resultType="user">
        SELECT
            *
        FROM
            t_user
        <where>
            <if test="last_name != null">
                last_name = #{last_name}
            </if>

            <if test="gender==0 or gender==1">
                and gender = #{gender}
            </if>
        </where>
    </select>
    
</mapper>

查询结果：

当姓名为空，就按照下面的性别条件查询

不再有异常问题

这是因为WHERE标签，可以动态的实现AND或者OR条件连接

但是不要删除了条件连接，这样还是会报语法错误

所以WHERE只是简单的判断和补充

 

3、TRIM 语句

可以动态的添加删除内容

-　prefix　前置添加

-　suffix　后置添加

-　suffixOverrides　前置删除

-　prefixOverrides　后置删除

    <select id="getUsersByNameAndGender" parameterType="user" resultType="user">
        SELECT
            *
        FROM
            t_user
        <trim suffixOverrides="and" prefix="where">
            <if test="last_name != null">
                last_name = #{last_name} and
            </if>

            <if test="gender==0 or gender==1">
                gender = #{gender}
            </if>
        </trim>
    </select>

不是很能明白前缀和后缀

 

4、CHOOSE(WHEN & OTHERS) 语句

可以执行多个条件判断，问题是只能有一个条件适用

【根据上面的结果能推断的适用条件是搭配其他的前置条件，并成为最后一个多选一的关键条件】

 

就纯语法案例：

-　有名字，按名字查

-　没有按性别查

-　再没有自己写一个条件查

    <select id="getUsersByNameAndGender" parameterType="user" resultType="user">
        SELECT
            *
        FROM
            t_user
        WHERE
        <choose>
            <when test="last_name != null">
                last_name = #{last_name}
            </when>

            <when test="gender == 1 or gender == 0">
                gender = #{gender}
            </when>

            <otherwise>
                id between 1 and 10
            </otherwise>
        </choose>
    </select>

测试一个上述条件都没有最后执行otherwise的结果

[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Opening JDBC Connection
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Created connection 11939193.
[cn.dai.mapper.UserMapper.getUsersByNameAndGender]-==>  Preparing: SELECT * FROM t_user WHERE gender = ? 
[cn.dai.mapper.UserMapper.getUsersByNameAndGender]-==> Parameters: 0(Integer)
[cn.dai.mapper.UserMapper.getUsersByNameAndGender]-<==      Total: 4
User(id=1, last_name=阿伟, gender=0)
User(id=3, last_name=杰哥, gender=0)
User(id=4, last_name=阿强, gender=0)
User(id=5, last_name=虞姬, gender=0)
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@b62d79]
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 11939193 to pool.

Process finished with exit code 0

 

5、SET 语句

用于删除更新多个字段的逗号

    <update id="updateUserById" parameterType="int" >
        UPDATE 
            t_user
        <set>
            <if test="last_name != null">
                last_name = #{last_name}，
            </if>

            <if test="gender == 1 or gender == 0">
                gender = #{gender}
            </if>
        </set>
        WHERE 
            id = #{id}
    </update>

动态的删除逗号分隔

 

6、FOREACH语句

在之前的一个动态SQL标签中

我们这样进行筛选一个连续的部分，或者说是使用in语句也可以

Mybatis提供一个FOREACH标签来完成这个功能

List<User> getUserByFragment(List<Integer> id);

查看约束我们发现这个遍历标签只允许集合类型

映射器配置

    <select id="getUsersByFragment" parameterType="int" resultType="user" >
        SELECT *
        FROM t_user
        <where>
            id in
            <foreach 
                    collection="id"
                    item="el"
                    index="(" 
                    separator=","
                    close=")"
            >
                #{el}
            </foreach>
        </where>

    </select>

截图说明：

但是这样的结果更像是拼接出来的一样，估计底层就是拼接吧。。。

上面的这些都不再做演示了

 

Script标签

使用注解SQL实现时，可以使用script标签完成动态SQL

比如:

@Update({"<script>",
      "update Author",
      "  <set>",
      "    <if test='username != null'>username=#{username},</if>",
      "    <if test='password != null'>password=#{password},</if>",
      "    <if test='email != null'>email=#{email},</if>",
      "    <if test='bio != null'>bio=#{bio}</if>",
      "  </set>",
      "where id=#{id}",
      "</script>"})
    void updateAuthorValues(Author author);

 

SQL片段

这个元素可以用来定义可重用的 SQL 代码片段，以便在其它语句中使用。

参数可以静态地（在加载的时候）确定下来，并且可以在不同的 include 元素中定义不同的参数值。

比如：

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

这个 SQL 片段可以在其它语句中使用，例如：

<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns"><property name="alias" value="t1"/></include>,
    <include refid="userColumns"><property name="alias" value="t2"/></include>
  from some_table t1
    cross join some_table t2
</select>

 

也可以在 include 元素的 refid 属性或内部语句中使用属性值，例如：

<sql id="sometable">
  ${prefix}Table
</sql>

<sql id="someinclude">
  from
    <include refid="${include_target}"/>
</sql>

<select id="select" resultType="map">
  select
    field1, field2, field3
  <include refid="someinclude">
    <property name="prefix" value="Some"/>
    <property name="include_target" value="sometable"/>
  </include>
</select>