In this article, I will show you how to configure Spring Boot using PostgreSQL database and build RESTful CRUD API from scratch.
We will also learn how Spring Data JPA and Hibernate can be used with PostgreSQL.
I will write REST APIs for Q&A application. The Q&A application will have 2 domain models: Question and Answer. Because a question will have many answers , I will define the one-to-many between the Question and the Answer entity.
First, I will create the Project and configure PostgreSQL database. Later, I will define domain models and repositories to access data from PostgreSQL. Finally, I will write REST APIs and test APIs using Postman.
I. Initializing Project
In this article, I will initialize the project using the Spring Initializr web tool, follow the instructions below:
First, visit http://start.spring.io
- Enter the postgres-demo into the Artifact field.
- Add Web, JPA, PostgreSQL and Lombok in the Dependencies section.
- Click Generate Project to download the project.
Please import the project into IDE you like and start working. 
Once the directory structure of the project is completed, you can refer to it to create packages and classes accordingly. 
II. PostgreSQL configuration
In order for Spring Boot to use PostgreSQL as a data source, we must configure it by adding driver, url, username and password of PostgreQuery database to src/main/resources/application.properties :
1 | ## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties) spring.datasource.driver-class-name=org.postgresql.Driver spring.datasource.url=jdbc:postgresql://localhost:5432/postgres_demo spring.datasource.username=postgres spring.datasource.password=123456 # The SQL dialect makes Hibernate generate better SQL for the chosen database spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true # Hibernate ddl auto (create, create-drop, validate, update) spring.jpa.hibernate.ddl-auto = update |
III. Identify domain models
Domain models are classes that are mapped to the corresponding tables in the database. We will have two main domain models in our application, Question and Answer . Both domain models will have the same properties as createAt and updateAt . It is best to separate these fields into a separate class. We will create an abstract class called AuditModel to contain these fields. We will also use the JPA Auditing feature of Spring Boot to automatically fill createAt and updateAt .
1. AuditModel
The following AuditModel class will be extended by other entities. It will use annotation @EntityListeners(AuditingEntityListener.class) to automatically fill createAt and updateAt .
1 | package com.example.postgresdemo.model; import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import lombok.Getter; import lombok.Setter; 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 = {"createAt", "updateAt"}, allowGetters = true ) @Getter @Setter public abstract class AuditModel implements Serializable { @Temporal(TemporalType.TIMESTAMP) @Column(name = "create_at", nullable = false, updatable = false) @CreatedDate private Date createAt; @Temporal(TemporalType.TIMESTAMP) @Column(name = "update_at", nullable = false) @LastModifiedDate private Date updateAt; } |
- Note: I am using Lombok's @Getter and @Setter , You need to set your IDE to be able to use Lombok. In case you get an error, please refer to the setting here
Enable JPA Auditing
To enable JPA Auditing, we will need to add @EnableJpaAuditing annotation to one of the config classes. So, open class PostgresDemoApplication.java and add @EnableJpaAuditing annotation as follows:
1 | 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
Below is class Question entity. It is mapped to a table named questions in the database.
1 | package com.example.postgresdemo.model; import lombok.Getter; import lombok.Setter; import javax.persistence.*; import javax.validation.constraints.NotBlank; import javax.validation.constraints.Size; @Entity @Table(name = "questions") @Getter @Setter public class Question extends AuditModel { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @NotBlank @Size(min = 3, max = 100) private String title; @Column(columnDefinition = "text") private String description; } |
3. Answer model
Below is the Answer entity class. It contains an @ManyToOne annotation that declares it has a one-to-one relationship with Question entity.
1 | package com.example.postgresdemo.model; import com.fasterxml.jackson.annotation.JsonIgnore; import lombok.Getter; import lombok.Setter; import org.hibernate.annotations.OnDelete; import org.hibernate.annotations.OnDeleteAction; import javax.persistence.*; @Entity @Table(name = "answers") @Getter @Setter public class Answer extends AuditModel { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) 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; } |
IV. Definition of Repositories classes
The Repository will be used to access Question and Answer from the database.
1. QuestionRepository
1 | 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
1 | 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); } |
V. Building the REST APIs
Finally, let us write the REST APIs inside the controllers to perform CRUD operations for Question and Answer .
1. QuestionController
1 | 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("/question/{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
1 | 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)); } } |
Custom ResourceNotFoundException class
Question and Answer REST APIs will return ResourceNotFoundException when no questions found or answers in the database. Below is the definition of ResourceNotFoundException class.
1 | 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 an @ResponseStatus(HttpStatus.NOT_FOUND) annotation @ResponseStatus(HttpStatus.NOT_FOUND) to alert Spring Boot status of 404 NOT FOUND when this exception is thrown.
VI. Run the application and test the API through Postman
We have completed the REST API building. Run the application and test those APIs. The following screenshots will show you how to test the API with Postman.
1. Create a question: Post / questions
2. Get question information: Get / questions
3. Create an answer: Post / questions / {questionId} / answers
4. Get the Answer information of Question: Get / questions / {questionId} / answers
