Apr 16, 20224 min read

Connecting to Your Database Using JDBC Driver

Here, 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:

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

Prerequisites

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

Have Postgres installed on your machine/server
Have a Salesforce account. In case you don’t have one, register yourself for one
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

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;

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.

Open your favorite IDE and create a new Java project.
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.
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);
        }
    }
}

The important things to notice here would be the lineClass.forName(className) which is responsible for loading the driver in to the memory.
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.
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:datadirect:postgresql://<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

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());
    }
}

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.
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: