关于MyBatis动态元素与Where 1=1的作用

在工作中有时候会用到动态SQL标签，此处写明一些关于以前用的少的动态标签详解与用处。

以下详情请查看·：https://www.hxstrive.com/subject/mybatis/231.htm

关于Mybatis的SQL动态标签之Where 元素

如果我们有下面一个示例:

<select id="findActiveBlogLike" parameterType="Blog" resultType="Blog">
    SELECT * FROM BLOG
    WHERE
    <if test="state != null">
        state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND title like #{author.name}
    </if>
</select>

 如果这些条件都没有匹配上将会发生什么？这条 SQL 结束时就会成这样：

SELECT * FROM BLOG
WHERE

这会导致查询失败。如果仅仅第二个条件匹配是什么样的？这条 SQL 结束时就会是这样：

SELECT * FROM BLOG
WHERE
AND title like "someTitle"

这个查询也会失败。这个问题不能简单的用条件来解决，如果你从来没有这样写过，那么你以后也不会这样来写。

MyBatis 有一个简单的处理，这在 90%的情况下都会有用。而在不能使用的地方，你可以自定义处理方式。加上一个简单的改变，所有事情都会顺利进行：

<select id="findActiveBlogLike" parameterType="Blog" resultType="Blog">
    SELECT * FROM BLOG
    <where>
        <if test="state != null">
            state = #{state}
        </if>
        <if test="title != null">
            AND title like #{title}
        </if>
        <if test="author != null and author.name != null">
            AND title like #{author.name}
        </if>
    </where>
</select>

<where> 元素知道如果由被包含的标记（如：<if>标签）返回了任意内容，则<where>标签仅仅插入“WHERE”字符串。如果 <where> 标签包含的标签的内容以“AND”或“ OR”开头的内容，那么 MyBatis 会将 where 后面紧接的 <if> 标签的内容前面的 AND 和 OR 移除。

示例代码

（1）MyBatis 配置文件 mybatis-cfg.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="database.properties"/>
    <environments default="MySqlDatabase" >
        <environment id="MySqlDatabase" >
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/hxstrive/mybatis/dynamic_sql/demo3/UserMapper.xml" />
    </mappers>
</configuration>

（2）Java 实体Bean UserBean.java，代码如下：

package com.hxstrive.mybatis.dynamic_sql.demo3;
 
public class UserBean {
   private Integer userId;
   private String name;
   private String sex;
   private Integer age;
   // 忽略 getter 和 setter
   @Override
   public String toString() {
      return "UserBean{" +
            "userId=" + userId +
            ", name='" + name + '\'' +
            ", sex='" + sex + '\'' +
            ", age=" + age +
            '}';
   }
}

（3）定义 Mapper 文件

a、UserMapper.java

package com.hxstrive.mybatis.dynamic_sql.demo3;
 
import org.apache.ibatis.annotations.Param;
import java.util.List;
 
public interface UserMapper {
   List<UserBean> getUserList(@Param("username") String username, @Param("userId") int userId);
}

b、UserMapper.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.hxstrive.mybatis.dynamic_sql.demo3.UserMapper">
   <!-- 映射结果 -->
   <resultMap id="RESULT_MAP" type="com.hxstrive.mybatis.dynamic_sql.demo3.UserBean">
      <id column="user_id" jdbcType="INTEGER" property="userId" />
      <result column="name" jdbcType="VARCHAR" property="name" />
      <result column="sex" jdbcType="VARCHAR" property="sex" />
      <result column="age" jdbcType="INTEGER" property="age" />
   </resultMap>
 
   <!-- 动态添加 where 条件 -->
   <select id="getUserList" resultMap="RESULT_MAP">
      select `user_id`, `name`, `age`, `sex` from `user`
      <where>
         <if test="username != null and username != ''">
            and `name` like #{username}
         </if>
         <if test="userId > 0">
            and `user_id`=#{userId}
         </if>
      </where>
   </select>
 
</mapper>

（4）客户端代码如下：

package com.hxstrive.mybatis.dynamic_sql.demo3;
 
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
 
public class DynamicSqlDemo {
 
   public static void main(String[] args) throws Exception {
      String cfgName = "com/hxstrive/mybatis/dynamic_sql/demo3/mybatis-cfg.xml";
      InputStream input = Resources.getResourceAsStream(cfgName);
      SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
      SqlSessionFactory sqlFactory = factoryBuilder.build(input);
      getUserList(sqlFactory);
   }
 
   private static void getUserList(SqlSessionFactory sqlFactory) {
      SqlSession sqlSession = sqlFactory.openSession(true);
      UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
      List<UserBean> userBeanList = userMapper.getUserList(null, 1);
      for(UserBean userBean : userBeanList) {
         System.out.println(userBean);
      }
   }
 
}

运行客户端代码，输出结果如下：

2020-09-10 22:07:51,251 DEBUG [org.apache.ibatis.logging.LogFactory] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
2020-09-10 22:07:51,292 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections.
2020-09-10 22:07:51,292 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections.
2020-09-10 22:07:51,292 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections.
2020-09-10 22:07:51,292 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections.
2020-09-10 22:07:51,511 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
2020-09-10 22:07:51,959 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Created connection 1297149880.
2020-09-10 22:07:51,962 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo3.UserMapper.getUserList] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@4d50efb8]
2020-09-10 22:07:51,963 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo3.UserMapper.getUserList] - ==>  Preparing: select `user_id`, `name`, `age`, `sex` from `user` WHERE `user_id`=? 
2020-09-10 22:07:52,023 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo3.UserMapper.getUserList] - ==> Parameters: 1(Integer)
2020-09-10 22:07:52,063 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo3.UserMapper.getUserList] - <==      Total: 1
UserBean{userId=1, name='赫仑', sex='男', age=27}

上面示例中，执行 getUserList 时，参数 username=null，userId=1，将返回如下 SQL 语句：

select `user_id`, `name`, `age`, `sex` from `user` WHERE `user_id`=?

 按照上面 Mapper xml 的定义，如果 userId 大于 0，则输出 “and `user_id`=#{userId}”，与其他 SQL 语句拼接在一起，则应该如下：

select `user_id`, `name`, `age`, `sex` from `user`
where
and `user_id`=#{userId}

上面 SQL 是有问题的。但是，MySQL 自动将 “where and `user_id`=#{userId}”之前的 and 去除掉了。也就导致上面示例运行成功了！！

 

除了以上方法以外也可不用Where元素标签，增加Where 1 = 1语句,如：

<select id="findActiveBlogLike" parameterType="Blog" resultType="Blog">
    SELECT * FROM BLOG
    WHERE 1 = 1
    <if test="state != null">
      AND state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND title like #{author.name}
    </if>
</select>

尚且不清Where 1 = 1的效率，与Where标签的效率哪个高，这里以后在做讨论