Table of Contents

1. Introduction
2. Prerequisites
3. Configuration: Connecting Spring Boot to PostgreSQL
4. Creating the DAO (Data Access Object)
5. Building the Service Layer
6. Crafting the Controller
7. Designing the Entity Class
8. Conclusion
9. Resources

1. Introduction

In the modern world of application development, seamless integration between databases and backend frameworks is essential. This article presents an in-depth walkthrough of connecting PostgreSQL, a robust open-source relational database, with Spring Boot, a widely-used Java-based framework. By the end of this guide, you’ll be adept at fetching data from a PostgreSQL database using the Spring Boot framework.

2. Prerequisites

Before we delve into the integration process, ensure the following prerequisites are met:

1. PostgreSQL database is installed and operational on your local machine.
2. A Spring Boot project has been created with the necessary dependencies.

go to https://start.spring.io/ and generate a new spring boot project,

Project Structure

3. Configuration: Connecting Spring Boot to PostgreSQL

To initiate the connection between Spring Boot and PostgreSQL, follow these configuration steps in application.properties

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/questiondb
spring.datasource.username=madhura
spring.datasource.password=1234
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

Replace the values with your specific database credentials and connection details.

4. Creating the DAO (Data Access Object)

The DAO layer is pivotal for database interaction. Here’s how you can create the QuestionDao interface using JpaRepository:

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

@Repository
public interface QuestionDao extends JpaRepository {
}

5. Building the Service Layer

The service layer acts as a bridge between the DAO and the controller. Create the QuestionService class:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class QuestionService {
    @Autowired
    QuestionDao questionDao;
    public List getAllQuestions() {
        return questionDao.findAll();
    }
}

6. Crafting the Controller

The controller defines API endpoints and manages incoming HTTP requests. Construct the QuestionController:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
@RequestMapping("question")
public class QuestionController {
    @Autowired
    QuestionService questionService;
    @GetMapping("allQuestions")
    public List getAllQuestions(){
        return questionService.getAllQuestions();
    }
}

7. Designing the Entity Class

The entity class maps to a database table. Create the Question class and annotate it with @Entity:

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;

@Entity
@Data
public class Question {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Integer id;
    private String questionTitle;
    private String option1;
    private String option2;
    private String option3;
    private String rightAnswer;
    private String difficultylevel;
}

8. Conclusion

After all of the above steps run the spring boot project and visit: localhost:8080/question/allQuestions

Congratulations! You’ve successfully integrated PostgreSQL with Spring Boot to retrieve data from a database. This tutorial covered the configuration, DAO, service, controller, and entity class required for seamless integration. Feel free to adapt and expand upon this foundation to develop sophisticated applications that leverage relational databases and Spring Boot’s capabilities.

Embrace this knowledge as you embark on your journey to create robust, data-driven Spring Boot applications. Happy coding!

9. Resources

Sample Data for Database:

INSERT INTO public.question(id, category, difficultylevel, option1, option2, option3, question_title, right_answer)
VALUES
    (1, 'java', 'easy', 'Java is an object-oriented programming language.', 'Java was developed by James Gosling.', 'Java is primarily used for Android app development.', 'Java Basics', 'Java is an object-oriented programming language.'),
    (2, 'js', 'medium', 'JavaScript is a scripting language used for web development.', 'JavaScript was first released in 1995 by Netscape.', 'JavaScript is a compiled language.', 'JavaScript Origins', 'JavaScript was first released in 1995 by Netscape.'),
    (3, 'python', 'hard', 'Python is known for its clean and readable syntax.', 'Python was named after the British comedy group Monty Python.', 'Python is only used for scientific computing.', 'Python Trivia', 'Python was named after the British comedy group Monty Python.'),
    (4, 'java', 'medium', 'Java applications are platform-independent.', 'Java was initially called Oak.', 'Java doesn’t support multithreading.', 'Java Platform', 'Java applications are platform-independent.'),
    (5, 'js', 'easy', 'JavaScript can be used for both front-end and back-end development.', 'JavaScript is a statically typed language.', 'JavaScript was originally developed by Microsoft.', 'JavaScript Usage', 'JavaScript can be used for both front-end and back-end development.'),
    (6, 'python', 'medium', 'Python is often used for data analysis and machine learning.', 'Python is a compiled language.', 'Python is not open-source.', 'Python Applications', 'Python is often used for data analysis and machine learning.'),
    (7, 'java', 'hard', 'Java was influenced by the C++ programming language.', 'Java was developed by a team of researchers at IBM.', 'Java is no longer widely used.', 'Java History', 'Java was influenced by the C++ programming language.'),
    (8, 'js', 'easy', 'JavaScript is commonly used to add interactivity to websites.', 'JavaScript was initially called LiveScript.', 'JavaScript is only used on desktop applications.', 'JavaScript Purpose', 'JavaScript is commonly used to add interactivity to websites.'),
    (9, 'python', 'medium', 'Python was created by Guido van Rossum.', 'Python is not suitable for web development.', 'Python is not an open-source language.', 'Python Creator', 'Python was created by Guido van Rossum.'),
    (10, 'java', 'hard', 'Java applications are compiled to bytecode.', 'Java is a dynamically typed language.', 'Java was originally developed at Microsoft.', 'Java Compilation', 'Java applications are compiled to bytecode.'),
    (11, 'js', 'easy', 'JavaScript code can be embedded directly into HTML pages.', 'JavaScript has no built-in data types.', 'JavaScript is used only for server-side scripting.', 'JavaScript Embedding', 'JavaScript code can be embedded directly into HTML pages.'),
    (12, 'python', 'medium', 'Python has a large standard library that includes modules for various tasks.', 'Python does not support object-oriented programming.', 'Python is a functional programming language.', 'Python Standard Library', 'Python has a large standard library that includes modules for various tasks.'),
    (13, 'java', 'easy', 'Java applications are compiled into machine code.', 'Java was first released in 2001.', 'Java is primarily used for scientific research.', 'Java Compilation', 'Java applications are compiled into machine code.'),
    (14, 'js', 'medium', 'JavaScript is often used with libraries like React and Angular for building user interfaces.', 'JavaScript is a low-level language.', 'JavaScript has limited browser compatibility.', 'JavaScript Libraries', 'JavaScript is often used with libraries like React and Angular for building user interfaces.'),
    (15, 'python', 'hard', 'Python is used by NASA for space exploration.', 'Python was originally developed for numerical computing.', 'Python has no support for third-party packages.', 'Python Applications', 'Python is used by NASA for space exploration.'),
    (16, 'java', 'medium', 'Java supports multithreading for concurrent programming.', 'Java was developed by Microsoft.', 'Java applications cannot run on Windows.', 'Java Multithreading', 'Java supports multithreading for concurrent programming.');

A Beginner’s Guide to Connecting PostgreSQL with Spring Boot to Retrieve Data was originally published in Enlear Academy on Medium, where people are continuing the conversation by highlighting and responding to this story.