Overview

PostgreSQL is a free and general purpose open source object-relational database system that uses and extends the SQL language while Springboot is an open source, Java based framework that helps developers to setup spring applications on the fly by removing boilerplate code and configurations. Though there are several ways to connect to PostgreSQL database from a Springboot application, we are going to focus on JDBC template and how to perform CRUD operations using it in this article.

The sample program have been tested to run with the following software:

• PostgreSQL 10 or above
• PgAdmin4(GUI tool of PostgreSQL)
• Eclipse IDE

Software Setup

Note - This section can be skipped if the required software setup exists.

PostgreSQL with PgAdmin4

The following tutorial covers the installation of  PostgreSQL with PgAdmin4 on windows:  https://qa.sqlservercentral.com/articles/installing-postgresql-11-on-windows-step-by-step-instruction

The following tutorial covers the installation of  PostgreSQL with PgAdmin4 on Linux: https://qa.sqlservercentral.com/articles/postgresql-jdbc-tutorial-on-linux

Eclipse IDE

Eclipse is an open source IDE suitable for the development of Java and Spring applications. Windows/Linux specific installer can be downloaded from the official website of Eclipse  https://www.eclipse.org/downloads/

Project Setup

Now that we have all the required software in place, let us get started. There are two ways to create a Springboot project:.

1. Create a new maven project in Eclipse and add all necessary Springboot dependencies.
2. Generate and download a new Springboot project from Springboot's official site, https://start.spring.io/, followed by importing in Eclipse.

The project structure in shown as below:

 Add dependency for Springboot & PostgreSQL JDBC Driver

In order to have all the required libraries in place for our project to be able to compile and run successfully, we have to add the following dependencies to the pom.xml file. The dependency tag with definition can be had directly from maven's official repository website, https://mvnrepository.com/

Configure Data Source Properties

Next, in order to able to communicate with PostgreSQL database from the Springboot application, we need to specify the database connection properties in the Springboot application configuration file, application.properties.

spring.datasource.url=jdbc:postgresql://localhost:5432/library
spring.datasource.username=postgres
spring.datasource.password=postgres

Database Integration Programs  - CRUD

We will create couple of classes inside a package as shown in the project structure above

1. SpringBootJDBCIntegrationDemo - Class consists of the main program and the individual method for CRUD operations. This class is the entry point in a Springboot application.

CREATE A RECORD

private void insertRecord() {
		String sql = "INSERT INTO library_master(bookname,bookid,bookgenre) VALUES ("
				+ "'Let us c', '03', 'Technical')";
		int rows = jdbcTemplate.update(sql);
		if (rows > 0) {
			System.out.println("A new row has been inserted.");
		}
	}

READ A RECORD

private Library fetchBookById(int id) {
		String sql = "SELECT * FROM library_master where bookid=?";
		return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Library.class), id);
	}

UPDATE A RECORD

private void updateRecordById(int id) {
		String sql = "UPDATE library_master SET bookname='Let us Python' where bookid=?";
		int rows = jdbcTemplate.update(sql,id);
		if (rows > 0) {
			System.out.println("*********Record updated successfully************");
		}
	}

DELETE A RECORD

private void deleteRecordById(int id) {
		String sql = "DELETE FROM library_master where bookid=?";
		int rows = jdbcTemplate.update(sql,id);
		if (rows > 0) {
			System.out.println("*********Record deleted successfully************");
		}
	}

MAIN PROGRAM

package com.example.jdbcpostgresdemo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
@SpringBootApplication
public class SpringBootJDBCIntegrationDemo implements CommandLineRunner {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	public static void main(String[] args) {
		SpringApplication.run(SpringBootJDBCIntegrationDemo.class, args);
	}
	public void run(String... args) throws Exception {
		insertRecord();
		Library book = fetchBookById(01);
		if (null != book) {
			System.out.println("**********Fetching record***********");
			System.out.println("bookName" + book.getBookName());
			System.out.println("bookId" + book.getBookId());
			System.out.println("bookGenre" + book.getBookGenre());
		}
		updateRecordById(05);
		deleteRecordById(04);
	}

2. Library - Java POJO class to hold the state of the object before and after database interaction.

package com.example.jdbcpostgresdemo;
public class Library {
	private String bookName;
	private String bookId;
	private String bookGenre;
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public String getBookId() {
		return bookId;
	}
	public void setBookId(String bookId) {
		this.bookId = bookId;
	}
	public String getBookGenre() {
		return bookGenre;
	}
	public void setBookGenre(String bookGenre) {
		this.bookGenre = bookGenre;
	}
}

Verification of Data in Postgres

Before execution of Program:

On executing the above program, all 4 CRUD operations are performed at once. Let us verify the update in Postgres db:

Conclusion

This article gives an understanding on how Springboot can be integrated with PostgreSQL database and how the basic CRUD operations can be executed in an Springboot application using JDBC template. We hope this article will help you get started on your Springboot - PostgreSQL journey.