The best elasticsearch highlevel java rest api-----bboss
Elasticsearch 6.3.0 官方新增的SQL功能非常不错,本文以实际案例来介绍其使用方法:
1.代码中的sql检索
执行的结果在本文的最后给出。
2.sql转换为dsl
可以将sql转换为dsl语句
sql转换为dsl的结果:
3.配置文件管理sql并实现sql检索
定义一个包含sql的dsl配置文件,sql语句中包含一个channelId检索条件:
加载配置文件并实现sql检索操作 ,从外部传入检索的条件channelId
输出检索的结果为:
4.完整的demo
https://gitee.com/bbossgroups/eshelloword-booter
https://github.com/bbossgroups/eshelloword-booter
5.开发交流
elasticsearch sql官方文档:
https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html
elasticsearch技术交流群:166471282
elasticsearch微信公众号:
![img_a21db47cf20ac4820026d60bcb2b9470.jpe](https://yqfile.alicdn.com/img_a21db47cf20ac4820026d60bcb2b9470.jpeg?x-oss-process=image/resize,w_1400/format,webp)
Elasticsearch 6.3.0 官方新增的SQL功能非常不错,本文以实际案例来介绍其使用方法:
- 通过sql实现检索功能(代码中直接操作sql,从配置中加载sql)
- 将sql转换为dsl功能
- 准备工作:集成Elasticsearch Restful API
1.代码中的sql检索
@Test public void testQuery(){ ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil(); String json = clientUtil.executeHttp("/_xpack/sql?format=txt", "{\"query\": \"SELECT * FROM dbclobdemo\"}", ClientInterface.HTTP_POST ); System.out.println(json); json = clientUtil.executeHttp("/_xpack/sql?format=json", "{\"query\": \"SELECT * FROM dbclobdemo\"}", ClientInterface.HTTP_POST ); System.out.println(json); }
执行的结果在本文的最后给出。
2.sql转换为dsl
可以将sql转换为dsl语句
public void testTranslate(){ ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil(); String json = clientUtil.executeHttp("/_xpack/sql/translate", "{\"query\": \"SELECT * FROM dbclobdemo\"}", ClientInterface.HTTP_POST ); System.out.println(json); }
sql转换为dsl的结果:
{ "size": 1000, "_source": { "includes": [ "author", "content", "docClass", "docabstract", "keywords", "mediapath", "newpicPath", "parentDetailTpl", "picPath", "publishfilename", "secondtitle", "subtitle", "title", "titlecolor" ], "excludes": [] }, "docvalue_fields": [ "auditflag", "channelId", "count", "createtime", "createuser", "detailtemplateId", "docLevel", "docsourceId", "doctype", "documentId", "docwtime", "flowId", "isdeleted", "isnew", "ordertime", "publishtime", "seq", "status", "version" ], "sort": [ { "_doc": { "order": "asc" } } ] }
3.配置文件管理sql并实现sql检索
定义一个包含sql的dsl配置文件,sql语句中包含一个channelId检索条件:
<properties> <!-- sql query --> <property name="sqlQuery"> <![CDATA[ {"query": "SELECT * FROM dbclobdemo where channelId=#[channelId]"} ]]> </property> </properties>
加载配置文件并实现sql检索操作 ,从外部传入检索的条件channelId
public void testSQLQueryFromDSL(){ ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一个加载sql配置文件的es客户端接口 //设置sql查询的参数 Map params = new HashMap(); params.put("channelId",1); String json = clientUtil.executeHttp("/_xpack/sql","sqlQuery",params, ClientInterface.HTTP_POST ); System.out.println(json);//打印检索结果 }
输出检索的结果为:
{ "columns": [ { "name": "auditflag", "type": "long" }, { "name": "author", "type": "text" }, { "name": "channelId", "type": "long" }, { "name": "content", "type": "text" }, { "name": "count", "type": "long" }, { "name": "createtime", "type": "date" }, { "name": "createuser", "type": "long" }, { "name": "detailtemplateId", "type": "long" }, { "name": "docClass", "type": "text" }, { "name": "docLevel", "type": "long" }, { "name": "docabstract", "type": "text" }, { "name": "docsourceId", "type": "long" }, { "name": "doctype", "type": "long" }, { "name": "documentId", "type": "long" }, { "name": "docwtime", "type": "date" }, { "name": "flowId", "type": "long" }, { "name": "isdeleted", "type": "long" }, { "name": "isnew", "type": "long" }, { "name": "keywords", "type": "text" }, { "name": "mediapath", "type": "text" }, { "name": "newpicPath", "type": "text" }, { "name": "ordertime", "type": "date" }, { "name": "parentDetailTpl", "type": "text" }, { "name": "picPath", "type": "text" }, { "name": "publishfilename", "type": "text" }, { "name": "publishtime", "type": "date" }, { "name": "secondtitle", "type": "text" }, { "name": "seq", "type": "long" }, { "name": "status", "type": "long" }, { "name": "subtitle", "type": "text" }, { "name": "title", "type": "text" }, { "name": "titlecolor", "type": "text" }, { "name": "version", "type": "long" } ], "rows": [ [ 0, "不详", 1, "asdfasdfasdfasdfsdf<img name=\"imgs\" src=\"../gencode7.png\" _ewebeditor_pa_src=\"http%3A%2F%2Flocalhost%2Fcms%2FsiteResource%2Ftest%2F_webprj%2Fgencode7.png\"><br>\r\nasdfasdf<img name=\"imgs\" src=\"content_files/20180505101457109.png\" _ewebeditor_pa_src=\"http%3A%2F%2Flocalhost%2Fcms%2FsiteResource%2Ftest%2F_webprj%2Fnews%2Fcontent_files%2F20180505101457109.png\"><br>\r\n<br>", 0, "2018-04-12T14:16:02.000Z", 1, 1, "普通分类", 1, "无asdfasdf", 1, 0, 1, "2018-05-06T03:30:04.000Z", 2, 0, 0, "news", "uploadfiles/201803/gencode4.png", "", "2018-04-12T14:06:45.000Z", "1", "uploadfiles/201803/gencode1.png", "asdf.html", "2018-04-14T14:36:12.000Z", "", 0, 11, "asdf", "adsf", "#000000", 1 ] ] }
4.完整的demo
https://gitee.com/bbossgroups/eshelloword-booter
https://github.com/bbossgroups/eshelloword-booter
5.开发交流
elasticsearch sql官方文档:
https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html
elasticsearch技术交流群:166471282
elasticsearch微信公众号:
![img_a21db47cf20ac4820026d60bcb2b9470.jpe](https://yqfile.alicdn.com/img_a21db47cf20ac4820026d60bcb2b9470.jpeg?x-oss-process=image/resize,w_1400/format,webp)