0 概述
在實際工作中，我們會經常遇到一些and 與 or 以及非相關嵌套的查詢方式，本文采用遞歸方式來實作這種相對複雜的嵌套查詢。
1 執行個體分析
如下我們要查詢使用者表中name 為小紅 且他的年齡等于10 或者大于15
select * from user where name='小紅' and （age=10 or age >15)
or 查詢寫法執行個體
select * from user where objectType=13 and （userId=1234 or userType =2 )
query 寫法
{
"query": {
"bool": {
"must": [
{
"term": {
"objectType": 13
}
},
{
"bool": {
"should": [
{
"bool": {
"must": [
{
"term": {
"userId": 1234
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"userType": 2
}
}
]
}
}
]
}
}
]
}
}
}
//輸出JSON
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.index.query.RangeQueryBuilder;
import org.elasticsearch.search.builder.SearchSourceBuilder;
public class QueryBuilderJSON {
public static void main(String[] args) {
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
final BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();
queryBuilder.must(QueryBuilders.termQuery("objectType", 13));
final BoolQueryBuilder and = QueryBuilders.boolQuery();
queryBuilder.must(and);
final BoolQueryBuilder or1 = QueryBuilders.boolQuery();
final BoolQueryBuilder or2 = QueryBuilders.boolQuery();
and.should(or1);
and.should(or2);
RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("created").from(12);
or1.must(QueryBuilders.termQuery("useType", 1));
or2.filter(rangeQueryBuilder);
System.out.print(searchSourceBuilder.query(queryBuilder).toString());
}
}
2 實作
ElasticSearch 基本查詢方式寫法可以參考我之前寫的ElasticSearch資料查詢，這裡實作在之前寫的代碼上進行了重構，以更好的支援這種複雜的嵌套查詢。
定義查詢條件（對于某一列進行的操作）
public class Condition {
private String fieldName;//字段名稱
private Object fieldValue;//字段值
private FieldOperator operator;//操作類型
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public Object getFieldValue() {
return fieldValue;
}
public void setFieldValue(Object fieldValue) {
this.fieldValue = fieldValue;
}
public FieldOperator getOperator() {
return operator;
}
public void setOperator(FieldOperator operator) {
this.operator = operator;
}
}
嵌套查詢，含有子查詢的
public class HybridCondition extends Condition {
private Connector connector;
private List<Condition> subConditions=new ArrayList<>();
public HybridCondition(Connector connector) {
this.connector = connector;
}
public Connector getConnector() {
return connector;
}
public void setConnector(Connector connector) {
this.connector = connector;
}
public List<Condition> getSubConditions() {
return subConditions;
}
//嵌套的查詢
public void setSubConditions(
List<Condition> subConditions) {
this.subConditions = subConditions;
}
public HybridCondition addCondition(Condition condition) {
//将此查詢條件添加到查詢添加清單中去，避免死循環
if (condition == this) {
return this;
}
subConditions.add(condition);
return this;
}
}
public enum Connector {
AND("and"),
OR("or"),
EMPTY("");
private String connName;
Connector(String connName) {
this.connName = connName;
}
public String getConnName() {
return connName;
}
public static boolean isEmpty(Connector conn) {
return conn == null || conn == EMPTY;
}
}
遞歸實作這種嵌套查詢
@Component
public class ElasticSearchQueryBuilder {
@Autowired
private FieldObjectHandlerRegistry registry;
private final static int MAX_DEPTH=200;
public ESSearchRequest buildQuery(QueryContext queryContext) {
final BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();
final String esIndex = queryContext.getIndex();
//建構查詢文法
buildQuery(queryBuilder, queryContext.getFieldObjects());
ESSearchRequest esSearchRequest = queryContext.getEsClient().buildingESSearchRequest()
.setIndices(esIndex)
.setTypes(queryContext.getType())
.setSize(queryContext.getPageSize())
.setFrom(queryContext.getOffset())
.setQuery(queryBuilder);
//建構排序
setSearchRequestBuilderSort(esSearchRequest, queryContext);
return esSearchRequest;
}
private void setSearchRequestBuilderSort(ESSearchRequest esSearchRequest,
QueryContext queryContext) {
if (queryContext.getSortRules() == null) {
return;
}
for (SortRule sortRule : queryContext.getSortRules()) {
if (ContentConstant.SORT_FIELD.contains(sortRule.getField())) {
esSearchRequest.addSort(sortRule.getField(), sortOrder(sortRule.getSort()));
}
}
}
private SortOrder sortOrder(final String order) {
return "asc".equalsIgnoreCase(order) ? SortOrder.ASC : SortOrder.DESC;
}
private void buildQuery(BoolQueryBuilder queryBuilder, List<Condition> conditions) {
if (CollectionUtils.isEmpty(conditions)) {
return;
}
for (Condition object : conditions) {
buildQueryBuilder(object,queryBuilder,0);
}
}
private void buildQueryBuilder(Condition queryCondition,BoolQueryBuilder queryBuilder,int depth) {
if (depth > MAX_DEPTH) {
//因為此函數是嵌套調用的，防止使用不規範造成死循環，此處需要對嵌套深度進行判斷
throw new IllegalArgumentException("query condition loop depth is too big.");
}
if (queryCondition instanceof HybridCondition) {
final BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
HybridCondition hybridCondition = (HybridCondition) queryCondition;
if (Connector.AND.equals(hybridCondition.getConnector())) {
for (Condition condition : hybridCondition.getSubConditions()) {
buildQueryBuilder(condition,boolQueryBuilder,depth+1);
}
queryBuilder.must(boolQueryBuilder);
}else if (Connector.OR.equals(hybridCondition.getConnector())) {
for (Condition condition : hybridCondition.getSubConditions()) {
buildQueryBuilder(condition,boolQueryBuilder,depth+1);
}
queryBuilder.should(boolQueryBuilder);
}
return;
}
registry.getHandler(queryCondition.getOperator().getOperator()).
buildQuerySyntaxForElasticSearch(queryBuilder, queryCondition);
}
}
各個查詢條件采用builder模式實作
public class ConditionBuilder {
private List<Condition> conditions;
public ConditionBuilder() {
conditions = new ArrayList<>();
}
public ConditionBuilder buildName(String name) {
if (name != null) {
conditions.add(buildCondition("name",name, FieldOperator.EQ));
}
return this;
}
public ConditionBuilder builderUserIds(List<Long> userIds) {
if (CollectionUtils.isNotEmpty(userIds)) {
conditions.add(buildCondition("userId", userIds, FieldOperator.IN));
}
return this;
}
//之間是或的關系
public ConditionBuilder builderAge(Integer equalAge,Integer largeAge) {
HybridCondition hybridCondition = new HybridCondition(Connector.AND);
if (equalAge!=null) {
HybridCondition temp = new HybridCondition(Connector.OR);
temp.addCondition(buildCondition("age", equalAge, FieldOperator.EQ));
hybridCondition.addCondition(temp);
}
if (largeAge!=null) {
HybridCondition temp = new HybridCondition(Connector.OR);
temp.addCondition(buildCondition("age", equalAge, FieldOperator.GE));
hybridCondition.addCondition(temp);
}
if (!hybridCondition.getSubConditions().isEmpty()) {
this.fieldObjects.add(hybridCondition);
}
return this;
}
public List<Condition> getConditions() {
return conditions;
}
private Condition buildCondition(String fieldName, Object value, FieldOperator operator) {
Condition condition = new Condition();
condition.setFieldName(fieldName);
condition.setFieldValue(value);
condition.setOperator(operator);
return condition;
}
}
![圖解elasticsearch的_source、_all、store和index[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)