jdbc

jdbc

elasticsearch-query-tookit一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成

Elasticsearchchennanlcy 发表了文章 • 0 个评论 • 58 次浏览 • 3 天前 • 来自相关话题

`elasticsearch-query-tookit`是一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成,提供Java编程接口可基于此工具包二次开发
 
只是重新造了个轮子,有兴趣的同学可以相互交流,QQ: 465360798
 
项目地址:https://github.com/gitchennan/ ... olkit
 
一、SQL解析生成DSL使用示例
SQL语法帮助手册戳这里: https://github.com/gitchennan/ ... p-doc
 
$(document).ready(function() {$('pre code').each(function(i, block) { hljs.highlightBlock( block); }); });String sql = "select * from index.order where status='SUCCESS' and price > 100 order by nvl(pride, 0) asc routing by 'JD' limit 0, 20";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();
//解析SQL
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql);
//生成DSL(可用于rest api调用)
String dsl = parseResult.toDsl();

//toRequest方法接收一个clinet对象参数
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);
//执行查询
SearchResponse response = searchReq.execute().actionGet();生成的DSL如下:
{
"from" : 0,
"size" : 20,
"query" : {
"bool" : {
"filter" : {
"bool" : {
"must" : [ {
"term" : {
"status" : "SUCCESS"
}
}, {
"range" : {
"price" : {
"from" : 100,
"to" : null,
"include_lower" : false,
"include_upper" : true
}
}
} ]
}
}
}
},
"sort" : [ {
"pride" : {
"order" : "asc",
"missing" : 0
}
} ]
}二、集成MyBatis、Spring
 
首先在Spring配置文件中增加如下代码
1. 指定driverClassName:org.elasticsearch.jdbc.api.ElasticDriver
2. 指定连接ES的连接串:jdbc:elastic:192.168.0.109:9300/product_cluster
3. 创建一个SqlMapClient对象,并指定sqlMapConfig.xml路径
 
<bean id="elasticDataSource" class="org.elasticsearch.jdbc.api.ElasticSingleConnectionDataSource" destroy-method="destroy">
<property name="driverClassName" value="org.elasticsearch.jdbc.api.ElasticDriver" />
<property name="url" value="jdbc:elastic:192.168.0.109:9300/product_cluster" />
</bean>

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="elasticDataSource" />
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>sqlMapConfig.xml文件内容如下:
<sqlMapConfig>
<settings
cacheModelsEnabled="true"
lazyLoadingEnabled="true"
enhancementEnabled="true"
maxSessions="64"
maxTransactions="20"
maxRequests="128"
useStatementNamespaces="true"/>

<sqlMap resource="sqlmap/PRODUCT.xml"/>

</sqlMapConfig>PRODUCT.xml文件中声明select sql语句
<sqlMap namespace="PRODUCT">
<select id="getProductByCodeAndMatchWord" parameterClass="java.util.Map" resultClass="java.lang.String">
SELECT *
FROM index.product
QUERY match(productName, #matchWord#) or prefix(productName, #prefixWord#, 'boost:2.0f')
WHERE productCode = #productCode#
AND advicePrice > #advicePrice#
AND $$buyers.buyerName IN ('china', 'usa')
ROUTING BY #routingVal#
</select>
</sqlMap>编写对应DAO代码:
@Repository
public class ProductDao {
@Autowired
@Qualifier("sqlMapClient")
private SqlMapClient sqlMapClient;


public List<Product> getProductByCodeAndMatchWord(String matchWord, String productCode) throws SQLException {
Map<String, Object> paramMap = Maps.newHashMap();
paramMap.put("productCode", productCode);
paramMap.put("advicePrice", 1000);
paramMap.put("routingVal", "A");
paramMap.put("matchWord", matchWord);
paramMap.put("prefixWord", matchWord);
String responseGson = (String) sqlMapClient.queryForObject("PRODUCT.getProductByCodeAndMatchWord", paramMap);

//反序列化查询结果
JdbcSearchResponseResolver responseResolver = new JdbcSearchResponseResolver(responseGson);
JdbcSearchResponse<Product> searchResponse = responseResolver.resolveSearchResponse(Product.class);

return searchResponse.getDocList();

}
}编写测试方法@Test
public void testProductQuery() throws Exception {
BeanFactory factory = new ClassPathXmlApplicationContext("application-context.xml");
ProductDao productDao = factory.getBean(ProductDao.class);

List<Product> productList = productDao.getProductByCodeAndMatchWord("iphone 6s", "IP_6S");
for (Product product : productList) {
System.out.println(product.getProductName());
}
}
 
  查看全部
`elasticsearch-query-tookit`是一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成,提供Java编程接口可基于此工具包二次开发
 
只是重新造了个轮子,有兴趣的同学可以相互交流,QQ: 465360798
 
项目地址:https://github.com/gitchennan/ ... olkit
 
一、SQL解析生成DSL使用示例
SQL语法帮助手册戳这里: https://github.com/gitchennan/ ... p-doc
 
String sql = "select * from index.order where status='SUCCESS' and price > 100 order by nvl(pride, 0) asc routing by 'JD' limit 0, 20";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();
//解析SQL
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql);
//生成DSL(可用于rest api调用)
String dsl = parseResult.toDsl();

//toRequest方法接收一个clinet对象参数
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);
//执行查询
SearchResponse response = searchReq.execute().actionGet();
生成的DSL如下:
{
"from" : 0,
"size" : 20,
"query" : {
"bool" : {
"filter" : {
"bool" : {
"must" : [ {
"term" : {
"status" : "SUCCESS"
}
}, {
"range" : {
"price" : {
"from" : 100,
"to" : null,
"include_lower" : false,
"include_upper" : true
}
}
} ]
}
}
}
},
"sort" : [ {
"pride" : {
"order" : "asc",
"missing" : 0
}
} ]
}
二、集成MyBatis、Spring
 
首先在Spring配置文件中增加如下代码
1. 指定driverClassName:org.elasticsearch.jdbc.api.ElasticDriver
2. 指定连接ES的连接串:jdbc:elastic:192.168.0.109:9300/product_cluster
3. 创建一个SqlMapClient对象,并指定sqlMapConfig.xml路径
 
<bean id="elasticDataSource" class="org.elasticsearch.jdbc.api.ElasticSingleConnectionDataSource" destroy-method="destroy">
<property name="driverClassName" value="org.elasticsearch.jdbc.api.ElasticDriver" />
<property name="url" value="jdbc:elastic:192.168.0.109:9300/product_cluster" />
</bean>

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="elasticDataSource" />
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>
sqlMapConfig.xml文件内容如下:
<sqlMapConfig>
<settings
cacheModelsEnabled="true"
lazyLoadingEnabled="true"
enhancementEnabled="true"
maxSessions="64"
maxTransactions="20"
maxRequests="128"
useStatementNamespaces="true"/>

<sqlMap resource="sqlmap/PRODUCT.xml"/>

</sqlMapConfig>
PRODUCT.xml文件中声明select sql语句
<sqlMap namespace="PRODUCT">
<select id="getProductByCodeAndMatchWord" parameterClass="java.util.Map" resultClass="java.lang.String">
SELECT *
FROM index.product
QUERY match(productName, #matchWord#) or prefix(productName, #prefixWord#, 'boost:2.0f')
WHERE productCode = #productCode#
AND advicePrice > #advicePrice#
AND $$buyers.buyerName IN ('china', 'usa')
ROUTING BY #routingVal#
</select>
</sqlMap>
编写对应DAO代码:
@Repository
public class ProductDao {
@Autowired
@Qualifier("sqlMapClient")
private SqlMapClient sqlMapClient;


public List<Product> getProductByCodeAndMatchWord(String matchWord, String productCode) throws SQLException {
Map<String, Object> paramMap = Maps.newHashMap();
paramMap.put("productCode", productCode);
paramMap.put("advicePrice", 1000);
paramMap.put("routingVal", "A");
paramMap.put("matchWord", matchWord);
paramMap.put("prefixWord", matchWord);
String responseGson = (String) sqlMapClient.queryForObject("PRODUCT.getProductByCodeAndMatchWord", paramMap);

//反序列化查询结果
JdbcSearchResponseResolver responseResolver = new JdbcSearchResponseResolver(responseGson);
JdbcSearchResponse<Product> searchResponse = responseResolver.resolveSearchResponse(Product.class);

return searchResponse.getDocList();

}
}
编写测试方法
@Test
public void testProductQuery() throws Exception {
BeanFactory factory = new ClassPathXmlApplicationContext("application-context.xml");
ProductDao productDao = factory.getBean(ProductDao.class);

List<Product> productList = productDao.getProductByCodeAndMatchWord("iphone 6s", "IP_6S");
for (Product product : productList) {
System.out.println(product.getProductName());
}
}

 
 

elasticsearch-jdbc导入数据时下面这个错误,请问是什么原因呢?

ElasticsearchJennyJoker 回复了问题 • 2 人关注 • 1 个回复 • 208 次浏览 • 2017-03-02 09:35 • 来自相关话题

elasticsearch-jdbc导入数据时,日志中大量的出现这样的记录。

ElasticsearchJennyJoker 回复了问题 • 3 人关注 • 2 个回复 • 288 次浏览 • 2017-03-01 18:25 • 来自相关话题

使用logstash input jdbc插件,从mysql导出数据到elasticsearch,发现乱码

LogstashViento 回复了问题 • 2 人关注 • 2 个回复 • 320 次浏览 • 2017-01-20 12:23 • 来自相关话题

Elasticsearch jdbc从mysql导入数据,到了一半shell脚本中断。

ElasticsearchXargin 回复了问题 • 2 人关注 • 1 个回复 • 220 次浏览 • 2017-01-10 22:01 • 来自相关话题

elasticsearch-jdbc从mysql导入的读取顺序是??

回复

Elasticsearchlinfree 发起了问题 • 1 人关注 • 0 个回复 • 158 次浏览 • 2017-01-10 17:48 • 来自相关话题

JDBC导入数据,有字段内容有HTML标签,会影响搜索的效率

Elasticsearchstrglee 回复了问题 • 3 人关注 • 2 个回复 • 281 次浏览 • 2016-12-14 21:35 • 来自相关话题

elasticsearch-river-jdbc mysql数据删除与更新问题

Elasticsearchzjxhsun 回复了问题 • 6 人关注 • 3 个回复 • 2575 次浏览 • 2015-06-17 16:45 • 来自相关话题

elasticsearch-jdbc导入数据时下面这个错误,请问是什么原因呢?

回复

ElasticsearchJennyJoker 回复了问题 • 2 人关注 • 1 个回复 • 208 次浏览 • 2017-03-02 09:35 • 来自相关话题

elasticsearch-jdbc导入数据时,日志中大量的出现这样的记录。

回复

ElasticsearchJennyJoker 回复了问题 • 3 人关注 • 2 个回复 • 288 次浏览 • 2017-03-01 18:25 • 来自相关话题

使用logstash input jdbc插件,从mysql导出数据到elasticsearch,发现乱码

回复

LogstashViento 回复了问题 • 2 人关注 • 2 个回复 • 320 次浏览 • 2017-01-20 12:23 • 来自相关话题

Elasticsearch jdbc从mysql导入数据,到了一半shell脚本中断。

回复

ElasticsearchXargin 回复了问题 • 2 人关注 • 1 个回复 • 220 次浏览 • 2017-01-10 22:01 • 来自相关话题

elasticsearch-jdbc从mysql导入的读取顺序是??

回复

Elasticsearchlinfree 发起了问题 • 1 人关注 • 0 个回复 • 158 次浏览 • 2017-01-10 17:48 • 来自相关话题

JDBC导入数据,有字段内容有HTML标签,会影响搜索的效率

回复

Elasticsearchstrglee 回复了问题 • 3 人关注 • 2 个回复 • 281 次浏览 • 2016-12-14 21:35 • 来自相关话题

elasticsearch-river-jdbc mysql数据删除与更新问题

回复

Elasticsearchzjxhsun 回复了问题 • 6 人关注 • 3 个回复 • 2575 次浏览 • 2015-06-17 16:45 • 来自相关话题

elasticsearch-query-tookit一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成

Elasticsearchchennanlcy 发表了文章 • 0 个评论 • 58 次浏览 • 3 天前 • 来自相关话题

`elasticsearch-query-tookit`是一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成,提供Java编程接口可基于此工具包二次开发
 
只是重新造了个轮子,有兴趣的同学可以相互交流,QQ: 465360798
 
项目地址:https://github.com/gitchennan/ ... olkit
 
一、SQL解析生成DSL使用示例
SQL语法帮助手册戳这里: https://github.com/gitchennan/ ... p-doc
 
String sql = "select * from index.order where status='SUCCESS' and price > 100 order by nvl(pride, 0) asc routing by 'JD' limit 0, 20";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();
//解析SQL
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql);
//生成DSL(可用于rest api调用)
String dsl = parseResult.toDsl();

//toRequest方法接收一个clinet对象参数
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);
//执行查询
SearchResponse response = searchReq.execute().actionGet();生成的DSL如下:
{
"from" : 0,
"size" : 20,
"query" : {
"bool" : {
"filter" : {
"bool" : {
"must" : [ {
"term" : {
"status" : "SUCCESS"
}
}, {
"range" : {
"price" : {
"from" : 100,
"to" : null,
"include_lower" : false,
"include_upper" : true
}
}
} ]
}
}
}
},
"sort" : [ {
"pride" : {
"order" : "asc",
"missing" : 0
}
} ]
}二、集成MyBatis、Spring
 
首先在Spring配置文件中增加如下代码
1. 指定driverClassName:org.elasticsearch.jdbc.api.ElasticDriver
2. 指定连接ES的连接串:jdbc:elastic:192.168.0.109:9300/product_cluster
3. 创建一个SqlMapClient对象,并指定sqlMapConfig.xml路径
 
<bean id="elasticDataSource" class="org.elasticsearch.jdbc.api.ElasticSingleConnectionDataSource" destroy-method="destroy">
<property name="driverClassName" value="org.elasticsearch.jdbc.api.ElasticDriver" />
<property name="url" value="jdbc:elastic:192.168.0.109:9300/product_cluster" />
</bean>

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="elasticDataSource" />
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>sqlMapConfig.xml文件内容如下:
<sqlMapConfig>
<settings
cacheModelsEnabled="true"
lazyLoadingEnabled="true"
enhancementEnabled="true"
maxSessions="64"
maxTransactions="20"
maxRequests="128"
useStatementNamespaces="true"/>

<sqlMap resource="sqlmap/PRODUCT.xml"/>

</sqlMapConfig>PRODUCT.xml文件中声明select sql语句
<sqlMap namespace="PRODUCT">
<select id="getProductByCodeAndMatchWord" parameterClass="java.util.Map" resultClass="java.lang.String">
SELECT *
FROM index.product
QUERY match(productName, #matchWord#) or prefix(productName, #prefixWord#, 'boost:2.0f')
WHERE productCode = #productCode#
AND advicePrice > #advicePrice#
AND $$buyers.buyerName IN ('china', 'usa')
ROUTING BY #routingVal#
</select>
</sqlMap>编写对应DAO代码:
@Repository
public class ProductDao {
@Autowired
@Qualifier("sqlMapClient")
private SqlMapClient sqlMapClient;


public List<Product> getProductByCodeAndMatchWord(String matchWord, String productCode) throws SQLException {
Map<String, Object> paramMap = Maps.newHashMap();
paramMap.put("productCode", productCode);
paramMap.put("advicePrice", 1000);
paramMap.put("routingVal", "A");
paramMap.put("matchWord", matchWord);
paramMap.put("prefixWord", matchWord);
String responseGson = (String) sqlMapClient.queryForObject("PRODUCT.getProductByCodeAndMatchWord", paramMap);

//反序列化查询结果
JdbcSearchResponseResolver responseResolver = new JdbcSearchResponseResolver(responseGson);
JdbcSearchResponse<Product> searchResponse = responseResolver.resolveSearchResponse(Product.class);

return searchResponse.getDocList();

}
}编写测试方法@Test
public void testProductQuery() throws Exception {
BeanFactory factory = new ClassPathXmlApplicationContext("application-context.xml");
ProductDao productDao = factory.getBean(ProductDao.class);

List<Product> productList = productDao.getProductByCodeAndMatchWord("iphone 6s", "IP_6S");
for (Product product : productList) {
System.out.println(product.getProductName());
}
}
 
  查看全部
`elasticsearch-query-tookit`是一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成,提供Java编程接口可基于此工具包二次开发
 
只是重新造了个轮子,有兴趣的同学可以相互交流,QQ: 465360798
 
项目地址:https://github.com/gitchennan/ ... olkit
 
一、SQL解析生成DSL使用示例
SQL语法帮助手册戳这里: https://github.com/gitchennan/ ... p-doc
 
String sql = "select * from index.order where status='SUCCESS' and price > 100 order by nvl(pride, 0) asc routing by 'JD' limit 0, 20";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();
//解析SQL
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql);
//生成DSL(可用于rest api调用)
String dsl = parseResult.toDsl();

//toRequest方法接收一个clinet对象参数
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);
//执行查询
SearchResponse response = searchReq.execute().actionGet();
生成的DSL如下:
{
"from" : 0,
"size" : 20,
"query" : {
"bool" : {
"filter" : {
"bool" : {
"must" : [ {
"term" : {
"status" : "SUCCESS"
}
}, {
"range" : {
"price" : {
"from" : 100,
"to" : null,
"include_lower" : false,
"include_upper" : true
}
}
} ]
}
}
}
},
"sort" : [ {
"pride" : {
"order" : "asc",
"missing" : 0
}
} ]
}
二、集成MyBatis、Spring
 
首先在Spring配置文件中增加如下代码
1. 指定driverClassName:org.elasticsearch.jdbc.api.ElasticDriver
2. 指定连接ES的连接串:jdbc:elastic:192.168.0.109:9300/product_cluster
3. 创建一个SqlMapClient对象,并指定sqlMapConfig.xml路径
 
<bean id="elasticDataSource" class="org.elasticsearch.jdbc.api.ElasticSingleConnectionDataSource" destroy-method="destroy">
<property name="driverClassName" value="org.elasticsearch.jdbc.api.ElasticDriver" />
<property name="url" value="jdbc:elastic:192.168.0.109:9300/product_cluster" />
</bean>

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="elasticDataSource" />
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>
sqlMapConfig.xml文件内容如下:
<sqlMapConfig>
<settings
cacheModelsEnabled="true"
lazyLoadingEnabled="true"
enhancementEnabled="true"
maxSessions="64"
maxTransactions="20"
maxRequests="128"
useStatementNamespaces="true"/>

<sqlMap resource="sqlmap/PRODUCT.xml"/>

</sqlMapConfig>
PRODUCT.xml文件中声明select sql语句
<sqlMap namespace="PRODUCT">
<select id="getProductByCodeAndMatchWord" parameterClass="java.util.Map" resultClass="java.lang.String">
SELECT *
FROM index.product
QUERY match(productName, #matchWord#) or prefix(productName, #prefixWord#, 'boost:2.0f')
WHERE productCode = #productCode#
AND advicePrice > #advicePrice#
AND $$buyers.buyerName IN ('china', 'usa')
ROUTING BY #routingVal#
</select>
</sqlMap>
编写对应DAO代码:
@Repository
public class ProductDao {
@Autowired
@Qualifier("sqlMapClient")
private SqlMapClient sqlMapClient;


public List<Product> getProductByCodeAndMatchWord(String matchWord, String productCode) throws SQLException {
Map<String, Object> paramMap = Maps.newHashMap();
paramMap.put("productCode", productCode);
paramMap.put("advicePrice", 1000);
paramMap.put("routingVal", "A");
paramMap.put("matchWord", matchWord);
paramMap.put("prefixWord", matchWord);
String responseGson = (String) sqlMapClient.queryForObject("PRODUCT.getProductByCodeAndMatchWord", paramMap);

//反序列化查询结果
JdbcSearchResponseResolver responseResolver = new JdbcSearchResponseResolver(responseGson);
JdbcSearchResponse<Product> searchResponse = responseResolver.resolveSearchResponse(Product.class);

return searchResponse.getDocList();

}
}
编写测试方法
@Test
public void testProductQuery() throws Exception {
BeanFactory factory = new ClassPathXmlApplicationContext("application-context.xml");
ProductDao productDao = factory.getBean(ProductDao.class);

List<Product> productList = productDao.getProductByCodeAndMatchWord("iphone 6s", "IP_6S");
for (Product product : productList) {
System.out.println(product.getProductName());
}
}