SpringBoot+Querydsl 框架,大大简化复杂查询操作
阅读本文大概需要 13 分钟。
来自:https://blog.csdn.net/topdeveloperr
概述
定义查询请求
google-like查询
特定字段的类sql查询
name:bill
name:bill AND city:LA
name:bill OR city:LA
使用Spring Data Querydsl
其官方网站在这里:http://querydsl.com/
public interface QuerydslPredicateExecutor<T> {
//查找并返回与Predicate匹配的单个entity。
OptionalfindById(Predicate predicate) ;
//查找并返回与Predicate匹配的所有entity
IterablefindAll(Predicate predicate) ;
//返回与Predicate匹配的数量。
long count(Predicate predicate);
//返回是否存在与Predicate匹配的entity。
boolean exists(Predicate predicate);
// … more functionality omitted.
}
QuerydslPredicateExecutor
,如以下示例所示:interface UserRepository extends CrudRepository , QuerydslPredicateExecutor {
}
Querydsl Predicate
编写type-safe的查询,如以下示例所示:Predicate predicate = user.firstname.equals("dave")
.and(user.lastname.startsWith("mathews"));
userRepository.findAll(predicate);
where firstname = 'dave' and lastname ='mathews%'
。这就是所谓的类sql的查询,用起来非常的直观。利用Spring Query DSL实现动态查询
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
(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,
ListqueryStringList) {
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();
}
MapmatcherMap = 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 MapgetMatcherWithQueryStr(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);
MapmatcherMap = new ConcurrentHashMap<>();
matcherMap.put("matcherOr", matcherOr);
matcherMap.put("matcherAnd", matcherAnd);
return matcherMap;
}
}
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 ListparamsOr;
private ListparamsAnd;
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(ListparamsOr, Class classType) {
Listpredicates = 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(ListparamsAnd, Class classType) {
Listpredicates = 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;
}
}
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.
PathBuilderentityPath = 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());
DateTimePathtimePath = entityPath.getDateTime(criteria.getKey(), Date.class);
NumberPathnumberPath = 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, PathBuilderentityPath, {
StringPath stringPath, DateTimePathtimePath, NumberPath 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,
DateTimePathtimePath, NumberPath {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,
DateTimePathtimePath, NumberPath {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, PathBuilderentityPath, StringPath stringPath, {
DateTimePathtimePath, NumberPath 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;
}
}
public class SearchCriteria {
private String key;
private String operation;
private Object value;
}
对字符串的解析需要借助正则表达式的帮助,正则表达式决定了我们支持怎样的查询. 由于字符串可以任意输入,存在无限种可能,对查询字符串的校验很关键也很复杂。 不同逻辑的查询条件需要存放在不同的容器里面,因为他们的拼接逻辑不一样,一个是或一个是与 不同的字段类型需要调用不同的生成Predicate的方法,例如String,Boolean和Date这些类型他们都有自己对应的查询实现 生成子表的Predicate很复杂,与主表的查询条件一起查询时逻辑更加复杂,上面的逻辑拿掉了这一部分。但是这个功能是可以实现的。
实现过程中的难题
主表包含多个子表数据时的AND查询
{
"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
}]
}
customerNumber: 5135116903 AND markets.active:false
customerNumber: 5135116903 AND markets.active:false AND markets.marketId:A1
推荐阅读:
一款 IDEA 插件帮你优雅转化 DTO、VO、BO、PO、DO
内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper......等技术栈!
⬇戳阅读原文领取! 朕已阅
评论