Integrating PostgreSQL with Spring Boot: Complete Guide

PostgreSQL Integration with Spring Boot: A Comprehensive Guide

Spring Boot simplifies the development of Java-based applications by providing a framework with pre-configured tools. PostgreSQL, a powerful open-source relational database, is commonly used as the database for Spring Boot applications. This guide explains how to integrate PostgreSQL with Spring Boot, including configuration, examples, and advanced usage for database interaction.

Steps to Configure PostgreSQL with Spring Boot

1. Add PostgreSQL Dependency

To use PostgreSQL, include the dependency in your pom.xml (Maven) or build.gradle (Gradle) file.

For Maven:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.5.0</version>
</dependency>

For Gradle:

implementation 'org.postgresql:postgresql:42.5.0'

2. Configure application.properties

Spring Boot uses application.properties or application.yml to manage database configurations.

Example (application.properties):

# PostgreSQL database configuration
spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.datasource.username=postgres
spring.datasource.password=yourpassword
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=update

Explanation:

• spring.datasource.url: Specifies the database URL.
• spring.datasource.username and spring.datasource.password: Database credentials.
• spring.jpa.properties.hibernate.dialect: Hibernate's PostgreSQL dialect for SQL generation.
• spring.jpa.hibernate.ddl-auto: Specifies schema generation strategy (update, create, etc.).

3. Create an Entity Class

Map your database tables to Java objects using the JPA @Entity annotation.

Example:

Code:

package com.example.demo.entity;

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

@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String role;

    // Getters and Setters
}

Explanation

• @Entity: Marks the class as a database entity.
• @Id: Specifies the primary key.
• @GeneratedValue: Auto-generates the primary key value.

4. Create a Repository

Use Spring Data JPA to interact with the database without writing SQL queries.

Example:

Code:

package com.example.demo.repository;

import com.example.demo.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

Explanation:

• Extending JpaRepository provides CRUD operations for the Employee entity.

5. Write a Controller

Create REST endpoints to perform database operations.

Example:

Code:

package com.example.demo.controller;

import com.example.demo.entity.Employee;
import com.example.demo.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/employees")
public class EmployeeController {
    @Autowired
    private EmployeeRepository repository;

    @PostMapping
    public Employee createEmployee(@RequestBody Employee employee) {
        return repository.save(employee);
    }

    @GetMapping
    public List<Employee> getAllEmployees() {
        return repository.findAll();
    }
}

Explanation:

• @RestController: Defines the class as a REST controller.
• @PostMapping and @GetMapping: Map HTTP methods to methods for creating and retrieving employees.

Example Run

1. Start PostgreSQL: Ensure your PostgreSQL server is running, and a database (mydb) is created.

2. Run Spring Boot Application: Start the Spring Boot application. The Employee table will be created automatically if spring.jpa.hibernate.ddl-auto=update is set.

3. Test the Endpoints:

• Use tools like Postman or cURL to interact with the API.
• POST request to create an employee:

Code:

POST /api/employees
{
  "name": "Jana Isabel",
  "role": "Developer"
}

• GET request to fetch all employees:

Code:

GET /api/employees

Advanced Configuration

1. Connection Pooling: Use HikariCP, the default connection pool for Spring Boot, for efficient database connections.

Code:

spring.datasource.hikari.maximum-pool-size=10

2. Database Initialization: Use data.sql for preloading data.

Code:

INSERT INTO employee (name, role) VALUES ('Alice', 'Manager');

3. Custom Query: Add custom queries to your repository:

Code:

@Query("SELECT e FROM Employee e WHERE e.role = ?1")
List<Employee> findByRole(String role);

All PostgreSQL Questions, Answers, and Code Snippets Collection.