SpringBoot+Querydsl 框架，大大簡化復(fù)雜查詢操作
概述
本篇博客主要將介紹的是利用spring query dsl框架實(shí)現(xiàn)的服務(wù)端查詢解析和實(shí)現(xiàn)介紹。
查詢功能是在各種應(yīng)用程序里面都有應(yīng)用，且非常重要的功能。用戶直接使用的查詢功能往往是在我們做好的UI界面上進(jìn)行查詢，UI會(huì)將查詢請(qǐng)求發(fā)給查詢實(shí)現(xiàn)的服務(wù)器，或者專門負(fù)責(zé)實(shí)現(xiàn)查詢的一個(gè)組件。市場上有專門做查詢的框架，其中比較出名，應(yīng)用也比較廣泛的是elasticsearch。
定義查詢請(qǐng)求
對(duì)于服務(wù)端來說，前端UI發(fā)送過來的查詢請(qǐng)求必然是按一定規(guī)則組織起來的，這樣的規(guī)則后端必須能夠支持和解析。換一種說法就是服務(wù)調(diào)用者和服務(wù)發(fā)布者之間需要遵循同一個(gè)規(guī)范才可以。百度的UI查詢是這樣定義的：
在上圖中加了藍(lán)色下劃線的地方即為我們?cè)诎俣犬?dāng)中搜索的字符串內(nèi)容，可以發(fā)現(xiàn)，百度的實(shí)現(xiàn)是將搜索的內(nèi)容當(dāng)做了http請(qǐng)求的url的參數(shù)來處理的，用了一個(gè)q作為key，q后面的內(nèi)容就是所查詢的內(nèi)容。
google的實(shí)現(xiàn)是類似的，如下圖所示：
對(duì)于google和百度這樣的只有一個(gè)搜索框的查詢界面，這樣處理是比較合理的，也不是整個(gè)查詢實(shí)現(xiàn)最關(guān)鍵的部分。更為關(guān)鍵的是后續(xù)服務(wù)器將這個(gè)查詢內(nèi)容進(jìn)行了怎樣的處理。對(duì)于別的一些產(chǎn)品來說，可能需要對(duì)某些關(guān)鍵字進(jìn)行單獨(dú)的查詢，這個(gè)時(shí)候肯定就不是一個(gè)搜索框能個(gè)滿足的需求了。
總的來說，我們可以有如下的方式來組織一個(gè)查詢
google-like查詢
這種查詢典型的應(yīng)用是一個(gè)查詢框，什么都可以查的情形，例如google和百度。對(duì)于這樣的查詢需求來說,在構(gòu)建查詢請(qǐng)求時(shí)只需將查詢的內(nèi)容放在http請(qǐng)求的的參數(shù)里面即可。
這樣的查詢解析是非常方便的，難度和需要考慮得事情在于要講查詢的內(nèi)容放到哪些地方去查詢。從數(shù)據(jù)庫的層面來說就是要去哪些數(shù)據(jù)庫的哪些表去查詢。
特定字段的類sql查詢
這種查詢是指定某個(gè)字段，然后采用類似于sql語句的寫法進(jìn)行查詢，各種查詢條件以一定的形式組織在一起，發(fā)給服務(wù)器進(jìn)行解析。這樣的查詢對(duì)服務(wù)器解析查詢的能力要求更高，它提供了一些更加具體的查詢條件。
例如我們以冒號(hào)表示等于,則一個(gè)查詢字符串的形式是:
name:bill
這個(gè)查詢的意思就是查詢名字name等于bill的記錄。
我們也可以將多個(gè)條件拼接在一起，讓他們直接用邏輯關(guān)系組合在一起，例如或者和并且的邏輯關(guān)系。例如：
name:bill?AND?city:LA
或者下面這種或者的關(guān)系：
name:bill?OR?city:LA
上面的查詢語句意味著我們的前后臺(tái)要定義一套自己的查詢邏輯和架構(gòu)，并且解析它，并將它轉(zhuǎn)換為正確的查詢。若我們想實(shí)現(xiàn)靈活的查詢，則上面的查詢語句在符合規(guī)則的前提下應(yīng)當(dāng)是可以自由組合的。怎么做取決于我們的實(shí)際需求。如果一個(gè)寫死的查詢關(guān)鍵字就能滿足我們的需求，則在當(dāng)前那個(gè)時(shí)期自然也是合理的。
但是從靈活性角度，技術(shù)角度，實(shí)現(xiàn)成靈活的可解析的，顯然是我們更想要的功能。最靈活的當(dāng)然就是sql語句能支持怎樣的查詢，我們都能支持對(duì)應(yīng)的查詢寫法，但是這對(duì)服務(wù)器的解析邏輯就有了更加高的要求，尤其是當(dāng)主表子表混在一起查詢之后，會(huì)更加復(fù)雜
使用Spring Data Querydsl
什么是Querydsl呢？Querydsl是一個(gè)框架，它可以通過它提供的的API幫助我們構(gòu)建靜態(tài)類型的SQL-like查詢，也就是在上面我們提到的組織查詢方式?？梢酝ㄟ^諸如Querydsl之類的流暢API構(gòu)造查詢。
Querydsl是出于以類型安全的方式維護(hù)HQL查詢的需要而誕生的。 HQL查詢的增量構(gòu)造需要String連接，這導(dǎo)致難以閱讀的代碼。通過純字符串對(duì)域類型和屬性的不安全引用是基于字符串的HQL構(gòu)造的另一個(gè)問題。
隨著域模型的不斷變化，類型安全性在軟件開發(fā)中帶來了巨大的好處。域更改直接反映在查詢中，而查詢構(gòu)造中的自動(dòng)完成功能使查詢構(gòu)造更快，更安全。
用于Hibernate的HQL是Querydsl的第一個(gè)目標(biāo)語言，如今querydsl支持JPA，JDO，JDBC，Lucene，Hibernate Search，MongoDB，Collections和RDFBean作為它的后端。
其官方網(wǎng)站在這里：http://querydsl.com/
Querydsl和spring有什么關(guān)系呢？幾個(gè)Spring Data的模塊通過QuerydslPredicateExecutor提供了與Querydsl的集成，如以下示例所示：
public?interface?QuerydslPredicateExecutor<T>?{
//查找并返回與Predicate匹配的單個(gè)entity。
??Optional?findById(Predicate?predicate) ;??
//查找并返回與Predicate匹配的所有entity
??Iterable?findAll(Predicate?predicate) ;???
//返回與Predicate匹配的數(shù)量。
??long?count(Predicate?predicate);????????????
//返回是否存在與Predicate匹配的entity。
??boolean?exists(Predicate?predicate);????????
?
??//?…?more?functionality?omitted.
}
Predicate就是我們需要傳入的一個(gè)查詢的抽象。
在spring當(dāng)中使用Querydsl，只需要在spring的repository接口繼承QuerydslPredicateExecutor，如以下示例所示:
interface?UserRepository?extends?CrudRepository,?QuerydslPredicateExecutor?{
}
在定義了上面的這個(gè)接口之后，我們就可以使用Querydsl Predicate編寫type-safe的查詢，如以下示例所示：
Predicate?predicate?=?user.firstname.equals("dave")
?.and(user.lastname.startsWith("mathews"));
?
userRepository.findAll(predicate);
上面的代碼構(gòu)建出的predicate體現(xiàn)在sql語句里的話就是這樣的: where firstname = 'dave' and lastname ='mathews%'。這就是所謂的類sql的查詢，用起來非常的直觀。
因此，我們可以將我們接收到的查詢請(qǐng)求，轉(zhuǎn)化為對(duì)應(yīng)的predicte，且從技術(shù)上講，只要predict支持的查詢拼接我們都能支持，難點(diǎn)只在于如何解析查詢請(qǐng)求，以及如何將他們轉(zhuǎn)換為對(duì)應(yīng)的predicate.
利用Spring Query DSL實(shí)現(xiàn)動(dòng)態(tài)查詢
下面是使用spring和Querydsl實(shí)現(xiàn)動(dòng)態(tài)查詢的一個(gè)例子.
現(xiàn)在假設(shè)我們有Model類如下:
public?class?Student?{
?
????private?String?id;
?
????private?String?gender;
?
????private?String?firstName;
?
????private?String?lastName;
?
????private?Date?createdAt;
??
????private?Boolean?isGraduated;
?
}
我們希望可以實(shí)現(xiàn)該類所有字段直接自由組合進(jìn)行查詢，且可以按照與和或的邏輯進(jìn)行查詢。且我們約定用冒號(hào)表示等于，例如:
firstname:li?AND?lastname:hua
?
firstname:li?OR?lastname:hua
?
firstname:li?AND?lastname:hua?AND?gender:male
上面的查詢都比較清晰，解析不會(huì)有太大難度，下面我們來看這樣一個(gè)查詢：
firstname:li?OR?lastname:hua?AND?gender:male
這個(gè)查詢的問題在于作為邏輯與的gender查詢，到底是只和前面一個(gè)條件進(jìn)行與操作，還是與前面兩個(gè)條件一起進(jìn)行一個(gè)與操作，顯然與的條件往往是作為filter的功能出現(xiàn)的。
因此我們應(yīng)當(dāng)將其看作整個(gè)其他條件的與操作，因此我們需要先將前面的條在組合在一起，例如，我們可以使用括號(hào)表示這個(gè)邏輯，那么查詢就會(huì)變成:
(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?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?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?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?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?paramsOr;
?
????private?List?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?paramsOr,?Class?classType) {
????????List?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?????????????????result?=?result.or(predicates.get(i));
????????????}
????????}
????????return?result;
????}
?
????private?BooleanExpression?buildBooleanExpressionAnd(List?paramsAnd,?Class?classType) {
????????List?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?????????????????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?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?timePath?=?entityPath.getDateTime(criteria.getKey(),?Date.class);
????????NumberPath?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?entityPath,
????????????StringPath?stringPath,?DateTimePath?timePath,?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,
????????????DateTimePath?timePath,?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,
????????????DateTimePath?timePath,?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,?PathBuilder?entityPath,?StringPath?stringPath,
????????????DateTimePath?timePath,?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;
????}
?
}
查詢條件的抽象類SearchCriteria定義如下:
public?class?SearchCriteria?{
????private?String?key;
????private?String?operation;
????private?Object?value;
}
大致的實(shí)現(xiàn)邏輯如下圖所示：
比較關(guān)鍵的點(diǎn)有下面這些：
對(duì)字符串的解析需要借助正則表達(dá)式的幫助，正則表達(dá)式?jīng)Q定了我們支持怎樣的查詢. 由于字符串可以任意輸入，存在無限種可能，對(duì)查詢字符串的校驗(yàn)很關(guān)鍵也很復(fù)雜。 不同邏輯的查詢條件需要存放在不同的容器里面，因?yàn)樗麄兊钠唇舆壿嫴灰粯?，一個(gè)是或一個(gè)是與 不同的字段類型需要調(diào)用不同的生成Predicate的方法，例如String，Boolean和Date這些類型他們都有自己對(duì)應(yīng)的查詢實(shí)現(xiàn) 生成子表的Predicate很復(fù)雜，與主表的查詢條件一起查詢時(shí)邏輯更加復(fù)雜，上面的邏輯拿掉了這一部分。但是這個(gè)功能是可以實(shí)現(xiàn)的。
實(shí)現(xiàn)過程中的難題
主表包含多個(gè)子表數(shù)據(jù)時(shí)的AND查詢
距離說明，現(xiàn)在有數(shù)據(jù)定義如下：
{
?"customerNumber":?"5135116903",
?"customerType":?"INDIVIDUAL",
?"createdBy":?"[email protected]",
?"changedBy":?"[email protected]",
?"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
?}]
}
其中父節(jié)點(diǎn)表是customer，子節(jié)點(diǎn)markets信息存儲(chǔ)在market表當(dāng)中。
現(xiàn)在，假設(shè)我們有這樣的查詢：
customerNumber:?5135116903?AND?markets.active:false
沒有疑問，上面的數(shù)據(jù)應(yīng)該被查出來?，F(xiàn)在查詢條件變成下面這樣：
customerNumber:?5135116903?AND?markets.active:false?AND?markets.marketId:A1
現(xiàn)在問題來了，語句的意思是此客戶的marker既要是非active 的且ID要是A1，但是此客戶又有多個(gè)market，從整個(gè)數(shù)組里來看，這個(gè)條件是滿足的。但是從單個(gè)的market個(gè)體來看這個(gè)條件是不滿足的。而我們作為用戶的話希望得到的效果必然是無法查處此customer信息。
這會(huì)給實(shí)現(xiàn)帶來問題，因?yàn)橛捎趍arket是一個(gè)數(shù)組，在數(shù)據(jù)表中對(duì)應(yīng)的就是幾條記錄，我們?cè)诮馕霾?gòu)建子表查詢時(shí)，必須確保對(duì)于子表的查詢條件是作用于單獨(dú)的一個(gè)node，也就是單獨(dú)的一條記錄，而不是從整個(gè)數(shù)組當(dāng)中去查，否則就會(huì)有問題。
來源：https://blog.csdn.net/topdeveloperr
如有文章對(duì)你有幫助，
“在看”和轉(zhuǎn)發(fā)是對(duì)我最大的支持！
推薦：
評(píng)論
圖片
表情