Create a Data Access Object for HubDB 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 HubDB integrates connectivity to live HubDB data in Java applications. By pairing these technologies, you gain simple, programmatic access to HubDB data. This article explains how to build a basic Data Access Object (DAO) and the accompanying code to read and write HubDB data.

Create a DAO for the HubDB NorthwindProducts 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 MyNorthwindProductsDAO {
 //insert new data into HubDB
 @SqlUpdate("INSERT INTO NorthwindProducts (Id, Name) values (:id, :name)")
 void insert(@Bind("id") String id, @Bind("name") String name);

 //request specific data from HubDB (String type is used for simplicity)
 @SqlQuery("SELECT Name FROM NorthwindProducts WHERE Id = :id")
 String findNameById(@Bind("id") String id);

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

Open a Connection to HubDB

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

There are two authentication methods available for connecting to HubDB data source: OAuth Authentication with a public HubSpot application and authentication with a Private application token.

Using a Custom OAuth App

AuthScheme must be set to "OAuth" in all OAuth flows. Be sure to review the Help documentation for the required connection properties for you specific authentication needs (desktop applications, web applications, and headless machines).

Follow the steps below to register an application and obtain the OAuth client credentials:

1. Log into your HubSpot app developer account.
   • Note that it must be an app developer account. Standard HubSpot accounts cannot create public apps.
2. On the developer account home page, click the Apps tab.
3. Click Create app.
4. On the App info tab, enter and optionally modify values that are displayed to users when they connect. These values include the public application name, application logo, and a description of the application.
5. On the Auth tab, supply a callback URL in the "Redirect URLs" box.
   • If you're creating a desktop application, set this to a locally accessible URL like http://localhost:33333.
   • If you are creating a Web application, set this to a trusted URL where you want users to be redirected to when they authorize your application.
6. Click Create App. HubSpot then generates the application, along with its associated credentials.
7. On the Auth tab, note the Client ID and Client secret. You will use these later to configure the driver.

8. Under Scopes, select any scopes you need for your application's intended functionality.

   A minimum of the following scopes is required to access tables:

   • hubdb
   • oauth
   • crm.objects.owners.read
9. Click Save changes.
10. Install the application into a production portal with access to the features that are required by the integration.
    • Under "Install URL (OAuth)", click Copy full URL to copy the installation URL for your application.
    • Navigate to the copied link in your browser. Select a standard account in which to install the application.
    • Click Connect app. You can close the resulting tab.

Using a Private App

To connect using a HubSpot private application token, set the AuthScheme property to "PrivateApp."

You can generate a private application token by following the steps below:

1. In your HubDB account, click the settings icon (the gear) in the main navigation bar.
2. In the left sidebar menu, navigate to Integrations > Private Apps.
3. Click Create private app.
4. On the Basic Info tab, configure the details of your application (name, logo, and description).
5. On the Scopes tab, select Read or Write for each scope you want your private application to be able to access.
6. A minimum of hubdb and crm.objects.owners.read is required to access tables.
7. After you are done configuring your application, click Create app in the top right.
8. Review the info about your application's access token, click Continue creating, and then Show token.
9. Click Copy to copy the private application token.

To connect, set PrivateAppToken to the private application token you retrieved.

Built-in Connection String Designer

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

java -jar cdata.jdbc.hubdb.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 HubDB will typically look like the following:

jdbc:hubdb:AuthScheme=OAuth;OAuthClientID=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH;

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:hubdb:AuthScheme=OAuth;OAuthClientID=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH;");
MyNorthwindProductsDAO dao = dbi.open(MyNorthwindProductsDAO.class);

//do stuff with the DAO

dao.close();

Read HubDB Data

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

//disply the result of our 'find' method
String name = dao.findNameById("1");
System.out.println(name);

Write HubDB Data

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

//add a new entry to the NorthwindProducts entity
dao.insert(newId, newName);

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