PosgtreSQL is a free and general purpose open source object-relational database system that uses and extends the SQL language. Though originally designed to run on UNIX platforms, Postgres is able to run on various platforms, such as macOS, Solaris, Windows, Unix, and Linux. PostgreSQL databases provide enterprise-class database solutions and are used by a wide variety of enterprises across many industries, like financial services, information technology, government and media & communications.

This article first covers installation of PostgreSQL and Java on Linux Platform and thereafter integration of PostgreSQL with JDBC. The article shows how to connect to PostgreSQL database using JDBC Driver on a Linux platform (this example uses Centos). The article covers basic select, insert, update, delete operations.

By the end of this article reader should be able to install and run PostgreSQL database on Linux platform. Also should be able to connect to the PostgreSQL db with JDBC via Java programs.

The examples have been tested to run with the following software setup:

• Java 1.7 or above
• Eclipse (IDE)
• PostgreSQL 10
• PgAdmin
• PostgreSQL JDBC driver 42.2.5(A jar file, add it to the project classpath) – Available here http://jdbc.postgresql.org/download.html

Software Setup

Note - This section can be skipped if the required software are already there in the system.

First, we need to install PostgreSQL with the help of the below command. This installs PostgreSQL from Centos repositories.

sudo apt install postgresql postgresql-contrib

To check the version installed, use the command

postgres --version

Post installation, initialize the database and start the database thereafter

sudo postgresql-setup initdb
sudo systemctl start postgresql

PgAdmin (Optional Installation)

This a free source GUI tool of PostgreSQL or in other words a client supported by all operating systems like Windows, MAC, Linux which can be used to access the schema and tables. If not preferred, you can always connect to the postgreSQL db via the terminal.

Install PgAdmin in centos. The following command installs PgAdmin4 in the system.

yum -y install pgadmin4

For installation and configuration in details, visit https://www.tecmint.com/install-pgadmin4-in-centos-7/

Java

The following commands installs Java 1.8 in Linux platform. Since this article covers integration of PostgreSQL with JDBC , Java is required to compile and run the programs covered below. This first command adds the java repository.

add-apt-repository ppa:webupd8team/java

This will update the repository

apt update

Lastly, we install Oracle JDK8

apt-get install oracle-java8-installer

Eclipse IDE

Eclipse is a free and open source IDE for programming. We use this IDE  in our examples to write and execute the Java programs. There are also other IDE's available in the market, which can be used. In this particular example we use Eclipse simply because it is lightweight and very user friendly.

Login as administrator and download Eclipse using the following command,

wget http://ftp.jaist.ac.jp/pub/eclipse/technology/epp/downloads/release/2019-03/R/eclipse-java-2019-03-R-linux-gtk-x86_64.tar.gz

Extract the Eclipse package to your desired directory (/opt).

tar -zxvf eclipse-java-2019-03-R-linux-gtk-x86_64.tar.gz -C /opt

Next, create Eclipse desktop launcher into system applications directory

sudo nano /usr/share/applications/eclipse.desktop

Add the following configuration into the file, eclipse.desktop.

[Desktop Entry]
Name=Eclipse IDE
Comment=Eclipse IDE
Type=Application
Encoding=UTF-8
Exec=/usr/local/bin/eclipse
Icon=/opt/eclipse/icon.xpm
Categories=GNOME;Application;Development;
Terminal=false
StartupNotify=true

After you’ve created Eclipse launcher, use Gnome dash to search and open the application

Code and Illustration

Before programming in Java we need to have the database and table in place. We will be using the default database 'postgres' in this tutorial. First, navigate to the Centos terminal. Login to the default database 'postgres' as user 'postgres'. This will open up the 'postgres=#' prompt.

sudo -u postgres psql

Enter \c to verify

postgres=# \c

Enter command \l to get a list of all databases

postgres=# \l

Now, let us create a table called 'Student'.

CREATE TABLE STUDENT
(
    ROLL integer NOT NULL Primary Key,
    NAME varchar(100) NOT NULL,
    SECTION varchar(2) NOT NULL,
    CREATED_DATE TIMESTAMP NOT NULL
);

We will use Eclipse to execute the following Java programs. If you are not familiar with Java programming in Eclipse you can take a look here, https://www.tutorialspoint.com/eclipse/eclipse_create_java_project.htm To get started, follow these steps:

1. Create a Java Project
2. Create package hierarchy 'postgresql.jdbc'

JDBC Database Connection

The DriverManager.getConnection() establishes a connection to the database by using the given database URL and the registered JDBC driver. Create a class 'PostgresWithJDBCConnection'

package postgresql.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class PostgresWithJDBCConnection {
    public static void main(String[] args) {
        // establishes database connection
        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/students", "postgres", "adminuser")) {
            System.out.println("Connection established successfully");
        } catch (SQLException e) {
            System.out.print(e.getMessage());
        }
    }
}

When the above program is compiled and executed successfully, it should display the following output

JDBC Insert

The following Java which program shows how we can insert records in the Student table, is primarily divided into two segments.

• connection to the database
• preparation and execution of the prepared statement using insert query

Create a class 'PostgresWithJDBCInsert'

package postgresql.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
public class PostgresWithJDBCInsert {
    public static void main(String[] args) {
        String SQL_INSERT = "INSERT INTO STUDENT (ROLL, NAME, SECTION, CREATED_DATE) VALUES (?, ?, ?, ?)";
        // establishes database connection
        // auto closes connection and preparedStatement
        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/students", "postgres",
                "adminuser");
            PreparedStatement preparedStatement = conn.prepareStatement(SQL_INSERT)) {
            // insert student record
            preparedStatement.setInt(1, 06); //1 specifies the first parameter in the query
            preparedStatement.setString(2, "Arnold");
            preparedStatement.setString(3, "A");
            preparedStatement.setTimestamp(4, new Timestamp(new Date().getTime()));
            preparedStatement.executeUpdate();
            System.out.print("record inserted successfully");
        } catch (SQLException e) {
            System.out.print(e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

When the above program is compiled and executed successfully, it should display the following line

JDBC Select

The following program shows how we can fetch and display all records from the Student table. Similar to the insert program, the following program is divided into two segments:

• connection to the database
• preparation and execution of the PreparedStatement using select query

Create a class 'PostgresWithJDBCSelect'

package postgresql.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
public class PostgresWithJDBCSelect {
    public static void main(String[] args) {
        List < Student > studentList = new ArrayList < > ();
        String SQL_SELECT = "Select * from STUDENT";
        // establishes database connection
        // auto closes connection and preparedStatement
        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/students", "postgres", "adminuser");

            PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT)) {
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int rollId = resultSet.getInt("ROLL");
                String name = resultSet.getString("NAME");
                String section = resultSet.getString("SECTION");
                Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");
                Student student = new Student();
                student.setRoll(rollId);
                student.setName(name);
                student.setSection(section);
                student.setCreatedDate(createdDate.toLocalDateTime());
                studentList.add(student);
            }
            for (Student student: studentList) {
                System.out.println("Roll No:: " + student.getRoll());
                System.out.println("Name:: " + student.getName());
                System.out.println("Section:: " + student.getSection());
            }

        } catch (SQLException e) {
            System.out.print(e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

When the above program is compiled and executed successfully, it should display the following

JDBC Update

The following Java program shows how we can update a record in Student table. Here also the program is divided into three segments:

• connection to the database
• preparation and execution of the PreparedStatement using update query
• fetches the updated data using select query

Create a class 'PostgresWithJDBCUpdate'

package postgresql.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
public class PostgresWithJDBCUpdate {
    public static void main(String[] args) {
        String SQL_UPDATE = "UPDATE STUDENT set SECTION = 'D' where ROLL=1;";
        List < Student > studentList = new ArrayList < > ();
        String SQL_SELECT = "Select * from STUDENT where ROLL=1";

        // establishes database connection
        // auto closes connection and preparedStatement
        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/students", "postgres",
                "adminuser");
            PreparedStatement preparedStatement = conn.prepareStatement(SQL_UPDATE)) {
            // update student record
            preparedStatement.executeUpdate();
            System.out.println("record updated successfully");
            // fetch updated record.
            PreparedStatement preparedStatement1 = conn.prepareStatement(SQL_SELECT);
            ResultSet resultSet = preparedStatement1.executeQuery();
            while (resultSet.next()) {
                int rollId = resultSet.getInt("ROLL");
                String name = resultSet.getString("NAME");
                String section = resultSet.getString("SECTION");
                Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");
                Student student = new Student();
                student.setRoll(rollId);
                student.setName(name);
                student.setSection(section);
                student.setCreatedDate(createdDate.toLocalDateTime());
                studentList.add(student);
            }
            for (Student student: studentList) {
                System.out.println("Roll No:: " + student.getRoll());
                System.out.println("Name:: " + student.getName());
                System.out.println("Section:: " + student.getSection());
            }
            preparedStatement1.close();
        } catch (SQLException e) {
            System.out.print(e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

When the above program is compiled and executed successfully, it should display the following

JDBC Delete

The following Java program shows how we can delete a record in Student table. The following program is divided into three segments:

• connection to the database
• preparation and execution of the PreparedStatement using delete query
• fetches the updated data using select query

Create a class 'PostgresWithJDBCDelete'

    package postgresql.jdbc;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Timestamp;
    import java.util.ArrayList;
    import java.util.List;
    public class PostgresWithJDBCDelete {
        public static void main(String[] args) {
            String SQL_DELETE = "Delete from STUDENT where ROLL=3;";
            List < Student > studentList = new ArrayList < > ();
            String SQL_SELECT = "Select * from STUDENT";
            // establishes database connection
            // auto closes connection and preparedStatement
            try (Connection conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/students", "postgres",
                    "adminuser");
                PreparedStatement preparedStatement = conn.prepareStatement(SQL_DELETE)) {
                // delete student record
                preparedStatement.executeUpdate();
                System.out.println("record deleted successfully");
                // fetch record.
                PreparedStatement preparedStatement1 = conn.prepareStatement(SQL_SELECT);
                ResultSet resultSet = preparedStatement1.executeQuery();
                while (resultSet.next()) {
                    int rollId = resultSet.getInt("ROLL");
                    String name = resultSet.getString("NAME");
                    String section = resultSet.getString("SECTION");
                    Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");
                    Student student = new Student();
                    student.setRoll(rollId);
                    student.setName(name);
                    student.setSection(section);
                    student.setCreatedDate(createdDate.toLocalDateTime());
                    studentList.add(student);
                }
                for (Student student: studentList) {
                    System.out.println("**********************");
                    System.out.println("Roll No:: " + student.getRoll());
                    System.out.println("Name:: " + student.getName());
                    System.out.println("Section:: " + student.getSection());
                }
                preparedStatement1.close();

            } catch (SQLException e) {
                System.out.print(e.getMessage());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

When the above program is compiled and executed successfully, it should display the following

Summary

To summarize over the course of this article we present an overview of how we can install PostgreSQL, Java on Linux(Centos) and then connect to the PostgreSQL database using JDBC  and perform some crud operations on it via Java program.