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

Create a DAO for the Outlook CalendarGroupCalendars 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 MyCalendarGroupCalendarsDAO {
  //request specific data from Outlook (String type is used for simplicity)
  @SqlQuery("SELECT  FROM CalendarGroupCalendars WHERE CalendarGroupId = :calendarGroupId")
  String findByCalendarGroupId(@Bind("calendarGroupId") String calendarGroupId);

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

Open a Connection to Outlook

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

Using OAuth Authentication

Microsoft Graph API uses OAuth 2.0 for authentication. You must register an application in the Microsoft Azure Portal to obtain OAuth credentials (Client ID and Client Secret).

Obtaining OAuth Credentials

1. Log in to the Azure Portal.
2. Navigate to Azure Active Directory > App registrations.
3. Click New registration to create a new application.
4. Enter an application name and select the appropriate account types.
5. Set the Redirect URI to your application's callback URL (e.g., http://localhost:33333 for desktop apps).
6. Click Register to create the application.
7. On the application overview page, copy the Application (client) ID - this is your OAuthClientId.
8. Navigate to Certificates & secrets and create a new client secret.
9. Copy the client secret value - this is your OAuthClientSecret.
10. Navigate to API permissions and add the required Microsoft Graph API permissions:
    • Mail.Read - For accessing email messages
    • Contacts.Read - For accessing contacts
    • Calendars.Read - For accessing calendar events
    • Tasks.Read - For accessing To Do tasks
    • offline_access - For obtaining refresh tokens
11. Click Grant admin consent to grant these permissions.

Connecting with OAuth

After setting the following connection properties, you are ready to connect:

• AuthScheme: Set this to OAuth.
• InitiateOAuth: Set this to GETANDREFRESH. The CData API Profile for Outlook will automatically walk through the OAuth process in order to obtain the access token.
• OAuthClientId: Set this to the Application (client) ID from Azure Portal.
• OAuthClientSecret: Set this to the client secret value from Azure Portal.
• TenantId: Set this to your Azure AD tenant identifier (GUID or domain name like 'contoso.onmicrosoft.com').
• CallbackURL: Set this to the Redirect URI you specified in your app registration (e.g., http://localhost:33333 for desktop apps).

Example connection string

Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;

Built-in Connection String Designer

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

java -jar cdata.jdbc.api.jar

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

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

jdbc:api:Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;

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:api:Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;");
MyCalendarGroupCalendarsDAO dao = dbi.open(MyCalendarGroupCalendarsDAO.class);

//do stuff with the DAO

dao.close();

Read Outlook Data

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

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

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