【ElasticSearch实战】——java操作ES基本查询在项目中应用

引言

最近小编负责的一个语音质检 项目中用到了ES，下面结合实例代码分享一下java操作ES查用的查询写法。

关于es的基本操作，例如新建索引、查询封装类，请参考前面博客：【ElasticSearch实战】——封装java操作es基础架构

1、ES在项目中的位置

2、项目查询需求

从上面查询条件来看，其中包括了精确查询，模糊查询及时间范围查询等，这些查询 都会在下面 样例代码中给出，并且还有部分统计的写法，例如机器质检不合格的数量， 人工审核不合格的数量等。

3、查询代码

package com.jack.search.es.service;

import com.jack.common.constant.SearchConstants;
import com.jack.search.es.model.CaseCallCheckListQueryInfo;
import org.apache.commons.lang.StringUtils;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.index.query.RangeQueryBuilder;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.SearchHits;
import org.elasticsearch.search.aggregations.Aggregation;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.Aggregations;
import org.elasticsearch.search.aggregations.bucket.filter.Filter;
import org.elasticsearch.search.aggregations.bucket.filter.FilterAggregationBuilder;
import org.elasticsearch.search.aggregations.metrics.cardinality.Cardinality;
import org.elasticsearch.search.aggregations.metrics.sum.Sum;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.elasticsearch.search.sort.FieldSortBuilder;
import org.elasticsearch.search.sort.SortBuilders;
import org.elasticsearch.search.sort.SortOrder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @author zhenghao
 * @description: 录音库查询
 * @date 2019/10/1115:49
 */
@Service
public class CallCheckQueryService {

    @Autowired
    private QueryService queryService;
    @Autowired
    private ElasticSearchService elasticSearchService;

    public Map<String, Object> getCallCheckList(CaseCallCheckListQueryInfo queryInfo) {
        if (queryInfo == null) {
            return null;
        }
        //构造查询条件
        BoolQueryBuilder boolQueryBuilder = this.getCallCheckQueryBuilder(queryInfo);
        //排序
        String orderBy = queryInfo.getOrderBy();
        if (StringUtils.isEmpty(orderBy)) {
            orderBy = String.format("%s:%s", "telTime", "desc");
        }
        //分页
        Integer pageNo = queryInfo.getPageNo();
        if ((pageNo == null) || (pageNo <= 0)) {
            pageNo = 0;
        } else {
            pageNo -= 1;
        }

        Integer pageSize = queryInfo.getPageSize();
        if ((pageSize == null) || (pageSize < 1)) {
            pageSize = 10;
        }
        //构造排序构造器FieldSortBuilder，设置排序参数
        String[] orderStr = orderBy.split(":");
        FieldSortBuilder sort = SortBuilders.fieldSort(orderStr[0]).order("desc".equalsIgnoreCase(orderStr[1])? SortOrder.DESC:SortOrder.ASC);

        //AggregationBuilder 相当于 mysql中的 group by
        //第一级机器质检不合格数据聚合 聚合名称为 hasError ，聚合数据条件为 字段 hasError>=1
        FilterAggregationBuilder hasErrorAggregationBuilder = AggregationBuilders.filter("hasError", QueryBuilders.rangeQuery("hasError").gte(1));
        //构造第二级子聚合 每一个质检项的值 >=1  注意：每一个checkItem 是一个字段，代表一个质检项 0代表没有命中该质检项，大于0代表表中
        for (int i = SearchConstants.CHECK_ITEM_MIN; i<= SearchConstants.CHECK_ITEM_MAX; i++) {
            String checkItemName = String.format("checkItem%d", i);
            hasErrorAggregationBuilder.subAggregation(AggregationBuilders.filter(checkItemName, QueryBuilders.rangeQuery(checkItemName).gte(1)));
        }
        //第一级人工审核不合格的数据聚合
        FilterAggregationBuilder auditHasErrorAggregationBuilder = AggregationBuilders.filter("auditHasError", QueryBuilders.rangeQuery("auditHasError").gte(1));

        //整合查询条件，相当于组长sql cardinality 表示去重
        FilterAggregationBuilder allCheckAggregationBuilder = AggregationBuilders.filter("allCheck", boolQueryBuilder)
                .subAggregation(hasErrorAggregationBuilder)
                .subAggregation(auditHasErrorAggregationBuilder)
                .subAggregation(AggregationBuilders.sum("totalTelLength").field("telLength"))
                .subAggregation(AggregationBuilders.cardinality("userId").field("userId"));

        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
        searchSourceBuilder.query(boolQueryBuilder).sort(sort).from(pageNo*pageSize).size(pageSize).aggregation(allCheckAggregationBuilder);
        //执行查询
        SearchRequest searchRequest = new SearchRequest();
        searchRequest.indices(SearchConstants.CALL_INDEX_NAME).types(SearchConstants.CALL_INDEX_TYPE).source(searchSourceBuilder);

        SearchResponse searchResponse = elasticSearchService.search(searchRequest);
        if (searchResponse == null) {
            return null;
        }
        //从查询结果中获得想要数据
        SearchHits searchHits = searchResponse.getHits();
        //获得查询结果条数
        long total = searchHits.getTotalHits();

        Map<String, Object> resultMap = new HashMap<>(10);
        resultMap.put("total", total);
        resultMap.put("tookInMillis", searchResponse.getTook().getMillis());

        //获得查询结果并转换为map
        if (total > 0) {
            SearchHit[] searchHitsArr = searchHits.getHits();
            List<Map<String, Object>> resultMapList = new ArrayList<>(searchHitsArr.length);
            for (int i = 0; i < searchHitsArr.length; i++) {
                Map<String, Object> map = searchHitsArr[i].getSourceAsMap();
                resultMapList.add(map);
            }

            resultMap.put("list", resultMapList);
        }
        //获得聚合桶数据
        Filter allCheckFilter = searchResponse.getAggregations().get("allCheck");
        Aggregations allCheckAggregations = allCheckFilter.getAggregations();
        //通话总时长
        Sum sum = allCheckAggregations.get("totalTelLength");
        Cardinality userIdCardinality = allCheckAggregations.get("userId");
        //机器审核错误的数量
        Filter hasErrorFilter = allCheckAggregations.get("hasError");
        Aggregations hasErrorAggregations = hasErrorFilter.getAggregations();
        //人工审核错误的数量
        Filter auditHasErrorFilter = allCheckAggregations.get("auditHasError");

        long errorCount = hasErrorFilter.getDocCount();
        long auditErrorCount = auditHasErrorFilter.getDocCount();
        resultMap.put("errorCount", errorCount);
        resultMap.put("auditErrorCount", auditErrorCount);
        resultMap.put("goodRate", total>0?1.0*(total-errorCount)/total:0);
        resultMap.put("auditGoodRate", total>0?1.0*(total-auditErrorCount)/total:0);
        resultMap.put("totalTelLength", sum.getValue());
        resultMap.put("averageTelLength", total>0?sum.getValue()/total:0);
        resultMap.put("userCount", userIdCardinality.getValue());

        for (int k = SearchConstants.CHECK_ITEM_MIN; k<= SearchConstants.CHECK_ITEM_MAX; k++) {
            Filter checkItemFilter = hasErrorAggregations.get(String.format("checkItem%d", k));
            resultMap.put(String.format("checkItem%dCount", k), checkItemFilter.getDocCount());
        }

        return resultMap;
    }

    /**
     * @Description: 构造查询条件
     * @author: zhenghao
     * @date: 2019/10/11 15:54
     */
    private BoolQueryBuilder getCallCheckQueryBuilder(CaseCallCheckListQueryInfo queryInfo) {

        /**
         * BoolQueryBuilder 是es提供的一个查询接口，可以对其进行参数设置完成对应的查询
         */
        BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();

        //根据主键id查询
        Long[] callRecordIds = queryInfo.getCallRecordIds();
        if ((callRecordIds != null) && (callRecordIds.length > 0)) {
            String[] strCallRecordIds = new String[callRecordIds.length];
            for (int i = 0; i < callRecordIds.length; i++) {
                strCallRecordIds[i] = String.valueOf(callRecordIds[i]);
            }
            //idsQuery查询多个id 相当于mysql中的 where id in(1,2,3)
            //filter必须匹配，但他以不评分、过滤模式来进行，这些语句对评分没有贡献，只是根据过滤标准来排除或包含文档
            boolQueryBuilder.filter(QueryBuilders.idsQuery().addIds(strCallRecordIds));
        }
        //根据caseId精确查询 termQuery 完全匹配
        Long caseId = queryInfo.getCaseId();
        if (caseId != null) {
            boolQueryBuilder.filter(QueryBuilders.termQuery("caseId", caseId));
        }
        //termsQuery 一次匹配多个值，查询多个公司的数据
        Long[] partnerIds = queryInfo.getPartnerIds();
        if ((partnerIds != null) && (partnerIds.length > 0)) {
            long[] pids = new long[partnerIds.length];
            for (int i = 0; i < pids.length; i++) {
                pids[i] = partnerIds[i];
            }
            boolQueryBuilder.filter(QueryBuilders.termsQuery("partnerId", pids));
        }

        //注意在es中是long类型 非包装类型Long
        Long[] departmentIds = queryInfo.getDepartmentIds();
        if ((departmentIds != null) && (departmentIds.length > 0)) {
            long[] dids = new long[departmentIds.length];
            for (int i = 0; i < dids.length; i++) {
                dids[i] = departmentIds[i];
            }
            boolQueryBuilder.filter(QueryBuilders.termsQuery("departmentId", dids));
        }
        //根据web端传递的参数不同，区分对应的userId不同的值
        Integer allotState = queryInfo.getAllotState();
        if (allotState != null) {
            if (allotState == 0) {
                //精确查询
                boolQueryBuilder.filter(QueryBuilders.termQuery("userId", -1));
            } else {
                Long userId = queryInfo.getUserId();
                if (userId != null) {
                    //精确查询
                    boolQueryBuilder.filter(QueryBuilders.termQuery("userId", userId));
                } else {
                    //范围查询 gt 代表大于
                    boolQueryBuilder.filter(QueryBuilders.rangeQuery("userId").gt(0));
                }
            }
        } else {
            Long userId = queryInfo.getUserId();
            if (userId != null) {
                boolQueryBuilder.filter(QueryBuilders.termQuery("userId", userId));
            } else {
                Integer auditHasError = queryInfo.getAuditHasError();
                // 查询已经分配质检用户的质检列表
                if ((auditHasError != null) && (auditHasError == -1)) {
                    boolQueryBuilder.filter(QueryBuilders.rangeQuery("userId").gt(0));
                }
            }
        }
        //将string类型的字段值 去掉*匹配符及空格
        String userName = queryService.trimWildcardQuery(queryInfo.getUserName());
        if (!StringUtils.isEmpty(userName)) {
            //精确查询
            boolQueryBuilder.filter(QueryBuilders.termQuery("userName", userName));
        }
        Long auditUserId = queryInfo.getAuditUserId();
        if (auditUserId != null) {
            boolQueryBuilder.filter(QueryBuilders.termQuery("auditUserId", auditUserId));
        }
        String auditUserName = queryService.trimWildcardQuery(queryInfo.getAuditUserName());
        if (!StringUtils.isEmpty(auditUserName)) {
            boolQueryBuilder.filter(QueryBuilders.termQuery("auditUserName", auditUserName));
        }

        Long clientId = queryInfo.getClientId();
        if (clientId != null) {
            boolQueryBuilder.filter(QueryBuilders.termQuery("clientId", clientId));
        }

        Long batchId = queryInfo.getBatchId();
        if (batchId != null) {
            boolQueryBuilder.filter(QueryBuilders.termQuery("batchId", batchId));
        }
        String caseCode = queryService.trimWildcardQuery(queryInfo.getCaseCode());
        if (caseCode != null) {
            //wildcardQuery模糊查询，相当于mysql中的like
            boolQueryBuilder.filter(QueryBuilders.wildcardQuery("caseCode", String.format("*%s*", caseCode)));
        }
        String borrowerName = queryService.trimWildcardQuery(queryInfo.getBorrowerName());
        if (!StringUtils.isEmpty(borrowerName)) {
            //wildcardQuery模糊查询，相当于mysql中的like
            boolQueryBuilder.filter(QueryBuilders.wildcardQuery("borrowerName", String.format("*%s*", borrowerName)));
        }
        String contactName = queryService.trimWildcardQuery(queryInfo.getContactName());
        if (!StringUtils.isEmpty(contactName)) {
            boolQueryBuilder.filter(QueryBuilders.wildcardQuery("contactName", String.format("*%s*", contactName)));
        }

        String contactTel = queryService.trimWildcardQuery(queryInfo.getContactTel());
        if (!StringUtils.isEmpty(contactTel)) {
            boolQueryBuilder.filter(QueryBuilders.wildcardQuery("contactTel", String.format("*%s*", contactTel)));
        }

        String fileName = queryService.trimWildcardQuery(queryInfo.getFileName());
        if (!StringUtils.isEmpty(fileName)) {
            boolQueryBuilder.filter(QueryBuilders.wildcardQuery("fileName", String.format("*%s*", fileName)));
        }

        //常见需求范围查询 查询某个时间段范围内的数据
        Long telTimeMin = queryInfo.getTelTimeMin();
        Long telTimeMax = queryInfo.getTelTimeMax();
        if ((telTimeMin != null) || (telTimeMax != null)) {
            //获得范围查询builder
            RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("telTime");
            if (telTimeMin != null) {
                //gte 代表大于等于
                rangeQueryBuilder.gte(telTimeMin);
            }
            if (telTimeMax != null) {
                //lt 代表小于某个值 lte代表小于等于某个值
                rangeQueryBuilder.lt(telTimeMax);
            }
            boolQueryBuilder.filter(rangeQueryBuilder);
        }

        //查询某个时间段范围内的数据
        Long telMillSecMin = queryInfo.getTelMillSecMin();
        Long telMillSecMax = queryInfo.getTelMillSecMax();
        if ((telMillSecMin != null) || telMillSecMax != null) {
            RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("telMillSec");
            if (telMillSecMin != null) {
                rangeQueryBuilder.gte(telMillSecMin);
            }
            if (telMillSecMax != null) {
                rangeQueryBuilder.lt(telMillSecMax);
            }
        }
        //时间范围查询
        Long auditTimeMin = queryInfo.getAuditTimeMin();
        Long auditTimeMax = queryInfo.getAuditTimeMax();
        if ((auditTimeMin != null) || (auditTimeMax != null)) {
            RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("auditTime");
            if (auditTimeMin != null) {
                rangeQueryBuilder.gte(auditTimeMin);
            }
            if (auditTimeMax != null) {
                rangeQueryBuilder.lt(auditTimeMax);
            }
            boolQueryBuilder.filter(rangeQueryBuilder);
        }
        String contactRelationName = queryService.trimWildcardQuery(queryInfo.getContactRelationName());
        if (!StringUtils.isEmpty(contactRelationName)) {
            boolQueryBuilder.filter(QueryBuilders.wildcardQuery("contactRelationName", String.format("*%s*", contactRelationName)));
        }

        String telNumber = queryService.trimWildcardQuery(queryInfo.getTelNumber());
        if (!StringUtils.isEmpty(telNumber)) {
            boolQueryBuilder.filter(QueryBuilders.wildcardQuery("telNumber", String.format("*%s*", telNumber)));
        }

        Integer hasError = queryInfo.getHasError();
        if (hasError == null) {
            boolQueryBuilder.filter(QueryBuilders.rangeQuery("hasError").gte(-1));
        } else {
            if (hasError >= 0) {
                boolQueryBuilder.filter(QueryBuilders.termQuery("hasError", hasError));
            } else {
                boolQueryBuilder.filter(QueryBuilders.rangeQuery("hasError").gte(0));
            }
        }

        Integer auditHasError = queryInfo.getAuditHasError();
        if (auditHasError == null) {
            boolQueryBuilder.filter(QueryBuilders.rangeQuery("auditHasError").gte(-1));
        } else if (auditHasError >= -1) {
            boolQueryBuilder.filter(QueryBuilders.termQuery("auditHasError", auditHasError));
        }

        String auditRemark = queryService.trimWildcardQuery(queryInfo.getAuditRemark());
        if (!StringUtils.isEmpty(contactName)) {
            boolQueryBuilder.filter(QueryBuilders.wildcardQuery("auditRemark", String.format("*%s*", auditRemark)));
        }

        Long[] recheckDepartmentIds = queryInfo.getRecheckDepartmentIds();
        if ((recheckDepartmentIds != null) && (recheckDepartmentIds.length > 0)) {
            long[] rdids = new long[recheckDepartmentIds.length];
            for (int i = 0; i < rdids.length; i++) {
                rdids[i] = recheckDepartmentIds[i];
            }
            boolQueryBuilder.filter(QueryBuilders.termsQuery("recheckDepartmentId", rdids));
        }
        //根据数据记录中是否存在某个字段进行查询
        Long recheckUserId = queryInfo.getRecheckUserId();
        if (recheckUserId != null) {
            boolQueryBuilder.filter(QueryBuilders.termQuery("recheckUserId", recheckUserId));
        } else {
            Integer allotRecheckState = queryInfo.getAllotRecheckState();
            if (allotRecheckState != null) {
                if (allotRecheckState == 0) {
                    //mustNot 档必须不匹配这些条件才能被包含进来，existsQuery 存在指定字段
                    BoolQueryBuilder recheckUserIdQueryBuilder = new BoolQueryBuilder();
                    //本条件是：返回不包含recheckUserId的字段的数据记录
                    recheckUserIdQueryBuilder.mustNot(QueryBuilders.existsQuery("recheckUserId"));
                    boolQueryBuilder.filter(recheckUserIdQueryBuilder);
                } else {
                    //本条件是：返回包含recheckUserId 这个字段的数据记录
                    boolQueryBuilder.filter(QueryBuilders.existsQuery("recheckUserId"));
                }
            }

            Integer recheckHasError = queryInfo.getRecheckHasError();
            if (recheckHasError != null) {
                boolQueryBuilder.filter(QueryBuilders.termQuery("recheckHasError", recheckHasError));
            } else {
                Integer hasRecheck = queryInfo.getHasRecheck();
                if (hasRecheck != null) {
                    if (hasRecheck == -1) {
                        boolQueryBuilder.filter(QueryBuilders.existsQuery("recheckUserId"));
                    } else if (hasRecheck == 0) {
                        //和上面不同的写法
                        boolQueryBuilder.mustNot(QueryBuilders.existsQuery("recheckHasError"));
                    } else {
                        boolQueryBuilder.filter(QueryBuilders.existsQuery("recheckHasError"));
                    }
                }
            }

            Long recheckTimeMin = queryInfo.getRecheckTimeMin();
            Long recheckTimeMax = queryInfo.getRecheckTimeMax();
            if ((recheckTimeMin != null) || (recheckTimeMax != null)) {
                RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("recheckTime");
                if (recheckTimeMin != null) {
                    rangeQueryBuilder.gte(recheckTimeMin);
                }
                if (recheckTimeMax != null) {
                    rangeQueryBuilder.lt(recheckTimeMax);
                }
                boolQueryBuilder.filter(rangeQueryBuilder);
            }

        }
        return boolQueryBuilder;
    }
}

小编在项目中将代码做了详细的 注释，通过注释可以看到所有的写法。

如果 本项目对您有帮助，记得给 星星。本项目后续会继续完善java对ES的操作，包括各种复杂查询和统计。