Create a Data Access Object for Elasticsearch Data using JDBI

JDBI is a SQL convenience library for Java that exposes two different style APIs, a fluent style and a SQL object style. The CData JDBC Driver for Elasticsearch integrates connectivity to live Elasticsearch data in Java applications. By pairing these technologies, you gain simple, programmatic access to Elasticsearch data. This article explains how to build a basic Data Access Object (DAO) and the accompanying code to read and write Elasticsearch data.

Getting Started

---

Create a DAO for the Elasticsearch Orders Entity

The interface below declares the desired behavior for the SQL object to create a single method for each SQL statement to be implemented.

public interface MyOrdersDAO {
 //insert new data into Elasticsearch
 @SqlUpdate("INSERT INTO Orders (ShipCity, Freight) values (:shipCity, :freight)")
 void insert(@Bind("shipCity") String shipCity, @Bind("freight") String freight);

 //request specific data from Elasticsearch (String type is used for simplicity)
 @SqlQuery("SELECT Freight FROM Orders WHERE ShipCity = :shipCity")
 String findFreightByShipCity(@Bind("shipCity") String shipCity);

 /*
 * close with no args is used to close the connection
 */
 void close();
}

Open a Connection to Elasticsearch

Collect the necessary connection properties and construct the appropriate JDBC URL for connecting to Elasticsearch.

Set the Server and Port connection properties to connect. To authenticate, set the User and Password properties, PKI (public key infrastructure) properties, or both. To use PKI, set the SSLClientCert, SSLClientCertType, SSLClientCertSubject, and SSLClientCertPassword properties.

The data provider uses X-Pack Security for TLS/SSL and authentication. To connect over TLS/SSL, prefix the Server value with 'https://'. Note: TLS/SSL and client authentication must be enabled on X-Pack to use PKI.

Once the data provider is connected, X-Pack will then perform user authentication and grant role permissions based on the realms you have configured.

Built-in Connection String Designer

For assistance in constructing the JDBC URL, use the connection string designer built into the Elasticsearch JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

java -jar cdata.jdbc.elasticsearch.jar

Fill in the connection properties and copy the connection string to the clipboard.

👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)

A connection string for Elasticsearch will typically look like the following:

jdbc:elasticsearch:Server=127.0.0.1;Port=9200;User=admin;Password=123456;

Use the configured JDBC URL to obtain an instance of the DAO interface. The particular method shown below will open a handle bound to the instance, so the instance needs to be closed explicitly to release the handle and the bound JDBC connection.

DBI dbi = new DBI("jdbc:elasticsearch:Server=127.0.0.1;Port=9200;User=admin;Password=123456;");
MyOrdersDAO dao = dbi.open(MyOrdersDAO.class);

//do stuff with the DAO

dao.close();

Read Elasticsearch Data

With the connection open to Elasticsearch, simply call the previously defined method to retrieve data from the Orders entity in Elasticsearch.

//disply the result of our 'find' method
String freight = dao.findFreightByShipCity("New York");
System.out.println(freight);

Write Elasticsearch Data

It is also simple to write data to Elasticsearch, using the previously defined method.

//add a new entry to the Orders entity
dao.insert(newShipCity, newFreight);

Since the JDBI library is able to work with JDBC connections, you can easily produce a SQL Object API for Elasticsearch by integrating with the CData JDBC Driver for Elasticsearch. Download a free trial and work with live Elasticsearch data in custom Java applications today.