Spring Boot, PostgreSQL, JPA, Hibernate RESTful CRUD API Example

In this article, you’ll learn how to configure Spring Boot to use 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.

We’ll write REST APIs for a Q&A application like Quora. The Q&A application will have two domain models - Question and Answer. Since one question can have many answers, we’ll define a one-to-many relationship between the Question and the Answer entity.

We’ll first bootstrap the project and configure PostgreSQL database. After that, we’ll define the domain models and repositories for accessing the data from PostgreSQL. Finally, we’ll write the REST APIs and test those APIs using Postman.

Cool, Let’s get started!

Bootstrapping the Project

You can bootstrap the Spring Boot project using Spring CLI tool by typing the following command in the terminal.

$ spring init --name=postgres-demo --dependencies=web,jpa,postgresql,validation postgres-demo

Alternatively, you can generate the project using Spring Initializr web tool by following the instructions below -

Head over to http://start.spring.io.
Enter postgres-demo in the Artifact field.
Enter com.example.postgresdemo in the Package field.
Add Web, JPA, PostgreSQL, and Validation in the dependencies section.
Click Generate to download the project.

That’s it! You may now import the project into your favorite IDE and start working.

Directory Structure of the Project

I am including the complete directory structure of the project here for your reference. We’ll be creating the required packages and classes one by one in the coming sections.

Configuring PostgreSQL

First thing First. Let’s configure Spring Boot to use PostgreSQL as our data source. You can do that simply by adding PostgreSQL database url, username, and password in the src/main/resources/application.properties file -

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

# 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

The last two properties in the above file are for Hibernate. It is the default JPA vendor that comes with spring-data-jpa.

Although Hibernate is database agnostic, we can specify the current database dialect to let it generate better SQL queries for that database.

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

Defining the domain models

The domain models are the classes that are mapped to the corresponding tables in the database. We have two main domain models in our application - Question and Answer. Both these domain models will have some common auditing related fields like createdAt and updatedAt.

It’s better to abstract out these common fields in a separate base class, and that’s what we’ll be doing in this post. We’ll create an abstract class called AuditModel that will hold these fields.

Moreover, we’ll use Spring Boot’s JPA Auditing feature to automatically populate createdAt and updatedAt values when a particular entity is inserted/updated in the database.

1. AuditModel

The following AuditModel class will be extended by other entities. It contains @EntityListeners(AuditingEntityListener.class) annotation that will automatically populate createdAt and updatedAt values when the entities are persisted.

package com.example.postgresdemo.model;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(
        value = {"createdAt", "updatedAt"},
        allowGetters = true
)
public abstract class AuditModel implements Serializable {
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "created_at", nullable = false, updatable = false)
    @CreatedDate
    private Date createdAt;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "updated_at", nullable = false)
    @LastModifiedDate
    private Date updatedAt;

    // Getters and Setters (Omitted for brevity)
}

Enable JPA Auditing

Note that, To enable JPA Auditing, you need to add the @EnableJpaAuditing annotation to one of the configuration classes. So open the main class PostgresDemoApplication.java and add the @EnableJpaAuditing annotation like so -

package com.example.postgresdemo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
@EnableJpaAuditing
public class PostgresDemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(PostgresDemoApplication.class, args);
    }
}

2. Question model

Following is the Question entity. It is mapped to a table named questions in the database.

package com.example.postgresdemo.model;

import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

@Entity
@Table(name = "questions")
public class Question extends AuditModel {
    @Id
    @GeneratedValue(generator = "question_generator")
    @SequenceGenerator(
            name = "question_generator",
            sequenceName = "question_sequence",
            initialValue = 1000
    )
    private Long id;

    @NotBlank
    @Size(min = 3, max = 100)
    private String title;

    @Column(columnDefinition = "text")
    private String description;

    // Getters and Setters (Omitted for brevity)
}

Note that I’m using a @SequenceGenerator to generate the question’s id. You could also use PostgreSQL SERIAL column by specifying @GeneratedValue(strategy=GenerationType.IDENTITY) annotation. But a SequenceGenerator performs better in this case.

3. Answer model

Following is the definition of Answer class. It contains a @ManyToOne annotation to declare that it has a many-to-one relationship with the Question entity.

package com.example.postgresdemo.model;

import com.fasterxml.jackson.annotation.JsonIgnore;
import javax.persistence.*;
import org.hibernate.annotations.OnDelete;
import org.hibernate.annotations.OnDeleteAction;

@Entity
@Table(name = "answers")
public class Answer extends AuditModel {
    @Id
    @GeneratedValue(generator = "answer_generator")
    @SequenceGenerator(
            name = "answer_generator",
            sequenceName = "answer_sequence",
            initialValue = 1000
    )
    private Long id;

    @Column(columnDefinition = "text")
    private String text;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "question_id", nullable = false)
    @OnDelete(action = OnDeleteAction.CASCADE)
    @JsonIgnore
    private Question question;

    // Getters and Setters (Omitted for brevity)
}

Defining the Repositories

The following repositories will be used to access the questions and answers from the database.

1. QuestionRepository

package com.example.postgresdemo.repository;

import com.example.postgresdemo.model.Question;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface QuestionRepository extends JpaRepository<Question, Long> {
}

2. AnswerRepository

package com.example.postgresdemo.repository;

import com.example.postgresdemo.model.Answer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface AnswerRepository extends JpaRepository<Answer, Long> {
    List<Answer> findByQuestionId(Long questionId);
}

Building the REST APIs

Finally, Let’s write the REST APIs inside the controllers for performing CRUD operations on questions and answers.

1. QuestionController

package com.example.postgresdemo.controller;

import com.example.postgresdemo.exception.ResourceNotFoundException;
import com.example.postgresdemo.model.Question;
import com.example.postgresdemo.repository.QuestionRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import javax.validation.Valid;

@RestController
public class QuestionController {

    @Autowired
    private QuestionRepository questionRepository;

    @GetMapping("/questions")
    public Page<Question> getQuestions(Pageable pageable) {
        return questionRepository.findAll(pageable);
    }


    @PostMapping("/questions")
    public Question createQuestion(@Valid @RequestBody Question question) {
        return questionRepository.save(question);
    }

    @PutMapping("/questions/{questionId}")
    public Question updateQuestion(@PathVariable Long questionId,
                                   @Valid @RequestBody Question questionRequest) {
        return questionRepository.findById(questionId)
                .map(question -> {
                    question.setTitle(questionRequest.getTitle());
                    question.setDescription(questionRequest.getDescription());
                    return questionRepository.save(question);
                }).orElseThrow(() -> new ResourceNotFoundException("Question not found with id " + questionId));
    }


    @DeleteMapping("/questions/{questionId}")
    public ResponseEntity<?> deleteQuestion(@PathVariable Long questionId) {
        return questionRepository.findById(questionId)
                .map(question -> {
                    questionRepository.delete(question);
                    return ResponseEntity.ok().build();
                }).orElseThrow(() -> new ResourceNotFoundException("Question not found with id " + questionId));
    }
}

2. AnswerController

package com.example.postgresdemo.controller;

import com.example.postgresdemo.exception.ResourceNotFoundException;
import com.example.postgresdemo.model.Answer;
import com.example.postgresdemo.repository.AnswerRepository;
import com.example.postgresdemo.repository.QuestionRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import javax.validation.Valid;
import java.util.List;

@RestController
public class AnswerController {

    @Autowired
    private AnswerRepository answerRepository;

    @Autowired
    private QuestionRepository questionRepository;

    @GetMapping("/questions/{questionId}/answers")
    public List<Answer> getAnswersByQuestionId(@PathVariable Long questionId) {
        return answerRepository.findByQuestionId(questionId);
    }

    @PostMapping("/questions/{questionId}/answers")
    public Answer addAnswer(@PathVariable Long questionId,
                            @Valid @RequestBody Answer answer) {
        return questionRepository.findById(questionId)
                .map(question -> {
                    answer.setQuestion(question);
                    return answerRepository.save(answer);
                }).orElseThrow(() -> new ResourceNotFoundException("Question not found with id " + questionId));
    }

    @PutMapping("/questions/{questionId}/answers/{answerId}")
    public Answer updateAnswer(@PathVariable Long questionId,
                               @PathVariable Long answerId,
                               @Valid @RequestBody Answer answerRequest) {
        if(!questionRepository.existsById(questionId)) {
            throw new ResourceNotFoundException("Question not found with id " + questionId);
        }

        return answerRepository.findById(answerId)
                .map(answer -> {
                    answer.setText(answerRequest.getText());
                    return answerRepository.save(answer);
                }).orElseThrow(() -> new ResourceNotFoundException("Answer not found with id " + answerId));
    }

    @DeleteMapping("/questions/{questionId}/answers/{answerId}")
    public ResponseEntity<?> deleteAnswer(@PathVariable Long questionId,
                                          @PathVariable Long answerId) {
        if(!questionRepository.existsById(questionId)) {
            throw new ResourceNotFoundException("Question not found with id " + questionId);
        }

        return answerRepository.findById(answerId)
                .map(answer -> {
                    answerRepository.delete(answer);
                    return ResponseEntity.ok().build();
                }).orElseThrow(() -> new ResourceNotFoundException("Answer not found with id " + answerId));

    }
}

The custom ResourceNotFoundException class

The Question and Answer REST APIs throw a ResourceNotFoundException when a question or answer was not found in the database. Following is the definition of ResourceNotFoundException class -

package com.example.postgresdemo.exception;

import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ResponseStatus;

@ResponseStatus(HttpStatus.NOT_FOUND)
public class ResourceNotFoundException extends RuntimeException {
    public ResourceNotFoundException(String message) {
        super(message);
    }

    public ResourceNotFoundException(String message, Throwable cause) {
        super(message, cause);
    }
}

The exception class contains a @ResponseStatus(HttpStatus.NOT_FOUND) annotation to tell spring boot to respond with a 404 NOT FOUND status when this exception is thrown.