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

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

 //request specific data from JDBC-ODBC Bridge (String type is used for simplicity)
 @SqlQuery("SELECT Name FROM Account 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 JDBC-ODBC Bridge

Collect the necessary connection properties and construct the appropriate JDBC URL for connecting to JDBC-ODBC Bridge.

To connect to an ODBC data source, specify either the DSN (data source name) or specify an ODBC connection string: Set Driver and the connection properties for your ODBC driver.

Built-in Connection String Designer

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

java -jar cdata.jdbc.jdbcodbc.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 JDBC-ODBC Bridge will typically look like the following:

jdbc:jdbcodbc:Driver={ODBC_Driver_Name};Driver_Property1=Driver_Value1;Driver_Property2=Driver_Value2;...

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:jdbcodbc:Driver={ODBC_Driver_Name};Driver_Property1=Driver_Value1;Driver_Property2=Driver_Value2;...");
MyAccountDAO dao = dbi.open(MyAccountDAO.class);

//do stuff with the DAO

dao.close();

Read JDBC-ODBC Bridge Data

With the connection open to JDBC-ODBC Bridge, simply call the previously defined method to retrieve data from the Account entity in JDBC-ODBC Bridge.

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

Write JDBC-ODBC Bridge Data

It is also simple to write data to JDBC-ODBC Bridge, using the previously defined method.

//add a new entry to the Account 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 JDBC-ODBC Bridge by integrating with the CData JDBC Driver for JDBC-ODBC Bridge. Download a free trial and work with live JDBC-ODBC Bridge data in custom Java applications today.