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:
- Create a Java Project
- 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
Connection established successfully |
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
record inserted successfully |
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
Roll No:: 1 Name:: John Section:: A |
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
record updated successfully Roll No:: 1 Name:: John Section:: D |
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
record deleted successfully ********************** Roll No:: 2 Name:: Jack Section:: A ********************** Roll No:: 4 Name:: Jack Section:: A ********************** Roll No:: 1 Name:: John Section:: D |
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.