JDBC连接ES查询

前言

ES也支持使用JDBC协议去 查询索引和文档内容，本文给出样例

---

一、maven依赖

        <!-- 数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>
        <dependency>
            <groupId>org.elasticsearch.plugin</groupId>
            <artifactId>x-pack-sql-jdbc</artifactId>
            <version>7.13.2</version>
        </dependency>
        <!-- 数据库驱动结束 -->

注意：其中x-pack-sql-jdbc 的依赖版本要和你安装的ES版本保持一致

二、实现代码

代码如下（示例）：

获取连接、执行SQL、查询所有库、表、字段元数据

/**
 * @author 林志鹏
 * @version 1.0.0
 * @date 2021/6/27 11:22
 */
@Slf4j
public class ESDataSource {

    public static void main(String[] args) throws Exception {

        String url = "jdbc:es://http://127.0.0.1:9200";
        Connection connection = getConnection(url);
//        showDatabases(connection);
//        showTables(connection);
        showColumns(connection);
//        testExcuteSQL(connection);
    }

    /**
     * 测试执行SQL.
     *
     * @param connection
     * @throws Exception
     */
    private static void testExcuteSQL(Connection connection) throws Exception{
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * from test_index");
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Object> resultList = handleResultSet(resultSet);
        log.info("结果：{}", resultList);
    }

    private static List<Object> handleResultSet(ResultSet resultSet) throws SQLException {
        List<Object> result = new ArrayList<>();
        while (resultSet.next()) {
            Map<String, Object> map = new LinkedHashMap<>();
            int total = resultSet.getMetaData().getColumnCount();
            for (int i = 1; i <= total; i++) {
                map.put(resultSet.getMetaData().getColumnName(i), null == resultSet.getObject(i) ? null : resultSet.getObject(i).toString());
            }
            result.add(map);
        }
        return result;
    }

    /**
     * 查看所有数据库.
     *
     * @param connection
     * @throws Exception
     */
    public static void showDatabases(Connection connection) throws Exception{
        ResultSet schemas = connection.getMetaData().getCatalogs();
        while (schemas.next()){
            System.out.println(schemas.getObject("TABLE_CAT"));
        }
    }

    /**
     * 查看数据库所有表.
     *
     * @param connection
     * @throws Exception
     */
    public static void showTables(Connection connection) throws Exception{
        ResultSet tables = connection.getMetaData().getTables(null, null, null, null);
        while (tables.next()) {
//            System.out.println(tables.getString("TABLE_NAME"));
            String TABLE_CAT = tables.getString("TABLE_CAT");
            String TABLE_SCHEM = tables.getString("TABLE_SCHEM");
            String TABLE_NAME = tables.getString("TABLE_NAME");
            String TABLE_TYPE = tables.getString("TABLE_TYPE");
            String REMARKS = tables.getString("REMARKS");
            log.info("表类别：{}、表模式：{}、表名称：{}、表类型：{}、表描述：{}", TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS);
        }
    }

    /**
     * 查看表所有字段.
     *
     * @param connection
     * @throws Exception
     */
    public static void showColumns(Connection connection) throws Exception{
        ResultSet tables = connection.getMetaData().getColumns(null, null, "test_index", null);
        while (tables.next()) {
            String TABLE_CAT = tables.getString("TABLE_CAT");
            String TABLE_SCHEM = tables.getString("TABLE_SCHEM");
            String TABLE_NAME = tables.getString("TABLE_NAME");
            String columnName = tables.getString("COLUMN_NAME");
            String typeName = tables.getString("TYPE_NAME");
            String columnSize = tables.getString("COLUMN_SIZE");
            String REMARKS = tables.getString("REMARKS");
            log.info("表类别：{}、表模式：{}、表名称：{}、字段名称：{}、字段类型：{}、字段大小：{}、字段描述：{}",
                    TABLE_CAT, TABLE_SCHEM, TABLE_NAME, columnName, typeName, columnSize, REMARKS);
        }
    }

    /**
     * 获取数据源.
     *
     * @param url
     * @return
     */
    private static DataSource getESDataSource(String url){
        EsDataSource esDataSource = new EsDataSource();
        esDataSource.setUrl(url);
        return esDataSource;
    }

    /**
     * 获取连接connection.
     *
     * @param url
     * @return
     */
    private static Connection getConnection(String url){
        DataSource esDataSource = getESDataSource(url);
        Connection connection = null;
        try {
            connection = esDataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }
}

注意： 如果JDBC报错，可能是ES支持SQL需要铂金版本，要么试用要么破解，详情自行百度