使用MyBatis过程中无可避免的要用到动态SQL做查询;有关MyBatis动态SQL的基础不在此记录,需要的同学可以去MyBatis官网查手册。
本文要解决的问题如下:
- 构建一种通用的数据结构(Object)用于生成MyBatis动态SQL中的where子句;
- 写一段通用的用于生成动态SQL中where子句的MyBatis配置,供其他Mapper文件引用;
生成动态SQL时需要支持的内容:
- 可以根据需要生成 AND 和 OR 子句;
- 可以根据需要生成 IN 子句;
- 可以根据需要生成 LIKE 子句;
通用 where 的 xml 配置段如下:
<mapper namespace="com.nlp365.pdf.mapper.base">
<sql id="andFiltersList">
<foreach collection="andFiltersList" open="(" close=")" separator="and" index="index" item="filters">
<include refid="com.nlp365.pdf.mapper.base.filters"></include>
</foreach>
</sql>
<sql id="orFiltersList">
<foreach collection="orFiltersList" open="(" close=")" separator="or" index="index" item="filters">
<include refid="com.nlp365.pdf.mapper.base.filters"></include>
</foreach>
</sql>
<sql id="andFilters">
<foreach collection="andFilters" open="(" close=")" separator="and" index="index" item="filter">
<include refid="com.nlp365.pdf.mapper.base.filter"></include>
</foreach>
</sql>
<sql id="orFilters">
<foreach collection="orFilters" open="(" close=")" separator="or" index="index" item="filter">
<include refid="com.nlp365.pdf.mapper.base.filter"></include>
</foreach>
</sql>
<sql id="filters">
<choose>
<when test="filters.type == 'and' and filters.list.size() > 0">
<foreach collection="filters.list" open="(" close=")" separator="and" index="index" item="filter">
<include refid="com.nlp365.pdf.mapper.base.filter"></include>
</foreach>
</when>
<when test="filters.type == 'or' and filters.list.size() > 0">
<foreach collection="filters.list" open="(" close=")" separator="or" index="index" item="filter">
<include refid="com.nlp365.pdf.mapper.base.filter"></include>
</foreach>
</when>
<when test="filters.type == 'in' and filters.inList.size() > 0">
${filters.key} in
<foreach collection="filters.inList" item="inItem" open="(" close=")" separator=",">
#{inItem}
</foreach>
</when>
<when test="filters.type == 'like'">
${filters.key} like #{filters.value}
</when>
<when test="filters.type == 'eq'">
${filters.key} = #{filters.value}
</when>
<when test="filters.type == 'ge'">
<![CDATA[${filters.key} >= #{filters.value}]]>
</when>
<when test="filters.type == 'le'">
<![CDATA[${filters.key} <= #{filters.value}]]>
</when>
<when test="filters.type == 'lt'">
<![CDATA[${filters.key} < #{filters.value}]]>
</when>
<when test="filters.type == 'gt'">
<![CDATA[${filters.key} > #{filters.value}]]>
</when>
<otherwise></otherwise>
</choose>
</sql>
<sql id="filter">
<choose>
<when test="filter.type == 'in' and filter.inList.size() > 0">
${filter.key} in
<foreach collection="filter.inList" item="inItem" open="(" close=")" separator=",">
#{inItem}
</foreach>
</when>
<when test="filter.type == 'like'">
${filter.key} like #{filter.value}
</when>
<when test="filter.type == 'eq'">
${filter.key} = #{filter.value}
</when>
<when test="filter.type == 'ge'">
<![CDATA[${filter.key} >= #{filter.value}]]>
</when>
<when test="filter.type == 'le'">
<![CDATA[${filter.key} <= #{filter.value}]]>
</when>
<when test="filter.type == 'lt'">
<![CDATA[${filter.key} < #{filter.value}]]>
</when>
<when test="filter.type == 'gt'">
<![CDATA[${filter.key} > #{filter.value}]]>
</when>
<otherwise></otherwise>
</choose>
</sql>
</mapper>
通用数据结构声明如下:
package com.nlp365.common;
import java.util.List;
public class Filter {
/**
* 过滤类型
* 支持的类型有
* and :对 list 中的 filter 使用 and 连接
* or : 对 list 中的 filter 使用 or 连接
* intIn : 对 intIn 中的 long 使用 `,` 连接 做 in 查询
* stringIn : 对 stringIn 中的 string 使用 `,` 连接 做 in 查询
* eq :key = value
* ge : key >= value
* le : key <= value
* gt : key > value
* lt : key < value
* like : key like value
*/
private String type;
/**
* 数据库字段
*/
private String key;
/**
* 值
*/
private String value;
/**
* 当 type 为 and 或 or 时,使用
*/
private List<Filter> list;
/**
* 当 type 为 in 时使用
*/
private List<String> inList;
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getKey() {
return "`" + key.replace(".", "`.`") + "`";
}
public String getOriginKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public List<Filter> getList() {
return list;
}
public void setList(List<Filter> list) {
this.list = list;
}
public List<String> getInList() {
return inList;
}
public void setInList(List<String> inList) {
this.inList = inList;
}
}
网友评论