Spring Boot PostgreSQL CRUD Example

In this tutorial, we’ll learn how to configure Spring Boot to use the PostgreSQL database and build a RESTful CRUD API from scratch.

You’ll also learn how Spring Data JPA and Hibernate can be used with PostgreSQL.

1. Create Spring Boot Project

Spring Boot provides a web tool called Spring Initializer to bootstrap an application quickly. Just go to https://start.spring.io/ and generate a new spring boot project.

Use the below details in the Spring boot creation:

Project Name: springboot-postgresql-hibernate-crud-example

Project Type: Maven

Choose dependencies: Spring Web, Spring Data JPA, PostgreSQL Driver

Package name: com.sourcecodeexamples.springboot

2. Maven Dependencies

Open the pom.xml file and replace it with the following content:
<?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>

	<groupId>net.guides.springboot2</groupId>
	<artifactId>springboot-postgresql-hibernate-crud-example</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>springboot-postgresql-hibernate-crud-example</name>
	<description>Spring Boot, PostgreSQL, JPA, Hibernate RESTful CRUD API example</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.5.RELEASE</version>
		<relativePath /> <!-- lookup parent from reposictory -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

3. Configure PostgreSQL Database

Spring Boot tries to auto-configure a DataSource if spring-data-jpa dependency is in the classpath by reading the database configuration from the application.properties file.

So, we just have to add the configuration, and Spring Boot will take care of the rest.

Open the application.properties file and add the following properties to it.


## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres_demo
spring.datasource.username=posgres
spring.datasource.password=postgres

# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

You will need to create a database named postgres_demo in PostgreSQL, and change the spring.datasource.username & spring.datasource.password properties as per your PostgreSQL installation.

In the above properties file, the last two properties are for Hibernate. Spring Boot uses Hibernate as the default JPA implementation.

The property spring.jpa.hibernate.ddl-auto is used to automatically create the tables based on the entity classes in the application.

4. Create JPA Entity

Let's create a new package called entity inside com.sourcecodeexamples.springboot and add a class named Employee.java with the following contents:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "employees")
public class Employee {

	private long id;
	private String firstName;
	private String lastName;
	private String emailId;
	
	public Employee() {
		
	}
	
	public Employee(String firstName, String lastName, String emailId) {
		this.firstName = firstName;
		this.lastName = lastName;
		this.emailId = emailId;
	}
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	
	@Column(name = "first_name", nullable = false)
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	
	@Column(name = "last_name", nullable = false)
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	
	@Column(name = "email_address", nullable = false)
	public String getEmailId() {
		return emailId;
	}
	public void setEmailId(String emailId) {
		this.emailId = emailId;
	}

	@Override
	public String toString() {
		return "Employee [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", emailId=" + emailId
				+ "]";
	}	
}

5. Create Repository or DAO Layer

The next thing we’re gonna do is create a repository to access Employee’s data from the database.

The JpaRepository interface defines methods for all the CRUD operations on the entity, and a default implementation of the JpaRepository called SimpleJpaRepository.

Let’s create the repository now. First, create a new package called repository inside the base package com.sourcecodeexamples.springboot. Then, create an interface called EmployeeRepository and extend it from JpaRepository -

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.sourcecodeexamples.springboot.entity.Employee;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long>{

}

6. Defining Custom Exception

Let's create a package called exception inside package com.sourcecodeexamples.springboot. 

Let's create an ErrorDetails class with the following contents:

import java.util.Date;

public class ErrorDetails {
	private Date timestamp;
	private String message;
	private String details;

	public ErrorDetails(Date timestamp, String message, String details) {
		super();
		this.timestamp = timestamp;
		this.message = message;
		this.details = details;
	}

	public Date getTimestamp() {
		return timestamp;
	}

	public String getMessage() {
		return message;
	}

	public String getDetails() {
		return details;
	}
}
Let's create a GlobalExceptionHandler class with the following contents:
import java.util.Date;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.context.request.WebRequest;
import org.springframework.web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler;

@ControllerAdvice
public class GlobalExceptionHandler extends ResponseEntityExceptionHandler {
	@ExceptionHandler(ResourceNotFoundException.class)
	public ResponseEntity<?> resourceNotFoundException(ResourceNotFoundException ex, WebRequest request) {
		ErrorDetails errorDetails = new ErrorDetails(new Date(), ex.getMessage(), request.getDescription(false));
		return new ResponseEntity<>(errorDetails, HttpStatus.NOT_FOUND);
	}

	@ExceptionHandler(Exception.class)
	public ResponseEntity<?> globleExcpetionHandler(Exception ex, WebRequest request) {
		ErrorDetails errorDetails = new ErrorDetails(new Date(), ex.getMessage(), request.getDescription(false));
		return new ResponseEntity<>(errorDetails, HttpStatus.INTERNAL_SERVER_ERROR);
	}
}
Let's create a ResourceNotFoundException class with the following contents:
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ResponseStatus;

@ResponseStatus(value = HttpStatus.NOT_FOUND)
public class ResourceNotFoundException extends Exception{

	private static final long serialVersionUID = 1L;

	public ResourceNotFoundException(String message){
    	super(message);
    }
}

7. Controller Layer

We’ll now create the REST APIs for creating, retrieving, updating, and deleting an Employee.

First, create a new package controller inside base package com.sourcecodeexamples.springboot. Then, create a new class EmployeeController.java with the following contents -

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.sourcecodeexamples.springboot.exception.ResourceNotFoundException;
import com.sourcecodeexamples.springboot.entity.Employee;
import com.sourcecodeexamples.springboot.repository.EmployeeRepository;
@RestController @RequestMapping("/api/v1") public class EmployeeController { @Autowired private EmployeeRepository employeeRepository; @GetMapping("/employees") public List<Employee> getAllEmployees() { return employeeRepository.findAll(); } @GetMapping("/employees/{id}") public ResponseEntity<Employee> getEmployeeById(@PathVariable(value = "id") Long employeeId) throws ResourceNotFoundException { Employee employee = employeeRepository.findById(employeeId) .orElseThrow(() -> new ResourceNotFoundException("Employee not found for this id :: " + employeeId)); return ResponseEntity.ok().body(employee); } @PostMapping("/employees") public Employee createEmployee(@Valid @RequestBody Employee employee) { return employeeRepository.save(employee); } @PutMapping("/employees/{id}") public ResponseEntity<Employee> updateEmployee(@PathVariable(value = "id") Long employeeId, @Valid @RequestBody Employee employeeDetails) throws ResourceNotFoundException { Employee employee = employeeRepository.findById(employeeId) .orElseThrow(() -> new ResourceNotFoundException("Employee not found for this id :: " + employeeId)); employee.setEmailId(employeeDetails.getEmailId()); employee.setLastName(employeeDetails.getLastName()); employee.setFirstName(employeeDetails.getFirstName()); final Employee updatedEmployee = employeeRepository.save(employee); return ResponseEntity.ok(updatedEmployee); } @DeleteMapping("/employees/{id}") public Map<String, Boolean> deleteEmployee(@PathVariable(value = "id") Long employeeId) throws ResourceNotFoundException { Employee employee = employeeRepository.findById(employeeId) .orElseThrow(() -> new ResourceNotFoundException("Employee not found for this id :: " + employeeId)); employeeRepository.delete(employee); Map<String, Boolean> response = new HashMap<>(); response.put("deleted", Boolean.TRUE); return response; } }

8. Run Spring Boot Application

We’ve successfully built all the APIs for our application. Let’s now run the app and test the APIs.

Just go to the root directory of the application and type the following command to run it -

$ mvn spring-boot:run

The application will start at Spring Boot’s default tomcat port 8080.

9. Testing REST APIs via Postman Client

1. Create Employee REST API


HTTP Method: POST 
Note that request and response JSON in the above diagram, the response contains database auto-generated id.

2. Get Employee by ID REST API

HTTP Method: GET 

3. Get all Employees REST API

HTTP Method: GET 

4. Update Employee REST API

HTTP Method: GET 

5. Delete Employee REST API

HTTP Method: DELETE 


You can download the source code of this tutorial from my GitHub repository at https://github.com/RameshMF/springboot-postgresql-hibernate-crud-example


Comments