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/
