Spring Boot Database Integration with JPA & PostgreSQL

Introduction

Database integration is a fundamental aspect of most Spring Boot applications. Spring Data JPA, combined with Hibernate ORM, provides a powerful and elegant way to interact with relational databases without writing boilerplate SQL code.

In this comprehensive guide, we'll cover:

Setting up Spring Data JPA with PostgreSQL
Entity mapping and relationships
Repository patterns and custom queries
Database migrations with Flyway
Best practices for production applications

Prerequisites: This tutorial builds on Getting Started with Spring Boot. Make sure you have Java and Spring Boot set up before proceeding.

Why Spring Data JPA?

Spring Data JPA simplifies database access by:

Reducing boilerplate: No need to write CRUD operations manually
Type safety: Compile-time checking for queries
Automatic implementation: Repository interfaces are implemented automatically
Powerful query methods: Derive queries from method names
Integration: Seamless integration with Spring's transaction management

Setting Up the Database

1. Install PostgreSQL

macOS (Homebrew):

brew install postgresql@15
brew services start postgresql@15

Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Windows: Download and install from postgresql.org

2. Create a Database

# Connect to PostgreSQL
psql postgres
 
# Create database and user
CREATE DATABASE springboot_demo;
CREATE USER demo_user WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE springboot_demo TO demo_user;
 
# Exit
\q

3. Add Dependencies

Update your pom.xml:

<dependencies>
    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
 
    <!-- PostgreSQL Driver -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
 
    <!-- Validation -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
 
    <!-- Flyway for migrations (optional but recommended) -->
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
</dependencies>

For Gradle (build.gradle):

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'org.postgresql:postgresql'
    implementation 'org.springframework.boot:spring-boot-starter-validation'
    implementation 'org.flywaydb:flyway-core'
}

4. Configure Database Connection

Edit src/main/resources/application.properties:

# Database Connection
spring.datasource.url=jdbc:postgresql://localhost:5432/springboot_demo
spring.datasource.username=demo_user
spring.datasource.password=secure_password
spring.datasource.driver-class-name=org.postgresql.Driver
 
# JPA/Hibernate Properties
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
 
# Flyway Migration
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration

Important Configuration Options:

ddl-auto=validate: Only validate schema, don't auto-create (recommended for production)
ddl-auto=create: Drop and recreate schema on startup (development only)
ddl-auto=update: Update schema automatically (not recommended for production)
show-sql=true: Log SQL statements (disable in production)

Creating Your First Entity

Let's create a User entity to represent users in our database.

1. Create the Entity Class

src/main/java/com/example/demo/entity/User.java:

package com.example.demo.entity;
 
import jakarta.persistence.*;
import jakarta.validation.constraints.Email;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.Size;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
 
import java.time.LocalDateTime;
 
@Entity
@Table(name = "users")
public class User {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @NotBlank(message = "Username is required")
    @Size(min = 3, max = 50)
    @Column(unique = true, nullable = false)
    private String username;
 
    @NotBlank(message = "Email is required")
    @Email(message = "Email should be valid")
    @Column(unique = true, nullable = false)
    private String email;
 
    @NotBlank(message = "Password is required")
    @Size(min = 8)
    @Column(nullable = false)
    private String password;
 
    @Column(name = "first_name")
    private String firstName;
 
    @Column(name = "last_name")
    private String lastName;
 
    @Column(nullable = false)
    private Boolean active = true;
 
    @CreationTimestamp
    @Column(name = "created_at", nullable = false, updatable = false)
    private LocalDateTime createdAt;
 
    @UpdateTimestamp
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;
 
    // Constructors
    public User() {}
 
    public User(String username, String email, String password) {
        this.username = username;
        this.email = email;
        this.password = password;
    }
 
    // Getters and Setters
    public Long getId() {
        return id;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getEmail() {
        return email;
    }
 
    public void setEmail(String email) {
        this.email = email;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    public String getFirstName() {
        return firstName;
    }
 
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
 
    public String getLastName() {
        return lastName;
    }
 
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
 
    public Boolean getActive() {
        return active;
    }
 
    public void setActive(Boolean active) {
        this.active = active;
    }
 
    public LocalDateTime getCreatedAt() {
        return createdAt;
    }
 
    public LocalDateTime getUpdatedAt() {
        return updatedAt;
    }
}

Key Annotations:

@Entity: Marks class as JPA entity
@Table: Specifies table name
@Id: Primary key field
@GeneratedValue: Auto-increment strategy
@Column: Column customization (name, constraints)
@CreationTimestamp: Auto-set creation time
@UpdateTimestamp: Auto-update modification time

Creating a Repository

Spring Data JPA repositories provide database operations without writing implementation code.

src/main/java/com/example/demo/repository/UserRepository.java:

package com.example.demo.repository;
 
import com.example.demo.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
 
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;
 
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
 
    // Derived query methods (automatically implemented)
    Optional<User> findByUsername(String username);
 
    Optional<User> findByEmail(String email);
 
    List<User> findByActiveTrue();
 
    List<User> findByFirstNameContainingIgnoreCase(String firstName);
 
    boolean existsByUsername(String username);
 
    boolean existsByEmail(String email);
 
    // Custom JPQL query
    @Query("SELECT u FROM User u WHERE u.createdAt >= :date")
    List<User> findUsersCreatedAfter(@Param("date") LocalDateTime date);
 
    // Native SQL query
    @Query(value = "SELECT * FROM users WHERE active = true AND created_at >= :date",
           nativeQuery = true)
    List<User> findActiveUsersCreatedAfter(@Param("date") LocalDateTime date);
 
    // Count queries
    long countByActiveTrue();
}

Query Method Naming Conventions:

findBy...: Retrieve entities
existsBy...: Check existence (returns boolean)
countBy...: Count matching entities
deleteBy...: Delete matching entities

Keywords:

And, Or: Combine conditions
Between: Range queries
LessThan, GreaterThan: Comparison
Like, Containing: String matching
IgnoreCase: Case-insensitive
OrderBy: Sorting

Creating a Service Layer

src/main/java/com/example/demo/service/UserService.java:

package com.example.demo.service;
 
import com.example.demo.entity.User;
import com.example.demo.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;
 
@Service
@Transactional
public class UserService {
 
    private final UserRepository userRepository;
 
    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }
 
    public User createUser(User user) {
        // Check if username already exists
        if (userRepository.existsByUsername(user.getUsername())) {
            throw new IllegalArgumentException("Username already exists");
        }
 
        // Check if email already exists
        if (userRepository.existsByEmail(user.getEmail())) {
            throw new IllegalArgumentException("Email already exists");
        }
 
        // In production, hash the password before saving
        // user.setPassword(passwordEncoder.encode(user.getPassword()));
 
        return userRepository.save(user);
    }
 
    @Transactional(readOnly = true)
    public Optional<User> getUserById(Long id) {
        return userRepository.findById(id);
    }
 
    @Transactional(readOnly = true)
    public Optional<User> getUserByUsername(String username) {
        return userRepository.findByUsername(username);
    }
 
    @Transactional(readOnly = true)
    public List<User> getAllActiveUsers() {
        return userRepository.findByActiveTrue();
    }
 
    @Transactional(readOnly = true)
    public List<User> searchUsers(String name) {
        return userRepository.findByFirstNameContainingIgnoreCase(name);
    }
 
    public User updateUser(Long id, User updatedUser) {
        User user = userRepository.findById(id)
            .orElseThrow(() -> new IllegalArgumentException("User not found"));
 
        user.setFirstName(updatedUser.getFirstName());
        user.setLastName(updatedUser.getLastName());
        user.setEmail(updatedUser.getEmail());
 
        return userRepository.save(user);
    }
 
    public void deactivateUser(Long id) {
        User user = userRepository.findById(id)
            .orElseThrow(() -> new IllegalArgumentException("User not found"));
 
        user.setActive(false);
        userRepository.save(user);
    }
 
    public void deleteUser(Long id) {
        userRepository.deleteById(id);
    }
 
    @Transactional(readOnly = true)
    public long countActiveUsers() {
        return userRepository.countByActiveTrue();
    }
}

Transaction Management:

@Transactional: Automatic transaction handling
readOnly = true: Optimize read-only operations
Transactions automatically rollback on exceptions

Creating REST API Endpoints

src/main/java/com/example/demo/controller/UserController.java:

package com.example.demo.controller;
 
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import jakarta.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
 
import java.util.List;
 
@RestController
@RequestMapping("/api/users")
public class UserController {
 
    private final UserService userService;
 
    public UserController(UserService userService) {
        this.userService = userService;
    }
 
    @PostMapping
    public ResponseEntity<User> createUser(@Valid @RequestBody User user) {
        User createdUser = userService.createUser(user);
        return ResponseEntity.status(HttpStatus.CREATED).body(createdUser);
    }
 
    @GetMapping("/{id}")
    public ResponseEntity<User> getUser(@PathVariable Long id) {
        return userService.getUserById(id)
            .map(ResponseEntity::ok)
            .orElse(ResponseEntity.notFound().build());
    }
 
    @GetMapping
    public ResponseEntity<List<User>> getAllActiveUsers() {
        List<User> users = userService.getAllActiveUsers();
        return ResponseEntity.ok(users);
    }
 
    @GetMapping("/search")
    public ResponseEntity<List<User>> searchUsers(@RequestParam String name) {
        List<User> users = userService.searchUsers(name);
        return ResponseEntity.ok(users);
    }
 
    @PutMapping("/{id}")
    public ResponseEntity<User> updateUser(@PathVariable Long id,
                                          @Valid @RequestBody User user) {
        User updatedUser = userService.updateUser(id, user);
        return ResponseEntity.ok(updatedUser);
    }
 
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteUser(@PathVariable Long id) {
        userService.deleteUser(id);
        return ResponseEntity.noContent().build();
    }
 
    @GetMapping("/count")
    public ResponseEntity<Long> countActiveUsers() {
        long count = userService.countActiveUsers();
        return ResponseEntity.ok(count);
    }
}

Database Migrations with Flyway

Flyway manages database schema versions and migrations safely.

1. Create Migration Files

Create directory: src/main/resources/db/migration/

V1__Create_users_table.sql:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);
 
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active);

V2__Add_user_roles.sql:

CREATE TABLE roles (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(255)
);
 
CREATE TABLE user_roles (
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);
 
INSERT INTO roles (name, description) VALUES
    ('USER', 'Regular user'),
    ('ADMIN', 'Administrator');

2. Migration Naming Convention

Format: V{version}__{description}.sql

V1__Initial_schema.sql
V2__Add_indexes.sql
V3__Add_user_roles.sql

Flyway executes migrations in order and tracks applied migrations in flyway_schema_history table.

Entity Relationships

One-to-Many Relationship

Post.java:

@Entity
@Table(name = "posts")
public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    private String title;
 
    @Column(columnDefinition = "TEXT")
    private String content;
 
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    private User author;
 
    @CreationTimestamp
    private LocalDateTime createdAt;
 
    // Getters and setters
}

Update User.java:

@OneToMany(mappedBy = "author", cascade = CascadeType.ALL, orphanRemoval = true)
private List<Post> posts = new ArrayList<>();

Many-to-Many Relationship

@Entity
@Table(name = "users")
public class User {
    // ... existing fields
 
    @ManyToMany
    @JoinTable(
        name = "user_roles",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private Set<Role> roles = new HashSet<>();
}
 
@Entity
@Table(name = "roles")
public class Role {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    private String name;
 
    @ManyToMany(mappedBy = "roles")
    private Set<User> users = new HashSet<>();
}

Fetch Types:

LAZY: Load relationship only when accessed (default for collections)
EAGER: Load relationship immediately (can cause N+1 query problems)

Testing Database Operations

src/test/java/com/example/demo/repository/UserRepositoryTest.java:

@SpringBootTest
@Transactional
class UserRepositoryTest {
 
    @Autowired
    private UserRepository userRepository;
 
    @Test
    void testCreateAndFindUser() {
        User user = new User("testuser", "test@example.com", "password123");
        user.setFirstName("Test");
        user.setLastName("User");
 
        User savedUser = userRepository.save(user);
 
        assertNotNull(savedUser.getId());
 
        Optional<User> foundUser = userRepository.findByUsername("testuser");
        assertTrue(foundUser.isPresent());
        assertEquals("test@example.com", foundUser.get().getEmail());
    }
 
    @Test
    void testFindActiveUsers() {
        userRepository.save(new User("user1", "user1@example.com", "pass"));
 
        User inactiveUser = new User("user2", "user2@example.com", "pass");
        inactiveUser.setActive(false);
        userRepository.save(inactiveUser);
 
        List<User> activeUsers = userRepository.findByActiveTrue();
 
        assertEquals(1, activeUsers.size());
        assertEquals("user1", activeUsers.get(0).getUsername());
    }
}

Best Practices

1. Use DTOs for API Responses

Don't expose entities directly:

public class UserDTO {
    private Long id;
    private String username;
    private String email;
    private String firstName;
    private String lastName;
 
    // No password field!
    // Constructor, getters, setters
}

2. Handle N+1 Query Problems

Use @EntityGraph or JOIN FETCH:

@Query("SELECT u FROM User u LEFT JOIN FETCH u.posts WHERE u.id = :id")
Optional<User> findByIdWithPosts(@Param("id") Long id);

3. Use Projections for Read Operations

public interface UserSummary {
    Long getId();
    String getUsername();
    String getEmail();
}
 
List<UserSummary> findAllBy();

4. Connection Pooling

Configure HikariCP in application.properties:

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000

5. Never Store Plain Passwords

Use Spring Security's PasswordEncoder:

@Autowired
private PasswordEncoder passwordEncoder;
 
user.setPassword(passwordEncoder.encode(rawPassword));

6. Use Proper Indexing

Add indexes for frequently queried columns:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

7. Pagination for Large Results

Page<User> findByActiveTrue(Pageable pageable);
 
// Usage
Pageable pageable = PageRequest.of(0, 20, Sort.by("createdAt").descending());
Page<User> users = userRepository.findByActiveTrue(pageable);

Common Issues and Solutions

Issue: LazyInitializationException

Problem: Accessing lazy-loaded relationship outside transaction.

Solution: Use @Transactional or fetch eagerly when needed.

Issue: N+1 Query Problem

Problem: One query for parent, N queries for children.

Solution: Use JOIN FETCH or @EntityGraph.

Issue: Database Lock Timeouts

Problem: Long-running transactions.

Solution: Keep transactions short, use optimistic locking.

Conclusion

Spring Data JPA with Hibernate provides a powerful abstraction for database operations in Spring Boot applications. Key takeaways:

Use Spring Data JPA repositories for automatic CRUD operations
Leverage Flyway for database migrations
Follow best practices: use DTOs, handle lazy loading, implement pagination
Test database operations thoroughly
Monitor and optimize query performance

In the next tutorial, we'll cover Spring Security integration to secure these endpoints with authentication and authorization.