In this tutorial, we are going to see the Spring Boot PostgreSQL DB CRUD example application.

Versions:

• • Spring Boot 2.5.0
  • Java 14
  • PostgreSQL 13.3

• Spring JPA 2.5.2

Spring Boot PostgreSQL:

This tutorial, assuming that you have installed and configure PostgreSQL on your machine.

Application Structure:

Here is the final application structure, as part of this example we are going to go complete CRUD operations with PostgreSQL.

Dependencies:

Add the PostgreSQL dependency in the pom.xmlfile.

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <scope>runtime</scope>
</dependency>

Complete pom.xml file

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.5.0</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.onlinetutorialspoint</groupId>
  <artifactId>Spring-Boot-PostgresSQL-Example</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>Spring-Boot-PostgresSQL-Example</name>
  <description>Demo project for Spring Boot</description>
  <properties>
    <java.version>14</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <scope>runtime</scope>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
</project>

In the above pom.xmlfile you may be noticed that I have included the spring-boot-data-jpadependency, the intention of this is to avoid  the bare hibernate/jdbc and take advantage of Spring Data JPA

Prepare PostgreSQL DB:

Connect to your local PostgreSQL DB and run the below commands to create a new DB.

% psql postgres -U otp      # connecting postgres
psql (13.3)
Type "help" for help.

postgres=> create database products;   # creating products db
CREATE DATABASE

postgres=>\connect products     # switch to products db
You are now connected to database "products" as user "otp".
products->

Now we are ready with products database, now let’s configure this DB in the application.

Configure the PostgreSQL:

We can configure the DB properties within the application either with .properties file or .yamlnow I am going with the application.properties file, here it is.

spring.datasource.url=jdbc:postgresql://localhost:5432/products
spring.datasource.username=otp
spring.datasource.password=123456
spring.jpa.show-sql=true


spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

spring.jpa.hibernate.ddl-auto=update

Create Model:

Create a database model class

package com.onlinetutorialspoint.model;

import javax.persistence.*;

@Entity
@Table(name = "items")
public class Item {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(name="item_name")
    private String itemName;
    @Column(name="item_category")
    private String category;

    public Item() {
    }

    public Item(String itemName, String category) {
        this.itemName = itemName;
        this.category = category;
    }
    public long getId() {
        return id;
    }

    public String getItemName() {
        return itemName;
    }

    public String getCategory() {
        return category;
    }
}

The above Item class is an ORM representation of item table in the database.

Rest Controller:

Crate rest controller that provides all CRUD operations for itemtable

package com.onlinetutorialspoint.controller;

import com.onlinetutorialspoint.model.Item;
import com.onlinetutorialspoint.repos.ItemRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.util.UriComponentsBuilder;

import java.util.List;
import java.util.Optional;

@RestController
@RequestMapping("/api/v1")
public class ItemController {
    @Autowired
    ItemRepository itemRepo;

    @RequestMapping("/items")
    @ResponseBody
    public ResponseEntity<List<Item>> getAllItems(){
        List<Item> items =  itemRepo.findAll();
        return new ResponseEntity<List<Item>>(items, HttpStatus.OK);
    }

    @GetMapping("/item/{itemId}")
    @ResponseBody
    public ResponseEntity<Item> getItem(@PathVariable Long itemId){
        Optional<Item> item = itemRepo.findById(itemId);
        return new ResponseEntity<Item>(item.get(), HttpStatus.OK);
    }

    @PostMapping(value = "/add",consumes = {"application/json"},produces = {"application/json"})
    @ResponseBody
    public ResponseEntity<Item> addItem(@RequestBody Item item, UriComponentsBuilder builder){
        itemRepo.save(item);
        HttpHeaders headers = new HttpHeaders();
        headers.setLocation(builder.path("/addItem/{id}").buildAndExpand(item.getId()).toUri());
        return new ResponseEntity<Item>(headers, HttpStatus.CREATED);
    }

    @PutMapping("/update")
    @ResponseBody
    public ResponseEntity<Item> updateItem(@RequestBody Item item){
        if(item != null){
            itemRepo.save(item);
        }
        return new ResponseEntity<Item>(item, HttpStatus.OK);
    }

    @DeleteMapping("/delete/{id}")
    @ResponseBody
    public ResponseEntity<Void> deleteItem(@PathVariable Long id){
        Optional<Item> item = itemRepo.findById(id);
        itemRepo.delete(item.get());
        return new ResponseEntity<Void>(HttpStatus.ACCEPTED);
    }
}

Create Repository:

The ItemRepository is a JPA repository, which provides all necessary CRUD methods, to make this happen we need to extend the repository class from JpaRepository class

package com.onlinetutorialspoint.repos;

import com.onlinetutorialspoint.model.Item;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ItemRepository extends JpaRepository<Item, Long> {
}

Create Spring Boot Main class:

This class act as the main class of the Spring Boot.

package com.onlinetutorialspoint;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootPostgresSqlExampleApplication {

  public static void main(String[] args) {
    SpringApplication.run(SpringBootPostgresSqlExampleApplication.class, args);
  }

}

Done!

Run the application:

Spring-Boot-PostgresSQL-Example % mvn clean install  
[INFO] Scanning for projects...
[INFO] 
[INFO] ------< com.onlinetutorialspoint:Spring-Boot-PostgresSQL-Example >------
[INFO] Building Spring-Boot-PostgresSQL-Example 0.0.1-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- maven-clean-plugin:3.1.0:clean (default-clean) @ Spring-Boot-PostgresSQL-Example ---
[INFO] Deleting /Users/chandra/Work/MyWork/Spring-Boot-PostgresSQL-Example/target
[INFO] ....
.....

Run

Spring-Boot-PostgresSQL-Example % mvn spring-boot:run
[INFO] Scanning for projects...
[INFO] 
[INFO] ------< com.onlinetutorialspoint:Spring-Boot-PostgresSQL-Example >------
[INFO] Building Spring-Boot-PostgresSQL-Example 0.0.1-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] >>> spring-boot-maven-plugin:2.5.0:run (default-cli) > test-compile @ Spring-Boot-PostgresSQL-Example >>>
[INFO] 
[INFO] --- maven-resources-plugin:3.2.0:resources (default-resources) @ Spring-Boot-PostgresSQL-Example ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Using 'UTF-8' encoding to copy filtered properties files.
[INFO] Copying 1 resource
...
...
2021-07-21 19:32:31.928 INFO 56034 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2021-07-21 19:32:31.936 INFO 56034 --- [ main] .SpringBootPostgresSqlExampleApplication : Started SpringBootPostgresSqlExampleApplication in 3.16 seconds (JVM running for 3.47)
2021-07-21 19:32:31.937 INFO 56034 --- [ main] o.s.b.a.ApplicationAvailabilityBean : Application availability state LivenessState changed to CORRECT
2021-07-21 19:32:31.938 INFO 56034 --- [ main] o.s.b.a.ApplicationAvailabilityBean : Application availability state ReadinessState changed to ACCEPTING_TRAFFIC

You can see the spring boot application port in the server logs above, try to add some items into the PostgreSQL database now.

Adding Items:

Adding Item 2:

List All items:

Updating Item id 2

After Update:

Deleting Item 2:

After deleting item 2:

Final PostgreSQL DB:

postgres=> \connect products
You are now connected to database "products" as user "otp".
products=> select * from items;
 id | item_category |    item_name     
----+---------------+------------------
  1 | Books         | Spring in Action
(1 row)

Done!

References:

• Install PostgreSQL on Mac
• Hibernate Dialects
• PostgreSQL

Happy Learning 🙂