[progress News] [progress Openedge Abl] Jdbc Tutorial: Connecting To Your Database Using Jdbc

In the first tutorial of the series, we will show you how you can use Progress DataDirect JDBC drivers in your Java application to connect to your database.

JDBC drivers are the most conventional way that many developers use to connect to databases from their Java applications. These drivers, once primarily available for relational databases, are now available for almost any type of data source such as Big Data, No SQL and SaaS.

Even with all these different types of data sources, it’s still easy to connect to and work with them using Progress DataDirect JDBC drivers. To demonstrate this, we have a planned a “JDBC—Revisited” tutorial series of three parts, which covers various concepts from creating a connection, executing simple queries, extracting metadata and DB interoperability features of DataDirect drivers.

In the first tutorial of the series, we will show you how you can use Progress DataDirect JDBC drivers in your Java application to connect to your database. The main objectives of this tutorial are to demonstrate:

1. Connecting to your data source using JDBC
2. Running simple SQL queries to fetch data

For the purpose of this tutorial, we will be using a relational database (Postgres) and SaaS data source (Salesforce) as our data sources. The goal is to demonstrate the simplicity of our Progress DataDirect JDBC drivers for Postgres and Salesforce for connecting and retrieving the data, irrespective of type of the data source.

Prerequisites


We expect you have the following setup before proceeding further with this tutorial.

1. Have Postgres installed on your machine/server
2. Have a Salesforce account. In case you don’t have one, register yourself for one
3. Download and Install Progress DataDirect JDBC drivers for Postgres and Salesforce

If you're interested in connecting to just one data source, then you only need to set up one of these.

Importing Sample Database Into Postgres

1. If you need data for your Postgres, you can download the DVD rental sample database and import it. To import the sample database, create a database in Postgres by running the following command either in psql or pgAdmin.
   
   
   
   create database dvdrentals;
   
   
2. Now run the following command to import the database from the downloaded database backup.
   
   pg_restore -U -d dvdrentals /path/to/dvdrental.tar

Connecting to Your Data Source


With everything setup for you to connect to database, let’s start the fun by writing code to connect to your data source.

1. Open your favorite IDE and create a new Java project.
2. The next important thing will be to add the JDBC drivers to the build path of the project.
   1. To do this in eclipse, Right click on Project in package explorer -> Build Path -> Add External Archives. Locate the JDBC driver jar file and press Open.
   2. To do this in Intellij, right click on Project folder -> Open module settings, this should open a new window. Go to libraries under Project settings and click on add to locate and add the JDBC jar files to the project.
3. To connect to a database using JDBC, first we need to load the JDBC driver in to memory and use the DriverManager to get a connection to database. The code to demonstrate this as follows:
   public class JDBCUtil {
   String className, URL, user, password;
   Connection connection;
   public JDBCUtil(String className, String URL, String user, String password) {
   this.className = className;
   this.URL = URL;
   this.user = user;
   this.password = password;
   this.connection = null;
   }
   public void getConnection() {
   //Load the driver class
   try {
   Class.forName(className);
   } catch (ClassNotFoundException ex) {
   System.out.println("Unable to load the class. Terminating the program");
   System.exit(-1);
   }
   //get the connection
   try {
   connection = DriverManager.getConnection(URL, user, password);
   } catch (SQLException ex) {
   System.out.println("Error getting connection: " + ex.getMessage());
   System.exit(-1);
   } catch (Exception ex) {
   System.out.println("Error: " + ex.getMessage());
   System.exit(-1);
   }
   }
   }
4. The important things to notice here would be the lineClass.forName(className) which is responsible for loading the driver in to the memory.
5. The next important thing is the line connection = DriverManager.getConnection(URL, user, password). This line actually gets the connection to the database if all the parameters are correctly configured and stores it in the connection object which can be used later to execute queries.
6. Now let’s talk about the parameters that we pass to the above two steps.
   1. To load the class, we are passing className so that the driver can be loaded in to memory. Each JDBC driver has a unique className, and since we are loading the Progress DataDirect Postgres driver, you can initialize the className property in the JDBCUtil class with driver’s class name of com.ddtek.jdbc.postgresql.PostgreSQLdriver.
   2. The next important value that you would have to pass to get a connection from DriverManager would be the JDBC URL. Every database has a unique way of configuring the URL, and for the Progress DataDirect Postgres JDBC driver it would be in the format of jdbc:datadirectostgresql://<hostname>:<post>;DatabaseName=<database Name>
   3. The other values that you pass to DriverManager.getConnection() method would be the username and password for your database. If all these values are perfectly configured, you will get back a connection object or you will get an exception.
   4. To connect to Salesforce, you just change the className to com.ddtek.jdbc.sforce.SForceDriver and the JDBC URL for it would be in the format of jdbc:datadirect:sforce://<hostname>;SecurityToken=<token>. With such minimal changes, you can connect to Salesforce easily.
      Note: A security token is not required when Salesforce has been configured for trusted IP ranges and the user is logging in from a trusted IP address.

Executing Simple SQL Queries

1. Now that we have created a connection to the database, we can run SQL queries with its help. Here's some sample code to demonstrate the execution of SQL query and printing it to the console:
   public void executeQuery(String query)
   {
   ResultSet resultSet = null;
   try
   {
   //executing query
   Statement stmt = connection.createStatement();
   resultSet = stmt.executeQuery(query);
   //Get Number of columns
   ResultSetMetaData metaData = resultSet.getMetaData();
   int columnsNumber = metaData.getColumnCount();
   //Printing the results
   while(resultSet.next())
   {
   for(int i = 1; i <= columnsNumber; i++)
   {
   System.out.printf("%-25s", (resultSet.getObject(i) != null)?resultSet.getObject(i).toString(): null );
   }
   }
   }
   catch (SQLException ex)
   {
   System.out.println("Exception while executing statement. Terminating program... " + ex.getMessage());
   }
   catch (Exception ex)
   {
   System.out.println("General exception while executing query. Terminating the program..." + ex.getMessage());
   }
   }
2. The important things to observe are connection.createStatement() and stmt.executeQuery(query). The createStatement() method creates a Statement object for sending SQL statements to your database and executeQuery(query) executes your SQL query and returns a ResultSet object.
3. To print the values in Resultset, we get the number of columns for that table first using ResultSetMetaData—you'll learn more about this in the next part of this tutorial series. Then iterate through each and every row that you get from the ResultSet and print the values to the console. Below is a screenshot of the results of a simple query that I ran against Salesforce for your reference:
   

Easy Connection and Execution


I hope this tutorial helped you understand how easy it is to connect to your database and execute simple SQL queries in Java using Progress DataDirect JDBC drivers. Also, I have pushed the code that’s used in this tutorial to the GitHub for your reference. If you still have any issues connecting to your database using Progress DataDirect JDBC drivers, leave your comments below or contact support.

In the next part of this "JDBC—Revisited" tutorial series, we will show you how to get the metadata from your databases using JDBC drivers. Subscribe to our blog via Email or RSS feed for updates to this tutorial series.

Try Free For 15 Days

Continue reading...