06. Spring Boot - Data Access

1. Connecting to the Database in Spring Boot

📄 application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/demo
    username: demo
    password: secret
    driver-class-name: org.postgresql.Driver
  jpa:
    hibernate:
      ddl-auto: update  # create, update, validate, none
      show-sql: true       # show SQL in the console
    properties:
      hibernate.format_sql: true

2. JPA + Hibernate

Spring Data JPA is a wrapper over Hibernate. Allows working with the database as if it were objects.

Entity (Table)

import jakarta.persistence.*;
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(nullable = false)
    private String name;
    private String email;
    // getters/setters
}

Repository (DAO layer)

import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User, Long> {
    // automatic methods by name
    List<User> findByName(String name);
    List<User> findByEmailContaining(String part);
}

Spring will automatically create an implementation based on the method name.

Service Layer

@Service
@RequiredArgsConstructor
public class UserService {
    private final UserRepository repo;
 
    public User createUser(User u) {
        return repo.save(u);
    }
 
    public List<User> allUsers() {
        return repo.findAll();
    }
}

Controller

@RestController
@RequestMapping("/api/users")
@RequiredArgsConstructor
public class UserController {
    private final UserService service;
 
    @GetMapping
    public List<User> all() {
        return service.allUsers();
    }
 
    @PostMapping
    public User create(@RequestBody User user) {
        return service.createUser(user);
    }
}

3. JPQL & Native Queries

📌 If findBy methods are not enough…

public interface UserRepository extends JpaRepository<User, Long> {
    // JPQL (works with Entities, not tables)
    @Query("SELECT u FROM User u WHERE u.name = :name")
    List<User> findByNameJPQL(@Param("name") String name);
    // Native SQL
    @Query(value = "SELECT * FROM users WHERE email LIKE %:email%", nativeQuery = true)
    List<User> findByEmailNative(@Param("email") String email);
}

4. DTO (Data Transfer Object)

To avoid returning Entities directly (dangerous for APIs).

public record UserDto(Long id, String name) {}
@Service
@RequiredArgsConstructor
class UserService {
    private final UserRepository repo;
 
    public List<UserDto> allUsers() {
        return repo.findAll().stream()
                   .map(u -> new UserDto(u.getId(), u.getName()))
                   .toList();
    }
}

5. Transaction Management

Spring Data JPA automatically wraps save/delete methods in transactions, but you can manage them manually.

@Service
public class BankService {
    @Transactional
    public void transfer(Account a1, Account a2, double sum) {
        a1.withdraw(sum);
        a2.deposit(sum);
        // if there is an exception here → the entire method will be rolled back
    }
}

📌 By default: rollback for RuntimeException.

6. Relationships (Entity relationships)

OneToOne

@Entity
class Profile {
    @Id @GeneratedValue Long id;
    String bio;
    @OneToOne
    User user;
}

OneToMany / ManyToOne

@Entity
class User {
    @Id @GeneratedValue Long id;
    String name;
    @OneToMany(mappedBy = "user")
    List<Post> posts = new ArrayList<>();
}
@Entity
class Post {
    @Id @GeneratedValue Long id;
    String text;
    @ManyToOne
    User user;
}

ManyToMany

@Entity
class Student {
    @Id @GeneratedValue Long id;
    String name;
    @ManyToMany
    @JoinTable(name = "student_course")
    Set<Course> courses;
}
@Entity
class Course {
    @Id @GeneratedValue Long id;
    String title;
    @ManyToMany(mappedBy = "courses")
    Set<Student> students;
}

7. Database Migrations (Flyway, Liquibase)

It’s better not to rely on ddl-auto: update and use migrations.

Flyway 📄 application.properties

spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration

📄 src/main/resources/db/migration/V1__init.sql

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

Upon startup, Spring Boot will automatically apply migrations.

8. Paging & Sorting

Spring Data JPA can automatically paginate:

@GetMapping
public Page<User> all(@RequestParam int page, @RequestParam int size) {
    return repo.findAll(PageRequest.of(page, size, Sort.by("name")));
}

📌 The response will be JSON with data and metadata (page number, total number of elements, etc.).

9. Best Practices

✅ Do not return Entities in the API → use DTOs.
✅ Always include migrations (Flyway/Liquibase).
✅ For production, ddl-auto=validate is better.
✅ Transactions — only in the service layer (@Transactional).
✅ Use Pageable for the API → do not overload the database.
✅ Separate Repository → Service → Controller (clean architecture).

VBO Wiki

Explorer