ElasticSearch sql 查詢改寫原生語法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

	public List<String> findForES(String indexName, String beginColumnName, String where) throws Exception {
		
		HttpClient client = null;
		HttpPost httpPost = null;
		HttpResponse response = null;
		StringBuffer url = new StringBuffer();
		String result = "";
		List<String> resultList = null;
		try {
			client = HttpClients.custom().build();
			url.append(ELASTICSEARCH_URL + "_sql?sql=");
			url.append("select " + beginColumnName);
			url.append("  from " + indexName.toLowerCase() + "/" + ELASTICSEARCH_TYPE );
			url.append(where);
			logger.info("ES SQL:" + url.toString());
			httpPost = new HttpPost(url.toString().replaceAll(" ", "%20"));
			httpPost.addHeader("Content-Type", ES_CONTENT_TYPE_JSON);
			response = client.execute(httpPost);
			if(response.getStatusLine().getStatusCode() == 200) {
				result = EntityUtils.toString(response.getEntity(), "UTF-8");
				if(StringUtils.isNotBlank(result)) {
					resultList = JsonUtil.jsonSkipToList(result);
				}
			}
				
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
		
		return resultList;
	}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

	public List<String> findForES(String index, String[] include, QueryBuilder queryBuilder) throws Exception {
		SearchRequest searchRequest = new SearchRequest(index);
		SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
		if(include != null){
			searchSourceBuilder.fetchSource(include, null);
		}
		searchSourceBuilder.query(queryBuilder == null ? QueryBuilders.matchAllQuery() : queryBuilder);
		searchSourceBuilder.from(0).size(5000);
		searchSourceBuilder.timeout(new TimeValue(60, TimeUnit.SECONDS));
		searchRequest.source(searchSourceBuilder);
		SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT);
		List<String> list = new ArrayList<>();
		response.getHits().forEach(hit -> list.add(hit.getSourceAsString()));
		return list;
	}