Ready to get started?

Learn more about the CData JDBC Driver for MailChimp or download a free trial:

Download Now

Create a Data Access Object for MailChimp Data using JDBI

A brief overview of creating a SQL Object API for MailChimp 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 MailChimp integrates connectivity to live MailChimp data in Java applications. By pairing these technologies, you gain simple, programmatic access to MailChimp data. This article walks through building a basic Data Access Object (DAO) and the accompanying code to read and write MailChimp data.

Create a DAO for the MailChimp Lists 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 MyListsDAO { //insert new data into MailChimp @SqlUpdate("INSERT INTO Lists (Contact_Country, Stats_AvgSubRate) values (:contact_Country, :stats_AvgSubRate)") void insert(@Bind("contact_Country") String contact_Country, @Bind("stats_AvgSubRate") String stats_AvgSubRate); //request specific data from MailChimp (String type is used for simplicity) @SqlQuery("SELECT Stats_AvgSubRate FROM Lists WHERE Contact_Country = :contact_Country") String findStats_AvgSubRateByContact_Country(@Bind("contact_Country") String contact_Country); /* * close with no args is used to close the connection */ void close(); }

Open a Connection to MailChimp

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

You can set the APIKey to the key you generate in your account settings, or, instead of providing your APIKey, you can use the OAuth standard to authenticate the application. OAuth can be used to enable other users to access their own data. To authenticate using OAuth, you will need to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL by registering an app with MailChimp.

See the "Getting Started" chapter in the help documentation for a guide to using OAuth.

Built-in Connection String Designer

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

java -jar cdata.jdbc.mailchimp.jar

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

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

jdbc:mailchimp:APIKey=myAPIKey;

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:mailchimp:APIKey=myAPIKey;"); MyListsDAO dao = dbi.open(MyListsDAO.class); //do stuff with the DAO dao.close();

Read MailChimp Data

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

//disply the result of our 'find' method String stats_AvgSubRate = dao.findStats_AvgSubRateByContact_Country("US"); System.out.println(stats_AvgSubRate);

Write MailChimp Data

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

//add a new entry to the Lists entity dao.insert(newContact_Country, newStats_AvgSubRate);

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