1、JDBC
先说一下使用JDBC连接的方式,由于ES集群的版本号是7.6.2,所以必须使用JDBC的版本也是7.6.2,必须保持一致
项目管理:
maven
依赖包:
<dependency>
<groupId>org.elasticsearch.plugin</groupId>
<artifactId>x-pack-sql-jdbc</artifactId>
<version>7.6.2</version>
</dependency>
这个要说明下,在下载这个包时,使用正常的资源库,都没有找到,一直都没有成功,从各个云镜像也没有搜索到,最后发现官网上有说明,有2种方式下:
- 将下面配置添加到资源库中,如
<repositories>
<repository>
<id>elastic.co</id>
<url>https://artifacts.elastic.co/maven</url>
</repository>
</repositories>
这样才成功下载了
-
直接链接地址下载
链接:https://www.elastic.co/cn/downloads/past-releases#jdbc-client
源代码:
驱动程序主类是:org.elasticsearch.xpack.sql.jdbc.EsDriver。
注意,驱动程序实现了JDBC 4.0服务提供程序机制,这意味着只要它在类路径中可用,它就会自动注册。
请求参数:http://localhost:9200/test/sql?sqltext=select key,value,orderNum from ase_item_config limit 5
import io.swagger.v3.oas.annotations.tags.Tag;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@Tag(name = "SQL查询", description = "测试ES使用SQL查询")
@RestController
@RequestMapping("/test/sql")
public class TestSqlController {
private static final String JDBC_DRIVER = "org.elasticsearch.xpack.sql.jdbc.EsDriver";
private String elasticsearchAddress = "test.hostname.net:9200";
@GetMapping("sql/jdbc/text")
public void sqlQuery(@RequestParam(name = "sqlText") String sqlText) {
try {
Class.forName(JDBC_DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String address = "jdbc:es://http://" + elasticsearchAddress;
Long total = 0L;
List<Map<String, Object>> outputList = new ArrayList<>();
try (Connection connection = DriverManager.getConnection(address);
Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery(sqlText)) {
int columnCount = results.getMetaData().getColumnCount();
List<String> columnNames = new ArrayList<>();
for (int i = 1; i <= columnCount; i++) {
columnNames.add(results.getMetaData().getColumnName(i));
}
while (results.next()) {
Map<String, Object> tempItem = new HashMap<>();
for (String columnName : columnNames) {
tempItem.put(columnName, results.getObject(columnName));
}
outputList.add(tempItem);
total++;
}
System.out.println(outputList);
} catch (Exception e) {
e.printStackTrace();
}
}
}
输出结果作了下整理,如下:
[{orderNum=7, value=testval, key=testKey}]
ES JDBC到此结束了。
2、Java Low Level REST Client
下面看一下使用ES的Java Low Level REST Client,SQL查询的处理方式。
请求过程:
- 初始化RestClient
- 使用performRequest方式,
说明下,这里是使用的client为低级API,如果用的高级API Client,那调用方法(getLowLevelClient())转为低级client,请求可以通过调用performRequest或performRequestAsync来发送。下面使用的是同步操作方法performRequest()
源代码:
@GetMapping("client/test")
public void test() throws IOException {
RestClient restClient = RestClient.builder(
new HttpHost("test.hostname.net", 9200, "http")
).build();
String method = "GET";
String endPoint = "/_sql";
Request request = new Request(method, endPoint);
request.addParameter("format", "json");
request.setJsonEntity("{\"query\":\"select * from test LIMIT 5\"}");
Response response = restClient.performRequest(request);
System.out.println(EntityUtils.toString(response.getEntity()));
}
输出结果为:json字符串
到此2种方式整理完了,作个记录。