Spring Boot + Querydsl，如何轻松实现复杂查询的简化操作？
- 内容介绍
- 文章标签
- 相关推荐
本文共计3167个文字，预计阅读时间需要13分钟。
概述 + 本篇博客主要介绍利用Spring Query DSL框架实现服务端查询的解析和实现介绍。查询功能在各种应用程序中都有应用，且是非经常重要的功能。用户直接使用的查询功能往往是查询+条件的形式。
概述本篇博客主要将介绍的是利用spring query dsl框架实现的服务端查询解析和实现介绍。
查询功能是在各种应用程序里面都有应用，且非常重要的功能。用户直接使用的查询功能往往是在我们做好的UI界面上进行查询，UI会将查询请求发给查询实现的服务器，或者专门负责实现查询的一个组件。市场上有专门做查询的框架，其中比较出名，应用也比较广泛的是elasticsearch。
对于服务端来说，前端UI发送过来的查询请求必然是按一定规则组织起来的，这样的规则后端必须能够支持和解析。换一种说法就是服务调用者和服务发布者之间需要遵循同一个规范才可以。百度的UI查询是这样定义的：
在上图中加了蓝色下划线的地方即为我们在百度当中搜索的字符串内容，可以发现，百度的实现是将搜索的内容当做了querydsl.com/
推荐一个 Spring Boot 基础教程及实战示例：
github.com/javastacks/spring-boot-best-practice
Querydsl和spring有什么关系呢？几个Spring Data的模块通过QuerydslPredicateExecutor提供了与Querydsl的集成，如以下示例所示：
public interface QuerydslPredicateExecutor<T> {
//查找并返回与Predicate匹配的单个entity。
Optional<T> findById(Predicate predicate);
//查找并返回与Predicate匹配的所有entity
Iterable<T> findAll(Predicate predicate);
//返回与Predicate匹配的数量。
long count(Predicate predicate);
//返回是否存在与Predicate匹配的entity。
boolean exists(Predicate predicate);
// … more functionality omitted.
}
Predicate就是我们需要传入的一个查询的抽象。
在spring当中使用Querydsl，只需要在spring的repository接口继承QuerydslPredicateExecutor，如以下示例所示:
interface UserRepository extends CrudRepository<User, Long>, QuerydslPredicateExecutor<User> {
}
在定义了上面的这个接口之后，我们就可以使用Querydsl Predicate编写type-safe的查询，如以下示例所示：
Predicate predicate = user.firstname.equals("dave")
.and(user.lastname.startsWith("mathews"));
userRepository.findAll(predicate);
上面的代码构建出的predicate体现在sql语句里的话就是这样的: where firstname = 'dave' and lastname ='mathews%'。这就是所谓的类sql的查询，用起来非常的直观。
因此，我们可以将我们接收到的查询请求，转化为对应的predicte，且从技术上讲，只要predict支持的查询拼接我们都能支持，难点只在于如何解析查询请求，以及如何将他们转换为对应的predicate.
利用Spring Query DSL实现动态查询下面是使用spring和Querydsl实现动态查询的一个例子.
现在假设我们有Model类如下:
public class Student {
private String id;
private String gender;
private String firstName;
private String lastName;
private Date createdAt;
private Boolean isGraduated;
}
我们希望可以实现该类所有字段直接自由组合进行查询，且可以按照与和或的逻辑进行查询。且我们约定用冒号表示等于，例如:
firstname:li AND lastname:hua
firstname:li OR lastname:hua
firstname:li AND lastname:hua AND gender:male
上面的查询都比较清晰，解析不会有太大难度，下面我们来看这样一个查询：
firstname:li OR lastname:hua AND gender:male
这个查询的问题在于作为逻辑与的gender查询，到底是只和前面一个条件进行与操作，还是与前面两个条件一起进行一个与操作，显然与的条件往往是作为filter的功能出现的。
因此我们应当将其看作整个其他条件的与操作，因此我们需要先将前面的条在组合在一起，例如，我们可以使用括号表示这个逻辑，那么查询就会变成:
(firstname:li AND lastname:hua) AND gender:male
这下逻辑就变得清晰了，难题就在于怎么解析了
public class QueryAnalysis{
private static final String EMPTY_STRING = "";
private static final String BLANK_STRING = " ";
private static final String COLON = ":";
private static final String BP_CATEGORY_CODE = "categoryCode";
private static final String OPEN_PARENTTHESIS = "(";
private static final String CLOSE_PARENTTHESIS = ")";
private static final String QUERY_REGEX = "([\\w.]+?)(:|<|>|!:)([^ ]*)";
//it has to lie between two blanks
private static final String QUERY_LOGIC_AND = " AND ";
private void generateQueryBuilderWithQueryString(PredicateBuilder builder, String q,
List<String> queryStringList) {
StringBuilder stringBuilder = new StringBuilder();
String queryTerm = q;
if (q == null) {
return;
}
if (!q.contains(" AND ") && !q.startsWith("(") && !q.endsWith(")")) {
queryTerm = stringBuilder.append("(").append(q).append(")").toString();
}
Map<String, Matcher> matcherMap = getMatcherWithQueryStr(queryTerm);
Matcher matcherOr = matcherMap.get("matcherOr");
Matcher matcherAnd = matcherMap.get("matcherAnd");
while (matcherOr.find()) {
builder.withOr(matcherOr.group(1), matcherOr.group(2), matcherOr.group(3));
}
while (matcherAnd.find()) {
builder.withAnd(matcherAnd.group(1), matcherAnd.group(2), matcherAnd.group(3));
isSearchParameterValid = true;
}
}
private static Map<String, Matcher> getMatcherWithQueryStr(String q) {
StringBuilder stringBuilder = new StringBuilder();
Pattern pattern = Pattern.compile(QUERY_REGEX);
// inside the subString is "or",outside them are "and"
String[] queryStringArraySplitByAnd = q.split(QUERY_LOGIC_AND);
String queryStringOr = EMPTY_STRING;
String queryStringAnd = EMPTY_STRING;
for (String string : queryStringArraySplitByAnd) {
if (string.trim().startsWith(OPEN_PARENTTHESIS) && string.trim().endsWith(CLOSE_PARENTTHESIS)) {
//only support one OR sentence
queryStringOr = string.trim().substring(1,string.length()-1);
} else {
queryStringAnd = stringBuilder.append(string).append(BLANK_STRING).toString();
}
}
String queryStringAndTrim = queryStringAnd.trim();
if(queryStringAndTrim.startsWith(OPEN_PARENTTHESIS) && queryStringAndTrim.endsWith(CLOSE_PARENTTHESIS)){
queryStringAnd = queryStringAndTrim.substring(1,queryStringAndTrim.length()-1);
}
Matcher matcherOr = pattern.matcher(queryStringOr);
Matcher matcherAnd = pattern.matcher(queryStringAnd);
Map<String, Matcher> matcherMap = new ConcurrentHashMap<>();
matcherMap.put("matcherOr", matcherOr);
matcherMap.put("matcherAnd", matcherAnd);
return matcherMap;
}
}
Predicate的逻辑如下：
import java.util.ArrayList;
import java.util.List;
import com.querydsl.core.types.dsl.BooleanExpression;
/**
* This class is mainly used to classify all the query parameters
*/
public class PredicateBuilder {
private static final String BLANK_STRING = " ";
private static final String TILDE_STRING = "~~";
private List<SearchCriteria> paramsOr;
private List<SearchCriteria> paramsAnd;
private BusinessPartnerMessageProvider messageProvider;
public PredicateBuilder(BusinessPartnerMessageProvider messageProvider){
paramsOr = new ArrayList<>();
paramsAnd = new ArrayList<>();
}
public PredicateBuilder withOr(
String key, String operation, Object value) {
String keyAfterConverted = keyConverter(key);
Object valueAfterConverted = value.toString().replaceAll(TILDE_STRING,BLANK_STRING).trim();
paramsOr.add(new SearchCriteria(keyAfterConverted, operation, valueAfterConverted));
return this;
}
public PredicateBuilder withAnd(
String key, String operation, Object value) {
String keyAfterConverted = keyConverter(key);
Object valueAfterConverted = value.toString().replaceAll(TILDE_STRING,BLANK_STRING).trim();
paramsAnd.add(new SearchCriteria(keyAfterConverted, operation, valueAfterConverted));
return this;
}
protected String keyConverter(String key){
return key;
}
public BooleanExpression buildOr(Class classType) {
return handleBPBooleanExpressionOr(classType);
}
public BooleanExpression buildAnd(Class classType) {
return handleBPBooleanExpressionAnd(classType);
}
private BooleanExpression handleBPBooleanExpressionOr(Class classType) {
if (paramsOr.isEmpty()) {
return null;
}
return buildBooleanExpressionOr(paramsOr, classType);
}
private BooleanExpression handleBPBooleanExpressionAnd(Class classType) {
if (paramsAnd.isEmpty()) {
return null;
}
return buildBooleanExpressionAnd(paramsAnd, classType);
}
private BooleanExpression buildBooleanExpressionOr(List<SearchCriteria> paramsOr, Class classType){
List<BooleanExpression> predicates = new ArrayList<>();
BooleanExpressionBuilder predicate;
for (SearchCriteria param : paramsOr) {
predicate = new BooleanExpressionBuilder(param, messageProvider);
BooleanExpression exp = predicate.buildPredicate(classType);
if (exp != null) {
predicates.add(exp);
}
}
BooleanExpression result = null;
if(!predicates.isEmpty()) {
result = predicates.get(0);
for (int i = 1; i < predicates.size(); i++) {
result = result.or(predicates.get(i));
}
}
return result;
}
private BooleanExpression buildBooleanExpressionAnd(List<SearchCriteria> paramsAnd, Class classType){
List<BooleanExpression> predicates = new ArrayList<>();
BooleanExpressionBuilder predicate;
for (SearchCriteria param : paramsAnd) {
predicate = new BooleanExpressionBuilder(param, messageProvider);
BooleanExpression exp = predicate.buildPredicate(classType);
if (exp != null) {
predicates.add(exp);
}
}
BooleanExpression result = null;
if(!predicates.isEmpty()) {
result = predicates.get(0);
for (int i = 1; i < predicates.size(); i++) {
result = result.and(predicates.get(i));
}
}
return result;
}
}
BooleanExpressionBuilder的逻辑如下：
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.ZoneOffset;
import java.util.Date;
import java.util.TimeZone;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.BooleanPath;
import com.querydsl.core.types.dsl.DateTimePath;
import com.querydsl.core.types.dsl.NumberPath;
import com.querydsl.core.types.dsl.PathBuilder;
import com.querydsl.core.types.dsl.StringPath;
public class BooleanExpressionBuilder {
private SearchCriteria criteria;
private BusinessPartnerMessageProvider messageProvider;
private static final String NO_SUCH_FILED_MESSAGE = "NO_SUCH_FIELD_FOR_QUERY_PARAMETER";
public BooleanExpressionBuilder(final SearchCriteria criteria ) {
this.criteria = new SearchCriteria(criteria.getKey(),criteria.getOperation(),criteria.getValue());
}
public BooleanExpression buildPredicate(Class classType) {
// the second param for PathBuilder constructor is the binding path.
PathBuilder<Class> entityPath = new PathBuilder<>(classType, classType.getSimpleName());
Boolean isValueMatchEndWith = criteria.getValue().toString().endsWith("*");
Boolean isValueMatchStartWith = criteria.getValue().toString().startsWith("*");
Boolean isOperationColon = ":".equalsIgnoreCase(criteria.getOperation());
int searchValueLength = criteria.getValue().toString().length();
StringPath stringPath = entityPath.getString(criteria.getKey());
DateTimePath<Date> timePath = entityPath.getDateTime(criteria.getKey(), Date.class);
NumberPath<Integer> numberPath = entityPath.getNumber(criteria.getKey(), Integer.class);
if ((isOperationColon) && (!isValueMatchStartWith) && (!isValueMatchEndWith)) {
return getEqualBooleanExpression(classType, entityPath, stringPath, timePath, numberPath);
}
if (">".equalsIgnoreCase(criteria.getOperation())) {
return getGreaterThanBooleanExpression(classType, timePath, numberPath);
}
if ("<".equalsIgnoreCase(criteria.getOperation())) {
return getLessThanBooleanExpression(classType, timePath, numberPath);
}
// !：means !=
if ("!:".equalsIgnoreCase(criteria.getOperation())) {
return getNotEqualBooleanExpression(classType, entityPath,
stringPath, timePath, numberPath);
}
//start with xxx
if ((isOperationColon) && isValueMatchEndWith && (!isValueMatchStartWith)) {
if (isSearchKeyValidForClass(classType))
return stringPath
.startsWithIgnoreCase(criteria.getValue().toString().substring(0, searchValueLength - 1).trim());
}
if ((isOperationColon) && (!isValueMatchEndWith) && (isValueMatchStartWith)) {
if (isSearchKeyValidForClass(classType))
return stringPath.endsWithIgnoreCase(criteria.getValue().toString().substring(1, searchValueLength).trim());
}
//contain xxx
if ((isOperationColon) && isValueMatchEndWith && isValueMatchStartWith) {
return getContainsBooleanExpression(classType, searchValueLength, stringPath);
}
return null;
}
private BooleanExpression getContainsBooleanExpression(Class classType,
int searchValueLength, StringPath stringPath) {
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(String.class) && searchValueLength>1) {
return stringPath.containsIgnoreCase(criteria.getValue().toString().substring(1,searchValueLength-1).trim());
}
//if there are only a "*" in the seatch value, then
if(fieldType.equals(String.class) && searchValueLength==1){
return stringPath.eq(criteria.getValue().toString());
}
} catch (NoSuchFieldException | SecurityException e) {
}
return null;
}
private boolean isSearchKeyValidForClass(Class classType) {
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(String.class)) {
return true;
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException(messageProvider.getMessage(NO_SUCH_FILED_MESSAGE,
new Object[] { criteria.getKey() }), e);
}
return false;
}
private BooleanExpression getNotEqualBooleanExpression(Class classType, PathBuilder<Class> entityPath,
StringPath stringPath, DateTimePath<Date> timePath, NumberPath<Integer> numberPath) {
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(Date.class)) {
dateTimeValueConverter();
return timePath.ne((Date) criteria.getValue());
}
if (fieldType.equals(Integer.class)) {
int value = Integer.parseInt(criteria.getValue().toString());
return numberPath.ne(value);
}
if (fieldType.equals(String.class)) {
return stringPath.ne(criteria.getValue().toString());
}
if (fieldType.equals(boolean.class)) {
booleanConverter();
BooleanPath booleanPath = entityPath.getBoolean(criteria.getKey());
return booleanPath.ne((Boolean) criteria.getValue());
}
if (fieldType.equals(Boolean.class)) {
booleanConverter();
BooleanPath booleanPath = entityPath.getBoolean(criteria.getKey());
return booleanPath.ne((Boolean) criteria.getValue());
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException();
}
return null;
}
private BooleanExpression getLessThanBooleanExpression(Class classType,
DateTimePath<Date> timePath, NumberPath<Integer> numberPath) {
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(Date.class)) {
dateTimeValueConverter();
return timePath.lt((Date) criteria.getValue());
}
if (fieldType.equals(Integer.class)) {
integerValueConverter();
return numberPath.lt((Integer) criteria.getValue());
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException(e.getCause());
}
return null;
}
private BooleanExpression getGreaterThanBooleanExpression(Class classType,
DateTimePath<Date> timePath, NumberPath<Integer> numberPath) {
// other data types do not make sense when use >
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(Date.class)) {
dateTimeValueConverter();
return timePath.gt((Date) criteria.getValue());
}
if (fieldType.equals(Integer.class)) {
integerValueConverter();
return numberPath.gt((Integer) criteria.getValue());
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException(e.getCause());
}
return null;
}
private BooleanExpression getEqualBooleanExpression(Class classType, PathBuilder<Class> entityPath, StringPath stringPath,
DateTimePath<Date> timePath, NumberPath<Integer> numberPath) {
// ：means =
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(Integer.class)) {
integerValueConverter();
return numberPath.eq((Integer) criteria.getValue());
}
if (fieldType.equals(Date.class)) {
dateTimeValueConverter();
return timePath.eq((Date) criteria.getValue());
}
if (fieldType.equals(boolean.class)) {
booleanConverter();
BooleanPath booleanPath = entityPath.getBoolean(criteria.getKey());
return booleanPath.eq((Boolean) criteria.getValue());
}
if (fieldType.equals(Boolean.class)) {
booleanConverter();
BooleanPath booleanPath = entityPath.getBoolean(criteria.getKey());
return booleanPath.eq((Boolean) criteria.getValue());
}
if (fieldType.equals(String.class)) {
return stringPath.equalsIgnoreCase(criteria.getValue().toString());
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException(e.getCause());
}
return null;
}
// convert string to datetime
private void dateTimeValueConverter() {
criteria.setValue(convertToTimeStamp(criteria.getValue().toString()));
}
private void booleanConverter() {
if (criteria.getValue().toString().equalsIgnoreCase("true")) {
criteria.setValue(true);
} else if (criteria.getValue().toString().equalsIgnoreCase("false")) {
criteria.setValue(false);
} else {
throw new BadRequestValidationException("Invalid Boolean");
}
}
// convert string to Integer
private void integerValueConverter() {
criteria.setValue(Integer.parseInt(criteria.getValue().toString()));
}
private Date convertToTimeStamp(String time) {
//convert date here
return parsedDate;
}
}
查询条件的抽象类SearchCriteria定义如下:
public class SearchCriteria {
private String key;
private String operation;
private Object value;
}
大致的实现逻辑如下图所示：
比较关键的点有下面这些：
- 对字符串的解析需要借助正则表达式的帮助，正则表达式决定了我们支持怎样的查询.
- 由于字符串可以任意输入，存在无限种可能，对查询字符串的校验很关键也很复杂。
- 不同逻辑的查询条件需要存放在不同的容器里面，因为他们的拼接逻辑不一样，一个是或一个是与
- 不同的字段类型需要调用不同的生成Predicate的方法，例如String，Boolean和Date这些类型他们都有自己对应的查询实现
- 生成子表的Predicate很复杂，与主表的查询条件一起查询时逻辑更加复杂，上面的逻辑拿掉了这一部分。但是这个功能是可以实现的。
距离说明，现在有数据定义如下：
{
"customerNumber": "5135116903",
"customerType": "INDIVIDUAL",
"createdBy": "Android.chen@sap.com",
"changedBy": "Android.chen@sap.com",
"createdAt": "2018-06-26T10:15:17.212Z",
"changedAt": "2018-06-26T10:15:17.212Z",
"markets": [{
"marketId": "A1",
"currency": "USD",
"country": "US",
"active": true
}, {
"marketId": "A2",
"currency": "USD",
"country": "US",
"active": false
}, {
"marketId": "A3",
"currency": "USD",
"country": "US",
"active": true
}]
}
其中父节点表是customer，子节点markets信息存储在market表当中。
现在，假设我们有这样的查询：
customerNumber: 5135116903 AND markets.active:false
没有疑问，上面的数据应该被查出来。现在查询条件变成下面这样：
customerNumber: 5135116903 AND markets.active:false AND markets.marketId:A1
现在问题来了，语句的意思是此客户的marker既要是非active 的且ID要是A1，但是此客户又有多个market，从整个数组里来看，这个条件是满足的。但是从单个的market个体来看这个条件是不满足的。而我们作为用户的话希望得到的效果必然是无法查处此customer信息。
这会给实现带来问题，因为由于market是一个数组，在数据表中对应的就是几条记录，我们在解析并构建子表查询时，必须确保对于子表的查询条件是作用于单独的一个node，也就是单独的一条记录，而不是从整个数组当中去查，否则就会有问题。
来源：blog.csdn.net/topdeveloperr/article/details/89550197
近期热文推荐：
1.1,000+ 道 Java面试题及答案整理(2022最新版)
2.劲爆！Java 协程要来了。。。
3.Spring Boot 2.x 教程，太全了！
4.别再写满屏的爆爆爆炸类了，试试装饰器模式，这才是优雅的方式！！
5.《Java开发手册（嵩山版）》最新发布，速速下载！
觉得不错，别忘了随手点赞+转发哦！
本文共计3167个文字，预计阅读时间需要13分钟。
概述 + 本篇博客主要介绍利用Spring Query DSL框架实现服务端查询的解析和实现介绍。查询功能在各种应用程序中都有应用，且是非经常重要的功能。用户直接使用的查询功能往往是查询+条件的形式。
概述本篇博客主要将介绍的是利用spring query dsl框架实现的服务端查询解析和实现介绍。
查询功能是在各种应用程序里面都有应用，且非常重要的功能。用户直接使用的查询功能往往是在我们做好的UI界面上进行查询，UI会将查询请求发给查询实现的服务器，或者专门负责实现查询的一个组件。市场上有专门做查询的框架，其中比较出名，应用也比较广泛的是elasticsearch。
对于服务端来说，前端UI发送过来的查询请求必然是按一定规则组织起来的，这样的规则后端必须能够支持和解析。换一种说法就是服务调用者和服务发布者之间需要遵循同一个规范才可以。百度的UI查询是这样定义的：
在上图中加了蓝色下划线的地方即为我们在百度当中搜索的字符串内容，可以发现，百度的实现是将搜索的内容当做了querydsl.com/
推荐一个 Spring Boot 基础教程及实战示例：
github.com/javastacks/spring-boot-best-practice
Querydsl和spring有什么关系呢？几个Spring Data的模块通过QuerydslPredicateExecutor提供了与Querydsl的集成，如以下示例所示：
public interface QuerydslPredicateExecutor<T> {
//查找并返回与Predicate匹配的单个entity。
Optional<T> findById(Predicate predicate);
//查找并返回与Predicate匹配的所有entity
Iterable<T> findAll(Predicate predicate);
//返回与Predicate匹配的数量。
long count(Predicate predicate);
//返回是否存在与Predicate匹配的entity。
boolean exists(Predicate predicate);
// … more functionality omitted.
}
Predicate就是我们需要传入的一个查询的抽象。
在spring当中使用Querydsl，只需要在spring的repository接口继承QuerydslPredicateExecutor，如以下示例所示:
interface UserRepository extends CrudRepository<User, Long>, QuerydslPredicateExecutor<User> {
}
在定义了上面的这个接口之后，我们就可以使用Querydsl Predicate编写type-safe的查询，如以下示例所示：
Predicate predicate = user.firstname.equals("dave")
.and(user.lastname.startsWith("mathews"));
userRepository.findAll(predicate);
上面的代码构建出的predicate体现在sql语句里的话就是这样的: where firstname = 'dave' and lastname ='mathews%'。这就是所谓的类sql的查询，用起来非常的直观。
因此，我们可以将我们接收到的查询请求，转化为对应的predicte，且从技术上讲，只要predict支持的查询拼接我们都能支持，难点只在于如何解析查询请求，以及如何将他们转换为对应的predicate.
利用Spring Query DSL实现动态查询下面是使用spring和Querydsl实现动态查询的一个例子.
现在假设我们有Model类如下:
public class Student {
private String id;
private String gender;
private String firstName;
private String lastName;
private Date createdAt;
private Boolean isGraduated;
}
我们希望可以实现该类所有字段直接自由组合进行查询，且可以按照与和或的逻辑进行查询。且我们约定用冒号表示等于，例如:
firstname:li AND lastname:hua
firstname:li OR lastname:hua
firstname:li AND lastname:hua AND gender:male
上面的查询都比较清晰，解析不会有太大难度，下面我们来看这样一个查询：
firstname:li OR lastname:hua AND gender:male
这个查询的问题在于作为逻辑与的gender查询，到底是只和前面一个条件进行与操作，还是与前面两个条件一起进行一个与操作，显然与的条件往往是作为filter的功能出现的。
因此我们应当将其看作整个其他条件的与操作，因此我们需要先将前面的条在组合在一起，例如，我们可以使用括号表示这个逻辑，那么查询就会变成:
(firstname:li AND lastname:hua) AND gender:male
这下逻辑就变得清晰了，难题就在于怎么解析了
public class QueryAnalysis{
private static final String EMPTY_STRING = "";
private static final String BLANK_STRING = " ";
private static final String COLON = ":";
private static final String BP_CATEGORY_CODE = "categoryCode";
private static final String OPEN_PARENTTHESIS = "(";
private static final String CLOSE_PARENTTHESIS = ")";
private static final String QUERY_REGEX = "([\\w.]+?)(:|<|>|!:)([^ ]*)";
//it has to lie between two blanks
private static final String QUERY_LOGIC_AND = " AND ";
private void generateQueryBuilderWithQueryString(PredicateBuilder builder, String q,
List<String> queryStringList) {
StringBuilder stringBuilder = new StringBuilder();
String queryTerm = q;
if (q == null) {
return;
}
if (!q.contains(" AND ") && !q.startsWith("(") && !q.endsWith(")")) {
queryTerm = stringBuilder.append("(").append(q).append(")").toString();
}
Map<String, Matcher> matcherMap = getMatcherWithQueryStr(queryTerm);
Matcher matcherOr = matcherMap.get("matcherOr");
Matcher matcherAnd = matcherMap.get("matcherAnd");
while (matcherOr.find()) {
builder.withOr(matcherOr.group(1), matcherOr.group(2), matcherOr.group(3));
}
while (matcherAnd.find()) {
builder.withAnd(matcherAnd.group(1), matcherAnd.group(2), matcherAnd.group(3));
isSearchParameterValid = true;
}
}
private static Map<String, Matcher> getMatcherWithQueryStr(String q) {
StringBuilder stringBuilder = new StringBuilder();
Pattern pattern = Pattern.compile(QUERY_REGEX);
// inside the subString is "or",outside them are "and"
String[] queryStringArraySplitByAnd = q.split(QUERY_LOGIC_AND);
String queryStringOr = EMPTY_STRING;
String queryStringAnd = EMPTY_STRING;
for (String string : queryStringArraySplitByAnd) {
if (string.trim().startsWith(OPEN_PARENTTHESIS) && string.trim().endsWith(CLOSE_PARENTTHESIS)) {
//only support one OR sentence
queryStringOr = string.trim().substring(1,string.length()-1);
} else {
queryStringAnd = stringBuilder.append(string).append(BLANK_STRING).toString();
}
}
String queryStringAndTrim = queryStringAnd.trim();
if(queryStringAndTrim.startsWith(OPEN_PARENTTHESIS) && queryStringAndTrim.endsWith(CLOSE_PARENTTHESIS)){
queryStringAnd = queryStringAndTrim.substring(1,queryStringAndTrim.length()-1);
}
Matcher matcherOr = pattern.matcher(queryStringOr);
Matcher matcherAnd = pattern.matcher(queryStringAnd);
Map<String, Matcher> matcherMap = new ConcurrentHashMap<>();
matcherMap.put("matcherOr", matcherOr);
matcherMap.put("matcherAnd", matcherAnd);
return matcherMap;
}
}
Predicate的逻辑如下：
import java.util.ArrayList;
import java.util.List;
import com.querydsl.core.types.dsl.BooleanExpression;
/**
* This class is mainly used to classify all the query parameters
*/
public class PredicateBuilder {
private static final String BLANK_STRING = " ";
private static final String TILDE_STRING = "~~";
private List<SearchCriteria> paramsOr;
private List<SearchCriteria> paramsAnd;
private BusinessPartnerMessageProvider messageProvider;
public PredicateBuilder(BusinessPartnerMessageProvider messageProvider){
paramsOr = new ArrayList<>();
paramsAnd = new ArrayList<>();
}
public PredicateBuilder withOr(
String key, String operation, Object value) {
String keyAfterConverted = keyConverter(key);
Object valueAfterConverted = value.toString().replaceAll(TILDE_STRING,BLANK_STRING).trim();
paramsOr.add(new SearchCriteria(keyAfterConverted, operation, valueAfterConverted));
return this;
}
public PredicateBuilder withAnd(
String key, String operation, Object value) {
String keyAfterConverted = keyConverter(key);
Object valueAfterConverted = value.toString().replaceAll(TILDE_STRING,BLANK_STRING).trim();
paramsAnd.add(new SearchCriteria(keyAfterConverted, operation, valueAfterConverted));
return this;
}
protected String keyConverter(String key){
return key;
}
public BooleanExpression buildOr(Class classType) {
return handleBPBooleanExpressionOr(classType);
}
public BooleanExpression buildAnd(Class classType) {
return handleBPBooleanExpressionAnd(classType);
}
private BooleanExpression handleBPBooleanExpressionOr(Class classType) {
if (paramsOr.isEmpty()) {
return null;
}
return buildBooleanExpressionOr(paramsOr, classType);
}
private BooleanExpression handleBPBooleanExpressionAnd(Class classType) {
if (paramsAnd.isEmpty()) {
return null;
}
return buildBooleanExpressionAnd(paramsAnd, classType);
}
private BooleanExpression buildBooleanExpressionOr(List<SearchCriteria> paramsOr, Class classType){
List<BooleanExpression> predicates = new ArrayList<>();
BooleanExpressionBuilder predicate;
for (SearchCriteria param : paramsOr) {
predicate = new BooleanExpressionBuilder(param, messageProvider);
BooleanExpression exp = predicate.buildPredicate(classType);
if (exp != null) {
predicates.add(exp);
}
}
BooleanExpression result = null;
if(!predicates.isEmpty()) {
result = predicates.get(0);
for (int i = 1; i < predicates.size(); i++) {
result = result.or(predicates.get(i));
}
}
return result;
}
private BooleanExpression buildBooleanExpressionAnd(List<SearchCriteria> paramsAnd, Class classType){
List<BooleanExpression> predicates = new ArrayList<>();
BooleanExpressionBuilder predicate;
for (SearchCriteria param : paramsAnd) {
predicate = new BooleanExpressionBuilder(param, messageProvider);
BooleanExpression exp = predicate.buildPredicate(classType);
if (exp != null) {
predicates.add(exp);
}
}
BooleanExpression result = null;
if(!predicates.isEmpty()) {
result = predicates.get(0);
for (int i = 1; i < predicates.size(); i++) {
result = result.and(predicates.get(i));
}
}
return result;
}
}
BooleanExpressionBuilder的逻辑如下：
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.ZoneOffset;
import java.util.Date;
import java.util.TimeZone;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.BooleanPath;
import com.querydsl.core.types.dsl.DateTimePath;
import com.querydsl.core.types.dsl.NumberPath;
import com.querydsl.core.types.dsl.PathBuilder;
import com.querydsl.core.types.dsl.StringPath;
public class BooleanExpressionBuilder {
private SearchCriteria criteria;
private BusinessPartnerMessageProvider messageProvider;
private static final String NO_SUCH_FILED_MESSAGE = "NO_SUCH_FIELD_FOR_QUERY_PARAMETER";
public BooleanExpressionBuilder(final SearchCriteria criteria ) {
this.criteria = new SearchCriteria(criteria.getKey(),criteria.getOperation(),criteria.getValue());
}
public BooleanExpression buildPredicate(Class classType) {
// the second param for PathBuilder constructor is the binding path.
PathBuilder<Class> entityPath = new PathBuilder<>(classType, classType.getSimpleName());
Boolean isValueMatchEndWith = criteria.getValue().toString().endsWith("*");
Boolean isValueMatchStartWith = criteria.getValue().toString().startsWith("*");
Boolean isOperationColon = ":".equalsIgnoreCase(criteria.getOperation());
int searchValueLength = criteria.getValue().toString().length();
StringPath stringPath = entityPath.getString(criteria.getKey());
DateTimePath<Date> timePath = entityPath.getDateTime(criteria.getKey(), Date.class);
NumberPath<Integer> numberPath = entityPath.getNumber(criteria.getKey(), Integer.class);
if ((isOperationColon) && (!isValueMatchStartWith) && (!isValueMatchEndWith)) {
return getEqualBooleanExpression(classType, entityPath, stringPath, timePath, numberPath);
}
if (">".equalsIgnoreCase(criteria.getOperation())) {
return getGreaterThanBooleanExpression(classType, timePath, numberPath);
}
if ("<".equalsIgnoreCase(criteria.getOperation())) {
return getLessThanBooleanExpression(classType, timePath, numberPath);
}
// !：means !=
if ("!:".equalsIgnoreCase(criteria.getOperation())) {
return getNotEqualBooleanExpression(classType, entityPath,
stringPath, timePath, numberPath);
}
//start with xxx
if ((isOperationColon) && isValueMatchEndWith && (!isValueMatchStartWith)) {
if (isSearchKeyValidForClass(classType))
return stringPath
.startsWithIgnoreCase(criteria.getValue().toString().substring(0, searchValueLength - 1).trim());
}
if ((isOperationColon) && (!isValueMatchEndWith) && (isValueMatchStartWith)) {
if (isSearchKeyValidForClass(classType))
return stringPath.endsWithIgnoreCase(criteria.getValue().toString().substring(1, searchValueLength).trim());
}
//contain xxx
if ((isOperationColon) && isValueMatchEndWith && isValueMatchStartWith) {
return getContainsBooleanExpression(classType, searchValueLength, stringPath);
}
return null;
}
private BooleanExpression getContainsBooleanExpression(Class classType,
int searchValueLength, StringPath stringPath) {
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(String.class) && searchValueLength>1) {
return stringPath.containsIgnoreCase(criteria.getValue().toString().substring(1,searchValueLength-1).trim());
}
//if there are only a "*" in the seatch value, then
if(fieldType.equals(String.class) && searchValueLength==1){
return stringPath.eq(criteria.getValue().toString());
}
} catch (NoSuchFieldException | SecurityException e) {
}
return null;
}
private boolean isSearchKeyValidForClass(Class classType) {
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(String.class)) {
return true;
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException(messageProvider.getMessage(NO_SUCH_FILED_MESSAGE,
new Object[] { criteria.getKey() }), e);
}
return false;
}
private BooleanExpression getNotEqualBooleanExpression(Class classType, PathBuilder<Class> entityPath,
StringPath stringPath, DateTimePath<Date> timePath, NumberPath<Integer> numberPath) {
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(Date.class)) {
dateTimeValueConverter();
return timePath.ne((Date) criteria.getValue());
}
if (fieldType.equals(Integer.class)) {
int value = Integer.parseInt(criteria.getValue().toString());
return numberPath.ne(value);
}
if (fieldType.equals(String.class)) {
return stringPath.ne(criteria.getValue().toString());
}
if (fieldType.equals(boolean.class)) {
booleanConverter();
BooleanPath booleanPath = entityPath.getBoolean(criteria.getKey());
return booleanPath.ne((Boolean) criteria.getValue());
}
if (fieldType.equals(Boolean.class)) {
booleanConverter();
BooleanPath booleanPath = entityPath.getBoolean(criteria.getKey());
return booleanPath.ne((Boolean) criteria.getValue());
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException();
}
return null;
}
private BooleanExpression getLessThanBooleanExpression(Class classType,
DateTimePath<Date> timePath, NumberPath<Integer> numberPath) {
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(Date.class)) {
dateTimeValueConverter();
return timePath.lt((Date) criteria.getValue());
}
if (fieldType.equals(Integer.class)) {
integerValueConverter();
return numberPath.lt((Integer) criteria.getValue());
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException(e.getCause());
}
return null;
}
private BooleanExpression getGreaterThanBooleanExpression(Class classType,
DateTimePath<Date> timePath, NumberPath<Integer> numberPath) {
// other data types do not make sense when use >
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(Date.class)) {
dateTimeValueConverter();
return timePath.gt((Date) criteria.getValue());
}
if (fieldType.equals(Integer.class)) {
integerValueConverter();
return numberPath.gt((Integer) criteria.getValue());
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException(e.getCause());
}
return null;
}
private BooleanExpression getEqualBooleanExpression(Class classType, PathBuilder<Class> entityPath, StringPath stringPath,
DateTimePath<Date> timePath, NumberPath<Integer> numberPath) {
// ：means =
try {
Class fieldType = classType.getDeclaredField(criteria.getKey()).getType();
if (fieldType.equals(Integer.class)) {
integerValueConverter();
return numberPath.eq((Integer) criteria.getValue());
}
if (fieldType.equals(Date.class)) {
dateTimeValueConverter();
return timePath.eq((Date) criteria.getValue());
}
if (fieldType.equals(boolean.class)) {
booleanConverter();
BooleanPath booleanPath = entityPath.getBoolean(criteria.getKey());
return booleanPath.eq((Boolean) criteria.getValue());
}
if (fieldType.equals(Boolean.class)) {
booleanConverter();
BooleanPath booleanPath = entityPath.getBoolean(criteria.getKey());
return booleanPath.eq((Boolean) criteria.getValue());
}
if (fieldType.equals(String.class)) {
return stringPath.equalsIgnoreCase(criteria.getValue().toString());
}
} catch (NoSuchFieldException | SecurityException e) {
throw new BadRequestValidationException(e.getCause());
}
return null;
}
// convert string to datetime
private void dateTimeValueConverter() {
criteria.setValue(convertToTimeStamp(criteria.getValue().toString()));
}
private void booleanConverter() {
if (criteria.getValue().toString().equalsIgnoreCase("true")) {
criteria.setValue(true);
} else if (criteria.getValue().toString().equalsIgnoreCase("false")) {
criteria.setValue(false);
} else {
throw new BadRequestValidationException("Invalid Boolean");
}
}
// convert string to Integer
private void integerValueConverter() {
criteria.setValue(Integer.parseInt(criteria.getValue().toString()));
}
private Date convertToTimeStamp(String time) {
//convert date here
return parsedDate;
}
}
查询条件的抽象类SearchCriteria定义如下:
public class SearchCriteria {
private String key;
private String operation;
private Object value;
}
大致的实现逻辑如下图所示：
比较关键的点有下面这些：
- 对字符串的解析需要借助正则表达式的帮助，正则表达式决定了我们支持怎样的查询.
- 由于字符串可以任意输入，存在无限种可能，对查询字符串的校验很关键也很复杂。
- 不同逻辑的查询条件需要存放在不同的容器里面，因为他们的拼接逻辑不一样，一个是或一个是与
- 不同的字段类型需要调用不同的生成Predicate的方法，例如String，Boolean和Date这些类型他们都有自己对应的查询实现
- 生成子表的Predicate很复杂，与主表的查询条件一起查询时逻辑更加复杂，上面的逻辑拿掉了这一部分。但是这个功能是可以实现的。
距离说明，现在有数据定义如下：
{
"customerNumber": "5135116903",
"customerType": "INDIVIDUAL",
"createdBy": "Android.chen@sap.com",
"changedBy": "Android.chen@sap.com",
"createdAt": "2018-06-26T10:15:17.212Z",
"changedAt": "2018-06-26T10:15:17.212Z",
"markets": [{
"marketId": "A1",
"currency": "USD",
"country": "US",
"active": true
}, {
"marketId": "A2",
"currency": "USD",
"country": "US",
"active": false
}, {
"marketId": "A3",
"currency": "USD",
"country": "US",
"active": true
}]
}
其中父节点表是customer，子节点markets信息存储在market表当中。
现在，假设我们有这样的查询：
customerNumber: 5135116903 AND markets.active:false
没有疑问，上面的数据应该被查出来。现在查询条件变成下面这样：
customerNumber: 5135116903 AND markets.active:false AND markets.marketId:A1
现在问题来了，语句的意思是此客户的marker既要是非active 的且ID要是A1，但是此客户又有多个market，从整个数组里来看，这个条件是满足的。但是从单个的market个体来看这个条件是不满足的。而我们作为用户的话希望得到的效果必然是无法查处此customer信息。
这会给实现带来问题，因为由于market是一个数组，在数据表中对应的就是几条记录，我们在解析并构建子表查询时，必须确保对于子表的查询条件是作用于单独的一个node，也就是单独的一条记录，而不是从整个数组当中去查，否则就会有问题。
来源：blog.csdn.net/topdeveloperr/article/details/89550197
近期热文推荐：
1.1,000+ 道 Java面试题及答案整理(2022最新版)
2.劲爆！Java 协程要来了。。。
3.Spring Boot 2.x 教程，太全了！
4.别再写满屏的爆爆爆炸类了，试试装饰器模式，这才是优雅的方式！！
5.《Java开发手册（嵩山版）》最新发布，速速下载！
觉得不错，别忘了随手点赞+转发哦！