Spring Data JPA — Database Access

Spring Data JPA eliminates boilerplate data access code. You define an interface that extends JpaRepository, and Spring generates a complete implementation at startup — no SQL, no connection management, no ResultSet parsing. For the cases where you need a custom query, you write JPQL or native SQL in an annotation and Spring executes it for you.

Setup

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/demodb
spring.datasource.username=root
spring.datasource.password=${DB_PASSWORD}
spring.jpa.hibernate.ddl-auto=update   # auto-creates/updates tables in dev; use 'validate' in prod
spring.jpa.show-sql=true               # logs generated SQL — useful in dev, turn off in prod

Entity Mapping

An entity is a Java class that maps to a database table. JPA annotations describe how each field maps to a column. The @PrePersist and @PreUpdate lifecycle callbacks are a clean way to maintain audit timestamps automatically, without relying on the caller to set them.

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) // auto-increment in MySQL
    private Long id;

    @Column(nullable = false, length = 100)
    private String name;

    @Column(nullable = false, unique = true) // unique constraint at the DB level
    private String email;

    @Column(nullable = false)
    private String passwordHash;

    @Enumerated(EnumType.STRING) // store enum name as text, not ordinal number
    @Column(nullable = false)
    private UserRole role;

    @Column(nullable = false, updatable = false) // never updated after insert
    private LocalDateTime createdAt;

    @Column(nullable = false)
    private LocalDateTime updatedAt;

    @PrePersist
    protected void onCreate() {
        createdAt = updatedAt = LocalDateTime.now(); // set both on first save
    }

    @PreUpdate
    protected void onUpdate() {
        updatedAt = LocalDateTime.now(); // update only updatedAt on subsequent saves
    }

    // constructors, getters, setters
}

Using @MappedSuperclass for Audit Fields

When many entities share the same audit fields, extract them into a base class. @MappedSuperclass tells JPA to include these fields in each subclass’s table without creating a separate table for the base class.

@MappedSuperclass
public abstract class BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, updatable = false)
    private LocalDateTime createdAt;

    @Column(nullable = false)
    private LocalDateTime updatedAt;

    @PrePersist  protected void onCreate() { createdAt = updatedAt = LocalDateTime.now(); }
    @PreUpdate   protected void onUpdate() { updatedAt = LocalDateTime.now(); }
}

// Product inherits id, createdAt, updatedAt — no duplication
@Entity
public class Product extends BaseEntity {
    private String name;
    private double price;
}

Repositories

Extending JpaRepository gives you a complete CRUD implementation for free. Beyond the built-ins, Spring generates queries from method names — read the method name like a sentence and Spring turns it into SQL.

// JpaRepository<EntityType, IdType> provides: save, findById, findAll, delete, count, exists
public interface UserRepository extends JpaRepository<User, Long> {

    // Derived queries — Spring reads the method name and generates the SQL automatically
    Optional<User> findByEmail(String email);
    List<User> findByRole(UserRole role);
    List<User> findByNameContainingIgnoreCase(String keyword); // WHERE UPPER(name) LIKE '%keyword%'
    boolean existsByEmail(String email);
    long countByRole(UserRole role);
    void deleteByEmail(String email);

    // Sorting and pagination built into derived queries
    List<User> findByRoleOrderByNameAsc(UserRole role);
    Page<User> findByRole(UserRole role, Pageable pageable);
}

Derived Query Keywords

Custom JPQL Queries

When the derived query syntax isn’t expressive enough, write JPQL directly with @Query. JPQL looks like SQL but operates on entity classes and fields rather than table names and columns — it stays portable across databases.

public interface UserRepository extends JpaRepository<User, Long> {

    // Named parameter binding — @Param matches the :since placeholder
    @Query("SELECT u FROM User u WHERE u.createdAt > :since ORDER BY u.createdAt DESC")
    List<User> findRecentUsers(@Param("since") LocalDateTime since);

    // Full-text search across two fields with pagination support
    @Query("SELECT u FROM User u WHERE LOWER(u.name) LIKE LOWER(CONCAT('%', :q, '%')) " +
           "OR LOWER(u.email) LIKE LOWER(CONCAT('%', :q, '%'))")
    Page<User> search(@Param("q") String query, Pageable pageable);

    // @Modifying marks this as a write query; @Transactional ensures it runs in a transaction
    @Modifying
    @Transactional
    @Query("UPDATE User u SET u.role = :role WHERE u.id = :id")
    int updateRole(@Param("id") Long id, @Param("role") UserRole role);

    @Modifying
    @Transactional
    @Query("DELETE FROM User u WHERE u.createdAt < :cutoff")
    int deleteOlderThan(@Param("cutoff") LocalDateTime cutoff);

    // Native SQL when JPQL can't express a DB-specific feature like full-text search
    @Query(value = "SELECT * FROM users WHERE MATCH(name, email) AGAINST(:q IN BOOLEAN MODE)",
           nativeQuery = true)
    List<User> fullTextSearch(@Param("q") String query);
}

Pagination and Sorting

Pagination is essential for any endpoint that could return a large number of records. Spring Data’s Pageable and Page abstractions handle the offset calculation, the COUNT query, and the response metadata automatically — you just specify the page number and size.

// Service layer — constructs the Pageable and maps entities to response DTOs
public Page<UserResponse> findAll(int page, int size, String sortBy, String direction) {
    Sort sort = direction.equalsIgnoreCase("desc")
        ? Sort.by(sortBy).descending()
        : Sort.by(sortBy).ascending();

    Pageable pageable = PageRequest.of(page, size, sort);
    return userRepository.findAll(pageable).map(UserResponse::from); // map each entity to DTO
}

// Controller — accepts page/size/sort from query string
@GetMapping
public Page<UserResponse> getUsers(
        @RequestParam(defaultValue = "0")    int page,
        @RequestParam(defaultValue = "20")   int size,
        @RequestParam(defaultValue = "name") String sortBy,
        @RequestParam(defaultValue = "asc")  String direction) {
    return userService.findAll(page, size, sortBy, direction);
}

Response — Spring serialises Page with metadata automatically:

{
  "content": [...],
  "pageable": { "pageNumber": 0, "pageSize": 20 },
  "totalElements": 143,
  "totalPages": 8,
  "last": false,
  "first": true
}

Relationships

@ManyToOne / @OneToMany

The @ManyToOne side owns the foreign key. FetchType.LAZY means the related entity is not loaded until you access it — this is the right default to avoid loading the entire object graph on every query. CascadeType.ALL on the @OneToMany side means operations on the parent propagate to children automatically.

@Entity
public class Order extends BaseEntity {

    @ManyToOne(fetch = FetchType.LAZY)       // loads User only when accessed
    @JoinColumn(name = "user_id", nullable = false)
    private User user;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    // orphanRemoval: removing an item from the list deletes it from the DB
    private List<OrderItem> items = new ArrayList<>();

    private double total;
    private OrderStatus status;
}

@Entity
public class OrderItem extends BaseEntity {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    private Order order;

    private String sku;
    private int quantity;
    private double unitPrice;
}

Avoiding N+1 with JOIN FETCH

The N+1 problem is one of the most common JPA performance issues. Loading a list of orders and then accessing each order’s user fires one query per order. JOIN FETCH solves this by loading both in a single query.

// N+1 problem — one query for orders, then one per order to load the user
List<Order> orders = orderRepository.findAll();  // accessing order.getUser() fires a query each time

// Fix: JOIN FETCH loads the user in the same query as the order
@Query("SELECT o FROM Order o JOIN FETCH o.user WHERE o.status = :status")
List<Order> findWithUserByStatus(@Param("status") OrderStatus status);

@ManyToMany

Many-to-many relationships require a join table. JPA manages the join table automatically — you just describe its structure with @JoinTable.

@Entity
public class Student extends BaseEntity {

    @ManyToMany
    @JoinTable(
        name = "student_course",               // name of the join table
        joinColumns = @JoinColumn(name = "student_id"),
        inverseJoinColumns = @JoinColumn(name = "course_id")
    )
    private Set<Course> courses = new HashSet<>();
}

@Entity
public class Course extends BaseEntity {

    @ManyToMany(mappedBy = "courses") // "courses" refers to the field in Student
    private Set<Student> students = new HashSet<>();
}

Transactions

@Transactional wraps a method in a database transaction. If the method throws a RuntimeException, the transaction rolls back automatically. The readOnly = true hint on the class tells Hibernate to skip dirty checking on reads, which improves performance — then individual write methods override it.

@Service
@Transactional(readOnly = true)  // default for all methods: read-only, no dirty checking
public class OrderService {

    private final OrderRepository orderRepository;
    private final UserRepository  userRepository;

    @Transactional  // overrides readOnly=true — this method writes to the database
    public Order placeOrder(Long userId, List<OrderItemRequest> items) {
        User user = userRepository.findById(userId)
            .orElseThrow(() -> new ResourceNotFoundException("User not found"));

        Order order = new Order(user, calculateTotal(items));
        items.forEach(i -> order.addItem(new OrderItem(i.sku(), i.quantity(), i.price())));
        return orderRepository.save(order); // save triggers @PrePersist lifecycle callbacks
    }

    // readOnly=true inherited — Hibernate skips dirty checking for this method
    public List<Order> getUserOrders(Long userId) {
        return orderRepository.findByUserId(userId);
    }
}