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.xml
file.
<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.xml
file you may be noticed that I have included the spring-boot-data-jpa
dependency, 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 .yaml
now 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 item
table
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:
Happy Learning 🙂