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

Create a DAO for the WooCommerce 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 WooCommerce
 @SqlUpdate("INSERT INTO Orders (ParentId, Total) values (:parentId, :total)")
 void insert(@Bind("parentId") String parentId, @Bind("total") String total);

 //request specific data from WooCommerce (String type is used for simplicity)
 @SqlQuery("SELECT Total FROM Orders WHERE ParentId = :parentId")
 String findTotalByParentId(@Bind("parentId") String parentId);

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

Open a Connection to WooCommerce

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

WooCommerce supports the following authentication methods: one-legged OAuth1.0 Authentication and standard OAuth2.0 Authentication.

Connecting using one-legged OAuth 1.0 Authentication

Specify the following properties (NOTE: the below credentials are generated from WooCommerce settings page and should not be confused with the credentials generated by using WordPress OAuth2.0 plugin):

• ConsumerKey
• ConsumerSecret

Connecting using WordPress OAuth 2.0 Authentication

After having configured the

• OAuthClientId
• OAuthClientSecret
• CallbackURL
• InitiateOAuth - Set this to either GETANDREFRESH or REFRESH

In either case, set the Url property to the URL of the WooCommerce instance.

Built-in Connection String Designer

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

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

jdbc:woocommerce:Url=https://example.com/; ConsumerKey=ck_ec52c76185c088ecaa3145287c8acba55a6f59ad; ConsumerSecret=cs_9fde14bf57126156701a7563fc87575713c355e5;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:woocommerce:Url=https://example.com/; ConsumerKey=ck_ec52c76185c088ecaa3145287c8acba55a6f59ad; ConsumerSecret=cs_9fde14bf57126156701a7563fc87575713c355e5;InitiateOAuth=GETANDREFRESH;");
MyOrdersDAO dao = dbi.open(MyOrdersDAO.class);

//do stuff with the DAO

dao.close();

Read WooCommerce Data

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

//disply the result of our 'find' method
String total = dao.findTotalByParentId("3");
System.out.println(total);

Write WooCommerce Data

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

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

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