Create a Data Access Object for JDBC-ODBC Bridge Data using JDBI



A brief overview of creating a SQL Object API for JDBC-ODBC Bridge data in 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 walks through building 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.

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.

Ready to get started?

Download a free trial of the JDBC-ODBC Bridge to get started:

 Download Now

Learn more:

ODBC Connectivity from Java Icon JDBC-ODBC Bridge Driver

The JDBC-ODBC Bridge provides JDBC access from any Java App to ODBC data sources on Windows, Linux and Mac. Whether your organization uses Java-based tools for reporting and analytics, or builds custom Java solutions, the CData JDBC-ODBC Bridge provides an easy way to connect with any ODBC data source.