PostgreSQL JDBC Driver Integration: 4 Easy Steps

PostgreSQL is a popular RDBMS that finds applications in all verticals of the data industry. This open-source platform allows companies to manage their vast datasets in a hassle-free manner. Today, Java developers are also drawn toward the seamless functioning of the PostgreSQL tool. They are looking for ways to connect their Java Programs to PostgreSQL databases and manage their applications optimally.  

Now, PostgreSQL facilitates Java programs via a Java Database Connectivity (JDBC). This way Java developers can connect to the PostgreSQL databases easily and perform various transformations. Therefore, data professionals and Java Developers both are keen on setting up the PostgreSQL JDBC Driver integration.

This article introduces PostgreSQL and JDBC along with their key features. It also provides you with a step-by-step guide using which you can manually set up your PostgreSQL JDBC Driver integration.  Read along to learn more about PostgreSQL JDBC Driver connection and its benefits! 

What is PostgreSQL?

PostgreSQL is a well-known object-oriented RDBMS (Relational Database Management System). This open-source and free platform supports both relational and non-relational queries in the JSON (JavaScript Object Notation ) format. Its popularity stems from its rigid compliance with SQL standards. PostgreSQL is built to manage high workloads ranging from single machines to cloud-based data warehouses. 

Despite being launched much early than its peers, PostgreSQL still ranks as one of the most popular and efficient DBMS amongst developers and database professionals. Moreover, its multiple extensions allow users to customize the database according to their needs without modifying the core database. PostgreSQL also contains features that let you create your own user data types and new XML data queries. Furthermore, the streaming replication service of Postgres provides a great backup solution for users.

Key Features of PostgreSQL

PostgreSQL is popular because of its following features:

• Open Source: PostgreSQL is an open-source platform that allows you to store unlimited data without any bounds of functionality constraints. Moreover, this free tool does not impose any distribution restrictions on your data. 
• Integrations: PostgreSQL provides language support for all the prominent programming languages including C, C++, Phyton, Java, etc.
• Multiversion Concurrency Control: A very popular PostgreSQL feature, this enables multiple users to access a database and perform read and write operations at the same time.  
• No vendor lock-in: PostgreSQL has a free data distribution policy. This implies PostgreSQL has an array of companies providing services for Postgres and thus eliminates the vendor lock-in situation. 

What is JDBC?

JDBC (Java Database Connectivity) is a fully functional java API that enables you to connect with databases and execute Java queries. It was launched as a part of Java SE( Java Standard Edition). JDBC is a Sun microsystem specification that deploys a standard abstraction( API or Protocol) allowing java applications to seamlessly integrate with various databases. It contains the Java language coupled with the java database connectivity standards and can write programs for accessing databases. Moreover, you can access the enterprise data stored in any relational database via JDBC APIs.

JDBC APIs make use of JDBC drivers to get access to databases like PostgreSQL. JDBC APIs other than accessing the tabular data, also enable you to perform operations on the databases. You can perform save, update, delete, and fetch operations on the data stored in any database using JDBC.

Key Features of JDBC

JDBC provides you with the following unique features:

• JDBC Calls: JDBC allows Java applications to make JDBC calls regarding SQL statements to the driver. This facilitates easy access to data. 
• Portability: JDBC provides wide-range portability and allows you to request any type of query from the database
• Integrations: You can deploy JDBC with numerous Java applications including Java Applets, Java Servlets, and Java Server Pages. etc., for setting up communication with other databases. 
• Rollback:  JDBC allows you to set checkpoints for databases and enables you to roll back to the desired checkpoint anytime. This provides a safety net in case of a server crash.

How to set up PostgreSQL JDBC Driver Integration?

This method requires you to add a PostgreSQL driver in your classpath and connect to a PostgreSQL database server. You need to use a DriverManager to provide connection strings and input the right credentials to connect to the server. Once the PostgreSQL JDBC Driver Integration is set, you can then execute queries on the established connection. 

The following steps will allow you to set up a PostgreSQL JDBC Driver Integration:

• Step 1: Connecting To the Database
• Step 2: Create a Table
• Step 3: INSERT Operation in PostgreSQL JDBC Driver Integration
• Step 4: SELECT Operation in PostgreSQL JDBC Driver Integration

Step 1: Connecting To the Database

The following code will enable you to connect JDBC to an existing PostgreSQL database and complete the first step of PostgreSQL JDBC Driver Integration. If no such database exists, then the code will create a new one, and return a database object. 

import java.sql.Connection;
import java.sql.DriverManager;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "newtest", "555");
      } catch (Exception e) {
         e.printStackTrace();
         System.err.println(e.getClass().getName()+": "+e.getMessage());
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

Note: Before compiling and running the above Java code, navigate to the pg_hba.conf file in your PostgreSQL installation directory and insert the following:

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

In case the server stops working, use the following commands to restart it:

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Now, compile and run the Java code to connect with PostgreSQL Database. Here, the user ID is “newtest” and the password used is “555” for accessing the PostgreSQL Database. (You may change this to suit your database configuration). 

The output of the above code will be as follow:

C:JavaPostgresIntegration>javac PostgreSQLJDBC.java
C:JavaPostgresIntegration>java -cp c:toolspostgresql-9.2-1002.jdbc3.jar;C:JavaPostgresIntegration PostgreSQLJDBC

Open database successfully

Step 2: Create a Table

Now, since you have created a database, you require to build a table in it to store data. You can use the following code to generate a new table in your above database (Do ensure that a table of a similar name is not already present in your target database):

import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "newtest", "555");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE BUSINESS " +
            "(ID INT PRIMARY KEY     NOT NULL," +
            " NAME           TEXT    NOT NULL, " +
            " AGE            INT     NOT NULL, " +
            " ADDRESS        CHAR(50), " +
            " SALARY         REAL)";
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

The above code program is compiled and executed, it will create the BUSINESS table in testdb database and will provide the following output:

Opened database successfully
Table created successfully

Step 3: INSERT Operation in PostgreSQL JDBC Driver Integration

The above two stps created a database and inserted the required table in your PostgreSQL database using JDBC. Now, the following Java code will allow you to insert records in the BUSINESS table created in the previous step:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "newtest", "555");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO BUSINESS (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (1, 'Amit', 23, 'California', 30000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO BUSINESS (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (2, 'Tony', 27, 'Texas', 25000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO BUSINESS (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (3, 'Tom', 26, 'Norway', 22000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO BUSINESS (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (4, 'Mandy', 28, 'Rich-Mond ', 55000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

The above con on execution will create the above-mentioned records in the BUSINESS table and will give the following output:

Opened database successfully
Records created successfully

Step 4: SELECT Operation in PostgreSQL JDBC Driver Integration

You can deploy the following Java code to fetch and display records from the BUSINESS table using the PostgreSQL JDBC Driver integration:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "newtest", "555");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         ResultSet rs = stmt.executeQuery( "SELECT * FROM BUSINESS;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

The above code will provide the following output:

Opened database successfully
ID = 1
NAME = Amit
AGE = 23
ADDRESS = California
SALARY = 30000.0

ID = 2
NAME = Tony
AGE = 27
ADDRESS = Texas
SALARY = 25000.0

ID = 3
NAME = Tom
AGE = 26
ADDRESS = Norway
SALARY = 22000.0

ID = 4
NAME = Mandy
AGE = 28
ADDRESS = Rich-Mond
SALARY = 55000.0

That’s it! You can now try the PostgreSQL JDBC Driver Integration by yourself.

Benefits of PostgreSQL JDBC Driver Integration

Setting up the PostgreSQL JDBC Driver integration will provide your business with the following benefits:

• The PostgreSQL JDBC Driver integration allows you to read databases of all kinds. All you need to ensure is that JDBC drivers are installed correctly.
• The PostgreSQL Java Driver provides you the option of automating the process of generating XML files from your database contents. This will safeguard you from manually transforming your PostgreSQL data into the correct formats.
• Setting up the PostgreSQL JDBC Driver integration provides you with full support to manage queries and stored procedures for your PostgreSQL database. Moreover, it is capable of managing both Synchronous and Asynchronous data processing.
• PostgreSQL JDBC Connection String offers enterprise-class performance and functions and using the Open Source DBMS with JDBC, you can extend your development possibilities. Furthermore, PostgreSQL users can directly participate in the large worldwide communities and find solutions to inconveniences and bugs.

Conclusion

The article introduced you to PostgreSQL and JDBC along with their unique features. It also provided a step-by-step guide using which you can easily connect your PostgreSQL Databases with JDBC. Furthermore, it elaborated on the benefits that PostgreSQL JDBC Driver integration can provide to your business data. After reading this article, you can also try and seamlessly establish a JDBC PostgreSQL integration.

Visit our Website to Explore Hevo

Now, to run SQL queries or perform Data Analytics on your PostgreSQL data, you first need to export this data to a Data Warehouse. This will require you to custom code complex scripts to develop the ETL processes. Hevo Data can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 100+ multiple sources like PostgreSQL to Cloud-based Data Warehouses like Amazon Redshift, Snowflake, Google BigQuery, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

Share your understanding of the PostgreSQL JDBC Driver Integration in the comments below!