Skip to content
Tutorials Center
My learning

Topics

Lesson 74 of 119 ยท Spring Boot

Spring Data JPA

Use Spring Data JPA repositories - JpaRepository, derived query methods, @Query, projections, paging and sorting, auditing - with the SQL each one runs, live on H2.

With plain JPA you write a DAO class for every entity: inject an EntityManager, write find, persist and a JPQL query for every search, and repeat it all for the next entity. Spring Data JPA removes that boilerplate. You declare an interface that extends JpaRepository, add method signatures such as findByAuthorOrderByYearDesc(String author), and Spring generates the implementation at startup, including the query, derived from the method name.

In this lesson you will learn the repository interfaces, derived query methods and their keywords, @Query for everything else, projections, paging and sorting, and auditing. Spring Data can't run on this page, so each runnable example shows the SQL that Spring Data and Hibernate generate, executed on an in-memory H2 database. The first one goes further: it implements a repository interface at runtime with a dynamic proxy that parses method names, which is exactly the trick Spring Data uses. You should know ORM and JPA Introduction, JPQL and the Criteria API and The DAO and Repository Patterns first.

Setup and the entity

Add the starter and a database driver. Spring Boot then creates the DataSource (with a HikariCP pool), the EntityManagerFactory with Hibernate, a transaction manager, and an implementation for every repository interface it finds below the main class's package:

XML
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <scope>runtime</scope>
</dependency>
PROPERTIES
spring.datasource.url=jdbc:postgresql://localhost:5432/library
spring.datasource.username=library
spring.datasource.password=${DB_PASSWORD}
spring.jpa.hibernate.ddl-auto=validate     # check the schema; let Flyway/Liquibase create it
spring.jpa.open-in-view=false              # see the warning below
logging.level.org.hibernate.SQL=DEBUG      # log every SQL statement while learning

The entity is a normal JPA entity, as in Entities and Hibernate Setup:

Java
@Entity
@Table(name = "book")
public class Book {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false) private String title;
    @Column(nullable = false) private String author;
    private int year;

    protected Book() {}                      // for JPA
    public Book(String title, String author, int year) { ... }
    // getters, and setters only where changes are allowed
}

Repository interfaces

You choose how much to expose by choosing which interface to extend:

Interface Adds
Repository<T, ID> Nothing: a marker. Declare only the methods you want.
CrudRepository<T, ID> save, saveAll, findById, existsById, findAll, count, deleteById, delete... (Iterable results)
ListCrudRepository<T, ID> The same, returning List instead of Iterable
ListPagingAndSortingRepository<T, ID> findAll(Sort), findAll(Pageable)
JpaRepository<T, ID> All of the above plus JPA extras: flush, saveAndFlush, deleteAllInBatch, getReferenceById, query by example
Java
public interface BookRepository extends JpaRepository<Book, Long> {
    List<Book> findByAuthor(String author);
}

@Service
public class BookService {
    private final BookRepository books;
    public BookService(BookRepository books) { this.books = books; }   // Spring injects the generated implementation

    public Book add(String title, String author, int year) {
        return books.save(new Book(title, author, year));
    }
}

Where does Spring Data look for these interfaces? Spring Boot's auto-configuration scans the package of your @SpringBootApplication class and its sub-packages. If your repositories or entities live elsewhere (a shared module, another root package), say so explicitly:

Java
@Configuration
@EnableJpaRepositories(basePackages = "com.example.shared.repository")
@EntityScan(basePackages = "com.example.shared.model")
class PersistenceConfig { }

@EnableJpaRepositories also lets you pick the entityManagerFactoryRef and transactionManagerRef when an application has more than one database. Outside Boot (a plain Spring application) this annotation is required, because nothing scans for you.

You never write class BookRepositoryImpl. At startup Spring Data creates a proxy for the interface: inherited methods like save go to a generic implementation (SimpleJpaRepository), and your own methods are turned into queries.

Derived query methods

Spring Data parses the method name. The prefix says what to do, By starts the criteria, property names are joined with And/Or, and keywords after a property choose the operator:

Method Generated JPQL (simplified)
findByAuthor(String a) where b.author = ?1
findByAuthorAndYearGreaterThan(String a, int y) where b.author = ?1 and b.year > ?2
findByYearBetween(int from, int to) where b.year between ?1 and ?2
findByTitleContainingIgnoreCase(String s) where upper(b.title) like upper(?1) with %s%
findByTitleStartingWith(String s) where b.title like ?1 with s%
findByAuthorIn(Collection<String> names) where b.author in ?1
findByIsbnIsNull() where b.isbn is null
findByAuthorOrderByYearDesc(String a) where b.author = ?1 order by b.year desc
findFirstByOrderByYearAsc() / findTop3By... ... order by b.year asc with a row limit
countByAuthor(String a) select count(b) ... where b.author = ?1
existsByTitle(String t) Checks whether at least one row matches
deleteByAuthor(String a) Loads matching entities and removes each one

Other prefixes (readBy, getBy, queryBy, streamBy) mean the same as findBy. Property paths work across relationships: findByPublisherName(String name) joins to publisher and compares its name. Return types are flexible: List<Book>, Optional<Book> (for at most one result), Book, Stream<Book>, Page<Book>, Slice<Book>, long, boolean.

The method names are checked at startup. A typo such as findByAutor stops the application with an error like "No property 'autor' found for type 'Book'", long before a user hits that code path.

Here is the whole mechanism in one program. BookRepository is just an interface. java.lang.reflect.Proxy creates an object that implements it (see Aspect-Oriented Programming for how proxies work), and the invocation handler parses every method name into SQL when the repository is created, then runs the SQL on H2 when a method is called. The last part creates a repository with a typo and fails the way Spring does.

Java A repository implemented from method names

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

(NON_KEYWORDS=YEAR in the connection URL lets H2 accept year as a column name; YEAR is a reserved word in H2 and in standard SQL. Real entities often map such fields to a column like publication_year.) The real parser supports many more keywords, Or, nested properties and type checks of the parameters, but the idea is the same: the method signature is the query.

Derived queries are great for one to three conditions. Beyond that the names become unreadable (findByAuthorAndYearGreaterThanAndTitleContainingIgnoreCaseOrderByYearDesc) and a single rename breaks them. Switch to @Query, or for truly dynamic filters, to Specifications or Querydsl.

@Query: JPQL and native SQL

@Query puts the query next to the method. It is JPQL by default, so it uses entity and field names, not tables and columns:

Java
public interface BookRepository extends JpaRepository<Book, Long> {

    @Query("select b from Book b where b.author = :author and b.year >= :since order by b.year")
    List<Book> recentByAuthor(@Param("author") String author, @Param("since") int since);

    @Query("select b from Book b join fetch b.publisher where b.id = :id")    // avoid N+1
    Optional<Book> findWithPublisher(@Param("id") long id);

    @Query(value = "select * from book where to_tsvector(title) @@ plainto_tsquery(:q)", nativeQuery = true)
    List<Book> fullTextSearch(@Param("q") String q);                         // PostgreSQL-specific SQL

    @Modifying
    @Transactional
    @Query("update Book b set b.price = b.price * :factor where b.author = :author")
    int raisePrices(@Param("author") String author, @Param("factor") BigDecimal factor);
}
  • Use named parameters (:author). Spring Boot compiles with -parameters, so @Param is optional, but it doesn't hurt. Parameters are always bound safely, never concatenated, so @Query is safe from SQL injection (see PreparedStatement and SQL Injection).
  • JPQL queries are also validated at startup, so a typo fails fast.
  • join fetch loads a relationship in the same query, the standard fix for the N+1 problem from Fetching Strategies and the N+1 Problem. @EntityGraph(attributePaths = "publisher") on a derived method does the same.
  • Native queries give you every database feature, at the cost of portability.
  • Bulk updates and deletes need @Modifying. They run directly in the database, so they bypass the persistence context (already-loaded entities keep their old values unless you set clearAutomatically = true), entity lifecycle callbacks and auditing. You'll see that in the last example.

Projections

Often you need a few columns, not whole entities. Declare the return type and Spring Data selects only what it needs:

Java
public record BookSummary(String title, int year) {}               // DTO projection
List<BookSummary> findByAuthor(String author);                      // selects only title and year

public interface AuthorStats { String getAuthor(); long getBooks(); }   // interface projection
@Query("select b.author as author, count(b) as books from Book b group by b.author")
List<AuthorStats> statsByAuthor();

Projections are read-only and avoid loading entities into the persistence context, which makes them the best choice for lists and reports.

Paging and sorting

Never load a whole table to show 20 rows. Add a Pageable parameter and return a Page:

Java
Page<Book> findByAuthor(String author, Pageable pageable);

Page<Book> page = books.findByAuthor("Ursula K. Le Guin",
        PageRequest.of(0, 20, Sort.by("year").descending().and(Sort.by("title"))));

page.getContent();         // the 20 books (or fewer)
page.getTotalElements();   // total matches, from a count query
page.getTotalPages();
page.hasNext();

Page numbers are zero-based. A Page runs two queries: one for the rows (order by ... limit 20 offset 0) and one count. If you don't need the total (infinite scrolling, "load more"), return Slice<Book> instead: it fetches one extra row to know whether there is a next slice, and skips the count. In a controller, Spring MVC can build the Pageable from the request for you: list(Pageable pageable) reads ?page=2&size=20&sort=year,desc.

Java Paging, sorting and slices in SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Two details to notice. The sort includes a tie-breaker (ID) so that rows with equal sort values don't jump between pages. And sort properties that come from the URL are checked against the entity's properties, because ORDER BY can't use a bind parameter: an unchecked property name would be an SQL injection hole. Spring Data does this check for you.

Deep pages get slow. OFFSET 100000 makes the database read and discard 100,000 rows. For large tables, use keyset (also called seek or cursor) pagination: "give me 20 books with year < :lastYear, ordered by year". Spring Data supports it with ScrollPosition and Window<T> return types (findFirst20ByOrderByYearDesc(ScrollPosition position)). Also, when returning a page as JSON, don't serialize PageImpl directly; its structure is not a stable API. Map it to your own DTO, or use Spring Data's PagedModel.

save(), transactions and auditing

save(entity) decides between insert and update by asking whether the entity is new: by default, an entity whose @Id is null (or whose @Version is null) is new and gets persisted; otherwise it is merged. Two consequences:

  • If you assign ids yourself (UUIDs, ISBNs), every save looks like an update, and Hibernate first runs a select to check. Implement Persistable<ID> with an isNew() method, or add a @Version field, to fix that.
  • Inside a transaction, you don't need save for changes to loaded entities at all: dirty checking writes them at commit (see Entity Lifecycle and Transactions).

Repository methods are transactional on their own (reads with readOnly = true). Put @Transactional on service methods that call several repository methods, so they succeed or fail together.

Auditing fills in who changed what and when, without code in every service:

Java
@SpringBootApplication
@EnableJpaAuditing
public class LibraryApplication { ... }

@Entity
@EntityListeners(AuditingEntityListener.class)
public class Book {
    // ...
    @CreatedDate private Instant createdAt;
    @LastModifiedDate private Instant updatedAt;
    @CreatedBy private String createdBy;          // needs an AuditorAware<String> bean (e.g. from Spring Security)
}

The entity listener sets these fields whenever JPA persists or updates the entity. The last program shows that behavior on H2: save inserts or updates depending on the id, sets the audit timestamps from a clock, keeps createdAt on updates, and then a bulk @Modifying update changes prices without touching updatedAt, because it never goes through the entity lifecycle. It ends with a DTO projection query.

Java save(), auditing and bulk updates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The two office products got more expensive at 12:00, but their updated_at still says 08:00. If a report relies on updatedAt, a bulk update silently breaks it. Either set the column in the bulk query too (set p.price = ..., p.updatedAt = CURRENT_TIMESTAMP), or load and change entities individually when the numbers are small.

Pitfalls professionals know

  • Open Session in View is enabled by default in Boot (spring.jpa.open-in-view=true, with a warning in the log). It keeps the persistence context open until the HTTP response is written, so lazy loading "works" in controllers and JSON serialization, while silently firing extra queries outside your transaction. Turn it off and load what you need in the service.
  • Don't return entities from controllers. Lazy relationships explode during JSON serialization, and you expose your schema. Map to DTOs or use projections.
  • findAll() on a large table loads everything into memory. Use paging, projections or streaming (Stream<Book> inside a transaction).
  • deleteBy... loads before deleting (so lifecycle callbacks run), one delete per entity. For large deletes use a @Modifying @Query bulk delete.
  • getReferenceById returns a lazy proxy without hitting the database; accessing it for a missing id throws later, not at the call.
  • Watch the SQL. Enable logging.level.org.hibernate.SQL=DEBUG in development and count the queries per request. Test repositories against a real database engine with @DataJpaTest and Testcontainers, covered next.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which SQL condition does the derived method findByYearGreaterThanAndAuthor(int year, String author) produce?

    Conditions are generated in the order they appear in the method name, and parameters are bound in declaration order. GreaterThan means a strict >.
  2. 2. A repository method is named findByAutor(String autor), but the entity property is author. When do you find out?

    Repository proxies are created at startup and derived queries are validated against the entity's properties, so the app fails fast.
  3. 3. You need the total number of results for a "page 3 of 12" display. What should the method return, and what does it cost?

    Page needs the total, so Spring Data runs an extra count query. Slice skips it and only knows whether a next slice exists.
  4. 4. An entity uses @LastModifiedDate. You run @Modifying @Query("update Product p set p.price = p.price * 1.1"). What happens to updatedAt?

    Bulk JPQL updates skip the persistence context and entity callbacks. Set the column in the query yourself if it must change.
  5. 5. Your entity's id is a UUID that you assign in the constructor. Why can save() be slower than expected?

    The default isNew check looks at the id (or version). Implement Persistable or add a @Version field so new entities are persisted directly.

Summary

  • Spring Data JPA generates repository implementations from interfaces; extend JpaRepository<T, ID> (or a smaller interface to expose less).
  • Derived query methods turn names like findByAuthorAndYearGreaterThanOrderByYearDesc into queries; they're validated at startup. Keep them short.
  • @Query takes JPQL (or native SQL with nativeQuery = true) with safely bound named parameters; bulk updates need @Modifying and bypass the persistence context and auditing.
  • DTO and interface projections select only the columns you need.
  • Pageable + Page gives rows plus a count query; Slice skips the count; use keyset scrolling for deep pages and a tie-breaker in sorts.
  • save() persists new entities (null id/version) and merges others; use @Transactional services and dirty checking for updates.
  • @EnableJpaAuditing with @CreatedDate/@LastModifiedDate/@CreatedBy fills audit fields automatically.
  • Disable Open Session in View, return DTOs instead of entities, and watch the generated SQL.

Next: Testing Spring Boot Applications shows how to test controllers, repositories and the whole application with Spring's test support.