| toolkit version | ES version |
|---|---|
| master | 2.4.4 |
| 2.4.4.2 | 2.4.4 |
| 1.x | 1.4.5 |
elasticsearch-query-tookit是一款基于SQL查询elasticsearch编程工具包，支持SQL解析生成DSL，支持JDBC驱动，支持和Spring、MyBatis集成，提供Java编程接口可基于此工具包二次开发
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();
注：其中routing by用于指定查询路由值，也可以不指定 生成的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
}
} ]
}
首先在Spring配置文件中增加如下代码
- 指定driverClassName：org.elasticsearch.jdbc.api.ElasticDriver
- 指定连接ES的连接串：jdbc:elastic:192.168.0.108:9300/product_cluster
- 创建一个SqlMapClient对象，并指定sqlMapConfig.xml路径
<bean id="elasticDataSource" class="org.es.jdbc.api.ElasticSingleConnectionDataSource" destroy-method="destroy">
<property name="driverClassName" value="org.es.jdbc.api.ElasticDriver" />
<property name="url" value="jdbc:elastic:192.168.0.108:9300/lu-search-cluster" />
<property name="suppressClose" value="true" />
</bean>
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="elasticDataSource" />
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>
<bean id="sqlMapClientTemplate" class="org.es.spring.ElasticSqlMapClientTemplate">
<property name="sqlMapClient" ref="sqlMapClient"/>
</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("sqlMapClientTemplate")
private ElasticSqlMapExecutor sqlMapClientTemplate;
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);
return sqlMapClientTemplate.queryForList("PRODUCT.getProductByCodeAndMatchWord", paramMap, Product.class);
}
}
编写测试方法
@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());
}
}
作者: [@陈楠][1] Email: [email protected]
<完>
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent