Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →Create a Data Access Object for Avalara AvaTax Data using JDBI
A brief overview of creating a SQL Object API for Avalara AvaTax 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 Avalara AvaTax integrates connectivity to live Avalara AvaTax data in Java applications. By pairing these technologies, you gain simple, programmatic access to Avalara AvaTax data. This article walks through building a basic Data Access Object (DAO) and the accompanying code to read and write Avalara AvaTax data.
Create a DAO for the Avalara AvaTax Transactions 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 MyTransactionsDAO {
//insert new data into Avalara AvaTax
@SqlUpdate("INSERT INTO Transactions (Code, TotalTax) values (:code, :totalTax)")
void insert(@Bind("code") String code, @Bind("totalTax") String totalTax);
//request specific data from Avalara AvaTax (String type is used for simplicity)
@SqlQuery("SELECT TotalTax FROM Transactions WHERE Code = :code")
String findTotalTaxByCode(@Bind("code") String code);
/*
* close with no args is used to close the connection
*/
void close();
}
Open a Connection to Avalara AvaTax
Collect the necessary connection properties and construct the appropriate JDBC URL for connecting to Avalara AvaTax.
The primary method for performing basic authentication is to provide your login credentials, as follows:
- User: Set this to your username.
- Password: Set this to your password.
Optionally, if you are making use of a sandbox environment, set the following:
- UseSandbox: Set this to true if you are authenticating with a sandbox account.
Authenticating Using Account Number and License Key
Alternatively, you can authenticate using your account number and license key. Connect to data using the following:
- AccountId: Set this to your Account Id. The Account Id is listed in the upper right hand corner of the admin console.
- LicenseKey: Set this to your Avalara Avatax license key. You can generate a license key by logging into Avalara Avatax as an account administrator and navigating to Settings -> Reset License Key.
Built-in Connection String Designer
For assistance in constructing the JDBC URL, use the connection string designer built into the Avalara AvaTax JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.
java -jar cdata.jdbc.avalaraavatax.jar
Fill in the connection properties and copy the connection string to the clipboard.
A connection string for Avalara AvaTax will typically look like the following:
jdbc:avalaraavatax:User=MyUser;Password=MyPassword;
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:avalaraavatax:User=MyUser;Password=MyPassword;");
MyTransactionsDAO dao = dbi.open(MyTransactionsDAO.class);
//do stuff with the DAO
dao.close();
Read Avalara AvaTax Data
With the connection open to Avalara AvaTax, simply call the previously defined method to retrieve data from the Transactions entity in Avalara AvaTax.
//disply the result of our 'find' method
String totalTax = dao.findTotalTaxByCode("051349");
System.out.println(totalTax);
Write Avalara AvaTax Data
It is also simple to write data to Avalara AvaTax, using the previously defined method.
//add a new entry to the Transactions entity
dao.insert(newCode, newTotalTax);
Since the JDBI library is able to work with JDBC connections, you can easily produce a SQL Object API for Avalara AvaTax by integrating with the CData JDBC Driver for Avalara AvaTax. Download a free trial and work with live Avalara AvaTax data in custom Java applications today.