Elasticsearch(ES6)------（5）kibana的es查询、mysql查询转换和对应javaAPI使用（一）

准备数据测试数据

我这里提前在mysql中造好了一些数据，通过javaAPI导入到es中

mysql建表语句

CREATE TABLE `product_item` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `product_name` varchar(256) NOT NULL COMMENT '产品名称',
  `price` double(18,2) NOT NULL COMMENT '价格',
  `count` int NOT NULL COMMENT '数量',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `supplier_id` bigint DEFAULT NULL COMMENT '供应商ID',
  PRIMARY KEY (`product_id`) USING BTREE,
  KEY `idx_product_name` (`product_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='商品表';

es索引信息，这种创建方式不考虑分词

mysql数据导入ES，java使用BulkProcessor做ES批量导入，这里就不多介绍了

@PostMapping(value = "/db-to-es")
    public BaseResponse dbToEs() throws InterruptedException {
        List<ProductItem> productItemList = productItemService.getProductItemList();
        EsManager esManager = new EsManager();
        BulkProcessor bulkProcessor = null;
        try {
            bulkProcessor = esManager.bulkProcessor();
        } catch (UnknownHostException e) {
            e.printStackTrace();
        }
        for (ProductItem var1 : productItemList) {
            String s = new Gson().toJson(var1);
            bulkProcessor.add(new IndexRequest("product_item", "_doc", var1.getProductId().toString()).source(s, XContentType.JSON));
        }
        bulkProcessor.awaitClose(30, TimeUnit.SECONDS);
        return BaseResponse.ok("保存数据成功:", productItemList.size());
    }

mysql数据

ES数据查看

进入正题

1. mysql的 = 查询对应es的term

select *from product_item where product_id='8001'

ES 查询

GET product_item/_search
{
  "query": {
    "term": {
      "productId": {
        "value": "8001"
      }
    }
  }
}

java代码

@PostMapping(value = "/search/term")
    public BaseResponse term(){
        //Term查询
        TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("productId","8001");
        //在这里输入索引名称和type类型
        SearchResponse response = transportClient.prepareSearch("product_item").setTypes("_doc")
                //设置查询类型java
                .setSearchType(SearchType.DFS_QUERY_THEN_FETCH)
                //设置查询关键词
                .setQuery(termQueryBuilder)
                //返回搜索响应信息
                .get();
        //打印下查询条件
        System.out.println("termQueryBuilder="+termQueryBuilder);
        SearchHits hits = response.getHits();
        List<ProductItem> list = new ArrayList<>();
        ProductItem productItem;
        for (SearchHit searchHit : hits) {
            Map<String, Object> sourceAsMap = searchHit.getSourceAsMap();
            productItem = new ProductItem();
            System.out.printf("searchHit="+searchHit);
            productItem.setProductId(Long.valueOf(sourceAsMap.get("productId").toString()));
            productItem.setPrice(Double.valueOf(sourceAsMap.get("price").toString()));
            productItem.setProductName(sourceAsMap.get("productName").toString());
            productItem.setSupplierId(Long.valueOf(sourceAsMap.get("supplierId").toString()));
            productItem.setCount(Integer.valueOf(sourceAsMap.get("count").toString()));
            list.add(productItem);
        }
        return BaseResponse.ok("success:", list);
    }

 2. like 查询，对应 es的wildcard  并对结果聚合 group by

ES查询 from=0 ,size=0 就是不显示hits结果内容，通过结果图可以看出来，这里着重说一下，from=0 并不是从第几页，而是从第几行，后续写分页查询会在用到这里。aggs是对结果进行聚合，聚合器的内容也很多，后续有时间会单独写一篇

GET product_item/_search
{
    "query": {
        "wildcard" : { 
          "productName" : "*显示器*" 
        }
    },
    "from": 0,
    "size": 0, 
    "aggs": {
		"distinct": {
			"terms": {
				"field": "productName"
			}
		}
	}
}

buckets中的doc_conut 就是总数,和count(*)一样

java代码

/**
     * 模糊查询，结果聚合
     * @return
     */
    @RequestMapping(value = "/search/wildcard", method = RequestMethod.POST)
    public BaseResponse<List<CateAttrRevise>> wildcard() {
        AggregationBuilder aggregationBuilder = AggregationBuilders
                .terms("productName_aggs").field("productName");
        BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
        //模糊查询
        boolQueryBuilder.must(QueryBuilders.wildcardQuery("productName","*显示器*"));
        SearchResponse response = transportClient.prepareSearch("product_item").setTypes("_doc")
                //设置查询类型java
                .setSearchType(SearchType.DFS_QUERY_THEN_FETCH)
                //设置聚合器
                .addAggregation(aggregationBuilder)
                //设置查询关键词
                .setQuery(boolQueryBuilder)
                //都设置为0，不显示hits结果内容
                .setFrom(0)
                .setSize(0)
                .get();
        Terms terms = response.getAggregations().get("productName_aggs");
        Map<String, Long> map = new HashMap<>();
        for(int i=0;i<terms.getBuckets().size();i++) {
            map.put(terms.getBuckets().get(i).getKey().toString(),terms.getBuckets().get(i).getDocCount());
        }
        return BaseResponse.ok(map);
    }