Enterprise Java

Jdbi for SaaS & NoSQL

A natural interface for Java-based data integration with CRM, ERP, Accounting, Marketing Automation, NoSQL, flat files and more

Jdbi is a SQL convenience library for Java that provides JDBC with a more natural Java database interface that’s easy to bind to domain data types. The library includes building blocks that allow developers to more easily map relationships and objects within applications.

Often, Java developers think about using Jdbi to work with relational databases. However, new JDBC drivers have emerged to offer live data connectivity from a variety of SaaS, NoSQL and Big Data stores that expand Jdbi applicability to a new generation of projects.

Take, for example, the JDBC drivers from CData Software (www.cdata.com/jdbc/). CData offers pure Java JDBC drivers with real-time access to application sources, such as Salesforce, NetSuite, Eloqua, Marketo, Dynamics, Hubspot and more than 100+ others. They even provide SQL connectivity to NoSQL data sources, such as MongoDB, Couchbase, Redis, Athena, BigQuery and more..

Leveraging these drivers, Java developers can easily integrate live data connectivity into their applications. The rest of this article gives a brief overview of creating a SQL Object API for Salesforce data in Jdbi using the CData JDBC driver for Salesforce.

Jdbi Access to Salesforce

Jdbi exposes two different style APIs: a fluent style and a SQL object style. The CData JDBC driver for Salesforce integrates connectivity to live Salesforce data in Java applications. By pairing these technologies, you gain simple, programmatic access to Salesforce data. The following code walks through building a basic Data Access Object (DAO) to read and write Salesforce data.

Create a DAO for the Salesforce 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.

// JavaScript source code
public interface MyAccountDAO {
  //insert new data into Salesforce
@SqlUpdate("INSERT INTO Account (Name, AnnualRevenue) values (:name, :annualRevenue)")
  void insert(@Bind("name") String name, @Bind("annualRevenue") String annualRevenue);
 
  //request specific data from Salesforce (String type is used for simplicity)
@SqlQuery("SELECT AnnualRevenue FROM Account WHERE Name = :name")
  String findAnnualRevenueByName(@Bind("name") String name);
 
  /*
  * close with no args is used to close the connection
  */
  void close();
}

Open a Connection to Salesforce

Collect the necessary connection properties and construct the appropriate JDBC URL for connecting to Salesforce, which will typically look like the following:

jdbc:salesforce:User=username;Password=password;SecurityToken=Your_Security_Token;

There are several authentication methods available for connecting to Salesforce:

  • Login
  • OAuth
  • SSO

The Login method requires you to have the username, password and security token of the user. If you do not have access to the username and password or do not wish to require them, you can use OAuth authentication.

SSO (single sign-on) can be used by setting the SSOProperties, SSOLoginUrl and TokenUrl connection properties, which allow you to authenticate to an identity provider. See the “Getting Started” chapter in the help documentation for more information.

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:salesforce:User=username;Password=password;SecurityToken=Your_Security_Token;");
MyAccountDAO dao = dbi.open(MyAccountDAO.class);

//do stuff with the DAO
dao.close();

Read Salesforce Data

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

//display the result of our 'find' method
String annualRevenue = dao.findAnnualRevenueByName("GenePoint");
System.out.println(annualRevenue);

Write Salesforce Data

It’s also simple to write data to Salesforce using the previously defined method.

//add a new entry to the Account entity
dao.insert(newName, newAnnualRevenue);

Since the Jdbi library is able to work with JDBC connections, you can easily produce a SQL Object API for Salesforce by integrating with the CData JDBC driver for Salesforce.

It’s that easy

Jdbi provides an easy-to-use data interface that can simplify the complexity of your data connectivity. When paired with the JDBC drivers from CData, Jdbi provides a clean object interface that can contribute to a 10x or more reduction in code on your next integration project, all while enhancing maintainability and reuse.

More information about Jdbi can be found online at: http://jdbi.org/

Download a free trial of the Salesforce JDBC driver (and 100+ additional JDBC drivers) from the CData website: https://www.cdata.com/jdbc/

CData Software Inc.

CData Software (www.cdata.com) is a leading provider of standards-based drivers and data access solutions for real-time integration with on-premise and SaaS applications, NoSQL, and Big Data. Our drivers are universally accessible, providing straightforward access to popular data technologies like JDBC, ODBC, ADO.NET, and OData. Our tools offer real-time integration from BI, ETL, Reporting, Analytics and custom Apps to more than 100+ data sources, including Big Data & NoSQL databases, CRM, ERP, and Accounting packages.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button