We have to extend the SpringBoot project to support SQL JDBC from Chapter 11, Elasticsearch from Java Programming. The project is located on the GitHub site (https://github.com/PacktPublishing/Mastering-Elasticsearch-7.0/tree/master/Chapter14/java_rest_client) under Chapter 14, Working with Elasticsearch SQL. We are not going to detail how the whole program works. Instead, we'll target just the workflow of the Elasticsearch SQL JDBC programming in SpringBoot.

Basically, there are four major steps based on our previous SpringBoot project framework. These are configuring the Maven dependency, configuring the SQL JDBC connection, performing the JDBC executeQuery request, and handling the ResultSet response. The following is a step-by-step guide to integrating Elasticsearch SQL JDBC:

1. Configuring the Maven dependency: In the pom.xml file, add the Elastic repository and the SQL JDBC dependency:

<repositories>
    ...
    <repository>
        <id>elastic.co</id>
            <url>https://artifacts.elastic.co/maven</url>
    </repository>
</repositories>

<dependencies>
    ....
    <dependency>
        <groupId>org.elasticsearch.plugin</groupId>
        <artifactId>x-pack-sql-jdbc</artifactId>
        <version>7.0.0</version>
    </dependency>
</dependencies>

2. Configuring the SQL JDBC client connection: Similar to what we did in the Java REST client initialization section of Chapter 12, Elasticsearch from Python Programming, in the com.example.client.restclient.configuration.SqlJdbcClientConfig.java file, we construct a org.elasticsearch.xpack.sql.jdbc.EsDataSource data source to create a JDBC connection. Our example is shown in the following code block:

package com.example.client.restclient.configuration;

import org.elasticsearch.xpack.sql.jdbc.EsDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
...

@Configuration
public class SqlJdbcClientConfig {
    ...
    @Value("${elasticsearch.host}")
    private String host;
  
    @Value("${elasticsearch.rest-client-port}")
    private int port;
  
    @Bean
    public Connection SqlJdbcClient() throws SQLException {
        EsDataSource dataSource = new EsDataSource();
        String url = "jdbc:es://" + host + ":" + port;
        dataSource.setUrl(url);
        Properties properties = new Properties();
        dataSource.setProperties(properties);
        return dataSource.getConnection();
    }
    ...
}

3. Sending out the JDBC request: In the com.example.client.restclient.service.impl.SqlJdbcClientServiceImpl.java file, we autowire in the Connection object from step 2. Then, we use the Connection object to create a JDBC SQL statement and call the executeQuery() method from the JDBC SQL statement to send out the request. The sample file is shown in the following code block:

package com.example.client.restclient.service.impl;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

@Service
public class SqlJdbcClientServiceImpl implements SqlJdbcClientService {
 
    @Autowired
    Connection connection;
    
    @Override
    public Map<String, Object> executeQuery(String sqlStatement) {
        List<Map<String, Object>> hitList = new ArrayList<Map<String, Object>>();
        Map<String, Object> result = new HashMap<String, Object>();
        Statement statement;
        int colCount=0, total=0;
 
       try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sqlStatement);
            ResultSetMetaData rsmd = resultSet.getMetaData();
            colCount = rsmd.getColumnCount();
            while (resultSet.next()) {
                total++;
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i=1; i<=colCount; i++) {
                    String columnName = rsmd.getColumnName(i);
                    map.put(columnName, resultSet.getObject(columnName));
                }
                hitList.add(map);
            }
        } catch (SQLException e) {
            ...
        }
        result.put("total", total);
        result.put("hits", hitList.toArray(new HashMap[hitList.size()]));
        return result;
    }
}

4. Handling the result of the response: As shown in the preceding code block, the response from the executeQuery() method is the JDBC ResultSet object. We first call the getMetaData() method from the ResultSet object to get the ResultSetMetaData object. For each record, we retrieve the column names from the ResultSetMetaData object and then get each column value by calling the getObject() method from the ResultSet object with the column name.