Over a million developers have joined DZone.

Connecting to Your Database Using JDBC

This post dives into using the Progress JDBC driver(s) to connect to various databases using Java and standard JDBC programming.

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

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, NoSQL, 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 the 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 the 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.
    3. To connect to a database using JDBC, first we need to load the JDBC driver into memory and use the DriverManager to get a connection to database. The code to demonstrate this as follows:
    4. 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);
              }
          }
      }


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

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


    3. 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.
    4. 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: ResultSetMetaData

    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.

    The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

    Topics:
    jdbc ,postgresql ,java ,eclipse ,jvm

    Published at DZone with permission of Saikrishna Teja Bobba, DZone MVB. See the original article here.

    Opinions expressed by DZone contributors are their own.

    The best of DZone straight to your inbox.

    SEE AN EXAMPLE
    Please provide a valid email address.

    Thanks for subscribing!

    Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
    Subscribe

    {{ parent.title || parent.header.title}}

    {{ parent.tldr }}

    {{ parent.urlSource.name }}