有时我们不想应用所有的条件，相反我们想选择很多情况下的一种。和 Java 中的 switch 语句相似， MyBatis 提供 choose 元素。
我们使用上面的示例，但是现在我们来搜索当 title 提供时仅有 title 条件，当 author 提供时仅有 author 条件。如果二者都没提供，只返回 featured blogs（也许是由管理员策略地选择的结果列表，而不是返回大量没有意义的随机博客结果列表）。
<select id="findActiveBlogLike" parameterType="Blog" resultType="Blog">
SELECT * FROM BLOG WHERE state="ACTIVE"
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND title like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>（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/demo2/UserMapper.xml" />
</mappers>
</configuration>（2）定义实体JavaBean UserBean.java，代码如下：
package com.hxstrive.mybatis.dynamic_sql.demo2;
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.demo2;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UserMapper {
List<UserBean> getUserList(@Param("username") String username, @Param("age") Integer age);
}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.demo2.UserMapper">
<!-- 映射结果 -->
<resultMap id="RESULT_MAP" type="com.hxstrive.mybatis.dynamic_sql.demo2.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 `user_id` is not null
<choose>
<when test="username != null and username != ''">
and `name` like #{username}
</when>
<when test="age != null">
and `age` >= #{age}
</when>
<otherwise>
and `sex` in ('男', '女')
</otherwise>
</choose>
</select>
</mapper>（4）客户端代码如下：
package com.hxstrive.mybatis.dynamic_sql.demo2;
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/demo2/mybatis-cfg.xml";
InputStream input = Resources.getResourceAsStream(cfgName);
SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlFactory = factoryBuilder.build(input);
getUserList(sqlFactory, "张", -1);
getUserList(sqlFactory, null, 30);
}
private static void getUserList(SqlSessionFactory sqlFactory, String username, Integer age) {
if(null != username && username.trim().length() > 0) {
username = "%" + username + "%";
}
SqlSession sqlSession = sqlFactory.openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<UserBean> userBeanList = userMapper.getUserList(username, age);
for (UserBean userBean : userBeanList) {
System.out.println(userBean);
}
sqlSession.close();
}
}运行上面客户端代码，输出结果如下：
2020-09-10 21:39:44,359 DEBUG [org.apache.ibatis.logging.LogFactory] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
2020-09-10 21:39:44,395 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections.
2020-09-10 21:39:44,395 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections.
2020-09-10 21:39:44,395 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections.
2020-09-10 21:39:44,396 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections.
2020-09-10 21:39:44,629 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
2020-09-10 21:39:45,139 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Created connection 141289226.
2020-09-10 21:39:45,142 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo2.UserMapper.getUserList] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@86be70a]
2020-09-10 21:39:45,143 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo2.UserMapper.getUserList] - ==> Preparing: select `user_id`, `name`, `age`, `sex` from `user` where `user_id` is not null and `name` like ?
2020-09-10 21:39:45,254 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo2.UserMapper.getUserList] - ==> Parameters: %张%(String)
2020-09-10 21:39:45,335 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo2.UserMapper.getUserList] - <== Total: 1
UserBean{userId=2, name='张小凡', sex='男', age=30}
2020-09-10 21:39:45,337 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@86be70a]
2020-09-10 21:39:45,337 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Returned connection 141289226 to pool.
2020-09-10 21:39:45,337 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
2020-09-10 21:39:45,338 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Checked out connection 141289226 from pool.
2020-09-10 21:39:45,338 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo2.UserMapper.getUserList] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@86be70a]
2020-09-10 21:39:45,338 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo2.UserMapper.getUserList] - ==> Preparing: select `user_id`, `name`, `age`, `sex` from `user` where `user_id` is not null and `age` >= ?
2020-09-10 21:39:45,339 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo2.UserMapper.getUserList] - ==> Parameters: 30(Integer)
2020-09-10 21:39:45,375 DEBUG [com.hxstrive.mybatis.dynamic_sql.demo2.UserMapper.getUserList] - <== Total: 2
UserBean{userId=2, name='张小凡', sex='男', age=30}
UserBean{userId=3, name='叶星云', sex='女', age=31}
2020-09-10 21:39:45,375 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@86be70a]
2020-09-10 21:39:45,376 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Returned connection 141289226 to pool.其中：
（1）如果 username="张"，age=-1，执行 getUserList 将输出如下 SQL 语句：
select `user_id`, `name`, `age`, `sex` from `user` where `user_id` is not null and `name` like ?
（2）如果 username=null，age=30，执行 getUserList 将输出如下 SQL 语句：
select `user_id`, `name`, `age`, `sex` from `user` where `user_id` is not null and `age` >= ?
川公网安备51010802032098
