描述
一直以来动态查询都是Jpa的一个痛点。最近做的项目使用Spring Data Jpa作为持久层，每次碰见动态查询都很头疼，所以就想着封装一个工具用来处理动态查询。
思路
定义一个工具类，在工具类中动态拼接sql条件，最后使用JdbcTemplate执行拼接好的sql。
实现
定义工具类
定义一个类DynamicQueryUtil，并在DynamicQueryUtil类中定义一个StringBuilder类型的属性sqlBuilder。sqlBuilder属性用于拼接sql字符串。
/**
* 动态查询工具
*
* @author 不可食用盐
*/
@Slf4j
public class DynamicQueryUtil {
private StringBuilder sqlBuilder;
// 提供静态方法创建DynamicQueryUtil
public static DynamicQueryUtil of(String sql) {
if (StrUtil.isBlank(sql)){
throw new RuntimeException("sql不能为空");
}
DynamicQueryUtil dynamicQueryUtil = new DynamicQueryUtil();
dynamicQueryUtil.sqlBuilder = new StringBuilder(sql);
return dynamicQueryUtil;
}
// 私有化构造方法，防止直接创建
private DynamicQueryUtil(){}
}
定义拼接参数的方法
定义动态拼接参数的方法，思路即使参数不为空的时候，把对应的查询条件拼接在sqlBuilder中。其中
/**
* 动态追加查询条件
*
* @param condition 条件
* @param value 条件值
* @author 不可食用盐
*/
public DynamicQueryUtil dynamicAppendCondition(String condition, String value) {
if (StrUtil.isNotBlank(value)) {
this.sqlBuilder.append(condition).append(" '").append(value).append("' ");
}
return this;
}
/**
* 动态追加查询条件
*
* @param condition 条件
* @param value 条件值
* @author 不可食用盐
*/
public DynamicQueryUtil dynamicAppendCondition(String condition, Number value) {
if (ObjectUtil.isNotNull(value)) {
this.sqlBuilder.append(condition).append(value);
}
return this;
}
/**
* 动态追加查询条件
*
* @param condition 条件
* @author 不可食用盐
*/
public DynamicQueryUtil dynamicAppendCondition(String condition) {
if (StrUtil.isNotBlank(condition)) {
this.sqlBuilder.append(condition);
}
return this;
}
/**
* 动态追加查询条件
*
* @param condition
* @param rList
* @author 不可食用盐
*/
public DynamicQueryUtil dynamicAppendStrIn(String condition, List<String> rList) {
if (ObjectUtil.isNotEmpty(rList)) {
String s1 = rList.stream().map(s -> " '" + s + "'").collect(Collectors.joining(","));
this.sqlBuilder.append(condition).append(" ( ").append(s1).append(" ) ");
}
return this;
}
/**
* 动态追加查询条件
*
* @param condition
* @param rList
* @author 不可食用盐
*/
public DynamicQueryUtil dynamicAppendNumIn(String condition, List<Number> rList) {
if (ObjectUtil.isNotEmpty(rList)) {
String s1 = rList.stream().map(String::valueOf).collect(Collectors.joining(","));
this.sqlBuilder.append(condition).append(" ( ").append(s1).append(" ) ");
}
return this;
}
/**
* 追加倒叙排序条件
*
* @param field 排序字段
* @author 不可食用盐
*/
public DynamicQueryUtil appendOrderByDesc(String field) {
if (StrUtil.isNotBlank(field)) {
this.sqlBuilder.append(" ORDER BY ").append(field).append(" DESC ");
}
return this;
}
/**
* 追加升序排序条件
*
* @param field 排序字段
* @author 不可食用盐
*/
public DynamicQueryUtil appendOrderByAsc(String field) {
if (StrUtil.isNotBlank(field)) {
this.sqlBuilder.append(" ORDER BY ").append(field).append(" ASC ");
}
return this;
}
定义查询方法
这里基于jdbcTemplate定义了封装了查询单个对象、查询一组对象和分页查询的方法。其中查询单个对象和查询一组对象的方法比较简单，就是简单封了一层。
分页查询的核心有两方面，一是根据分页查询参数Pageable对象计算出limit的初始位置和记录数，另一个是改写sql使用count()统计出执行sql返回的总数据条数。
/**
* 返回单个对象
*
* @param jdbcTemplate
* @param tClass
* @return T
* @author 不可食用盐
*/
public <T> T singleForObject(JdbcTemplate jdbcTemplate, Class<T> tClass) {
printSql();
return jdbcTemplate.queryForObject(this.sqlBuilder.toString(), tClass);
}
/**
* 返回单个对象
*
* @param jdbcTemplate
* @param args
* @param tClass
* @return T
* @author 不可食用盐
*/
public <T> T singleForObject(JdbcTemplate jdbcTemplate, Object[] args, Class<T> tClass) {
printSql();
return jdbcTemplate.queryForObject(this.sqlBuilder.toString(), tClass, args);
}
/**
* 返回单个对象
*
* @param jdbcTemplate
* @param args
* @param argTypes
* @param tClass
* @return T
* @author 不可食用盐
*/
public <T> T singleForObject(JdbcTemplate jdbcTemplate, Object[] args, int[] argTypes, Class<T> tClass) {
printSql();
return jdbcTemplate.queryForObject(this.sqlBuilder.toString(), args, argTypes, tClass);
}
/**
* 返回一个map
*
* @param jdbcTemplate
* @return java.util.Map<java.lang.String, java.lang.Object>
* @author 不可食用盐
*/
public Map<String, Object> singleForMap(JdbcTemplate jdbcTemplate) {
printSql();
return jdbcTemplate.queryForMap(this.sqlBuilder.toString());
}
/**
* 返回一个map
*
* @param jdbcTemplate
* @param args
* @return java.util.Map<java.lang.String, java.lang.Object>
* @author 不可食用盐
*/
public Map<String, Object> singleForMap(JdbcTemplate jdbcTemplate, Object[] args) {
printSql();
return jdbcTemplate.queryForMap(this.sqlBuilder.toString(), args);
}
/**
* 返回一个map
*
* @param jdbcTemplate
* @param args
* @param argTypes
* @return java.util.Map<java.lang.String, java.lang.Object>
* @author 不可食用盐
*/
public Map<String, Object> singleForMap(JdbcTemplate jdbcTemplate, Object[] args, int[] argTypes) {
printSql();
return jdbcTemplate.queryForMap(this.sqlBuilder.toString(), args, argTypes);
}
/**
* 返回集合对象
*
* @param jdbcTemplate
* @param tClass 查询结果对象类型
* @return java.util.List<T>
* @author 不可食用盐
*/
public <T> List<T> list(JdbcTemplate jdbcTemplate, Class<T> tClass) {
printSql();
return jdbcTemplate.query(this.sqlBuilder.toString(), new BeanPropertyRowMapper<>(tClass));
}
/**
* 返回集合对象
*
* @param jdbcTemplate
* @param args 参数数组
* @param tClass 查询结果对象类型
* @return java.util.List<T>
* @author 不可食用盐
*/
public <T> List<T> list(JdbcTemplate jdbcTemplate, Object[] args, Class<T> tClass) {
printSql();
return jdbcTemplate.query(this.sqlBuilder.toString(), new BeanPropertyRowMapper<>(tClass), args);
}
/**
* 返回分页对象
*
* @param jdbcTemplate
* @param args 参数数组
* @param pageable 分页参数
* @param tClass 查询结果对象类型
* @return org.springframework.data.domain.Page<T>
* @author 不可食用盐
*/
public <T> Page<T> page(JdbcTemplate jdbcTemplate, Object[] args, Pageable pageable, Class<T> tClass) {
String sql = this.sqlBuilder.toString();
String countSql = getQueryResultTotalSql(sql);
int totalSize = jdbcTemplate.queryForObject(countSql, Integer.class);
if (totalSize == 0) {
return new PageImpl<>(new ArrayList<>(), pageable, totalSize);
}
String sortStr = "";
Sort sort = pageable.getSort();
if (sort.isSorted()) {
sortStr = sort.stream()
.map(order -> order.getProperty() + " " + order.getDirection())
.collect(Collectors.joining(" "));
}
if (StrUtil.isNotBlank(sortStr)){
int order = sql.toLowerCase().lastIndexOf("order");
if (order >= 0){
sql = sql.substring(order) + "ORDER BY " + sortStr;
}
}
int offset = (pageable.getPageNumber()) * pageable.getPageSize();
int limit = pageable.getPageSize();
sql = sql + " limit " + limit + " offset " + offset;
printSql();
List<T> content = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(tClass), args);
return new PageImpl<>(content, pageable, totalSize);
}
/**
* 返回分页对象
*
* @param jdbcTemplate
* @param pageable 分页参数
* @param tClass 查询结果对象类型
* @return org.springframework.data.domain.Page<T>
* @author 不可食用盐
*/
public <T> Page<T> page(JdbcTemplate jdbcTemplate, Pageable pageable, Class<T> tClass) {
String sql = this.sqlBuilder.toString();
String countSql = getQueryResultTotalSql(sql);
int totalSize = jdbcTemplate.queryForObject(countSql, Integer.class);
if (totalSize == 0) {
return new PageImpl<>(new ArrayList<>(), pageable, totalSize);
}
int offset = (pageable.getPageNumber()) * pageable.getPageSize();
int limit = pageable.getPageSize();
sql = sql + " limit " + limit + " offset " + offset;
printSql();
List<T> content = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(tClass));
return new PageImpl<>(content, pageable, totalSize);
}
这里没有让
DynamicQueryUtil类成为Spring 的 Bean所有采用了JdbcTemplate作为参数传入方法
获取查询总结果数的sql
创建getQueryResultTotalSql方法，这个方法的作用就是根据查询sql创建一个能统计查询结果总数的sql,就是将Select和From之间的查询内容换成Count(1)。
思路：由于Select和From是成对出现的，所以只需要找到和首次出现的Select对应的From的位置，并将其位置之前的字符串替换成SELECT COUNT(1) 即可。
做法：先将sql按空格分隔，遍历分隔后的字符数组，遇见Select关键字就将当前位置入栈，遇见From关键字就出栈。当栈为空时，说明这个位置之前的内容就是要被替换成SELECT COUNT(1) 的内容。
/**
* 获取统计总数的sql
*
* @param sql
* @return java.lang.String
* @author 不可食用盐
*/
private String getSqlResultCount(String sql) {
List<String> strList = Arrays.stream(sql.split(" "))
.map(String::trim)
.filter(StrUtil::isNotBlank)
.collect(Collectors.toList());
// 创建一个栈，用于记录遍历过程中未匹配的selete
Stack<Integer> records = new Stack<>();
records.push(0);
long endIndex = 0;
for (int i = 1; i < strList.size(); i++) {
String str = strList.get(i);
if ("from".equalsIgnoreCase(str)) {
records.pop();
if (records.empty()) {
endIndex = i;
break;
}
}
if ("select".equalsIgnoreCase(str)) {
records.push(i);
continue;
}
}
String result = strList.stream()
.skip(endIndex)
.collect(Collectors.joining(" "));
result = "SELECT COUNT(1) " + result;
return result;
}
这里有个问题就是字符和
From之间要有空格，如果字符和From关键字之间没有空格会导致替换错误
本来是想使用正则来实现的，但是无奈水平有限。在网上找到了一个正则表达式(?<=(SELECT|select|Select)).*?(?=(FROM|from|From))，结果碰见包含子查询的时候出现问题了，无奈只能弃用。
定义打印sql的方法
最后定义个打印sql的方法，以便排除问题时知道执行的sql。
/**
* 打印sql
* 只在dev或test环境下打印
*
* @return void
* @author 不可食用盐
*/
private void printSql() {
try {
String active = SpringUtil.getActiveProfile();
if ("dev".equals(active) || "test".equals(active)) {
log.info("待执行sql: {}", this.sqlBuilder.toString());
}
} catch (Exception exception) {
log.error("获取系统环境失败", exception);
}
}
使用示例
public void query(String name, Integer age){
String sql = "select * from user where del = false";
Page<User> userPage = DynamicQueryUtil.of(sql)
.dynamicAppendCondition("name = ", name)
.dynamicAppendCondition("age >=", age)
.appendOrderByDesc("create_time")
.page(jdbcTemplate, Pageable.ofSize(10), User.class);
}
才疏学浅水平有限，大佬们有更好的方法欢迎留言。
