Skip to content
notes.vinhnq.com

Search

Database & ORMSenior23 min read

ORM and JPA: JPQL, native queries, @Transactional and locking

Review 26 cards
On this page

ORM and JPA series: 1. Entities and repositories · 2. Entity relationships · 3. The E-Learning schema, DTOs, JSON · 4. Persistence context and N+1 · 5. JPQL, @Transactional, locking

This part tackles the two problems raised in the third ORM lesson: complex queries that derived queries can’t express, and efficient updates that don’t need a SELECT first. Then come three senior topics: where @Transactional breaks, optimistic vs pessimistic locking, and why you should turn off open-session-in-view. Read part 4 on the persistence context and N+1 first.

1. The problems

Key idea: derived queries run out of steam for complex or dynamic queries, and “load then modify” costs a SELECT per update. This post solves both with @Query, Specifications and bulk updates.

1.1. Querying: derived queries run out of road

Derived queries build the query from the method name. Great for simple lookups, but once things get complex:

List<Course> findByStatusAndPriceBetweenAndTeacherNameContainingIgnoreCaseOrderByCreatedAtDesc(
        CourseStatus status, BigDecimal min, BigDecimal max, String teacherName);
  • Method names get long and unreadable, and hard to review.
  • No support for GROUP BY, HAVING, subqueries or non-trivial aggregates. Simple joins through a property path do work, like TeacherName above.
  • No dynamic conditions: every combination of filters needs its own method.

The fix: @Query with JPQL or native SQL; for dynamic conditions, Specifications/Criteria (section 4).

1.2. Saving: you have to SELECT before you UPDATE

2. JPQL

Key idea: JPQL is written against entity and Java field names, and Hibernate translates it to your database’s SQL. For UPDATE/DELETE, add @Modifying and run it in a transaction.

2.1. What JPQL is

JPQL (Jakarta Persistence Query Language) is an object-oriented query language: you write against entity and field names, not tables and columns. The same JPQL runs on MySQL, PostgreSQL… because Hibernate translates it per dialect.

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("select u from User u where u.username = :username and u.status = :status")
    Optional<User> findActive(@Param("username") String username,
                              @Param("status") UserStatus status);

    @Query("""
           select c.teacher.name as teacherName, count(c) as total
           from Course c
           where c.status = :status
           group by c.teacher.name
           having count(c) >= :min
           """)
    List<TeacherCourseCount> countByTeacher(@Param("status") CourseStatus status,
                                            @Param("min") long min);
}

public interface TeacherCourseCount {   // interface-based projection
    String getTeacherName();
    long getTotal();
}
@Param and the -parameters flag since Spring 6.1

2.2. Bulk update and delete with @Modifying

@Modifying(clearAutomatically = true, flushAutomatically = true)
@Transactional
@Query("update User u set u.status = :status where u.id = :id")
int updateStatus(@Param("id") Long id, @Param("status") UserStatus status);

@Modifying
@Transactional
@Query("delete from User u where u.status = :status and u.lastLoginAt < :before")
int deleteInactive(@Param("status") UserStatus status, @Param("before") Instant before);
  • @Modifying tells Spring Data this isn’t a SELECT, so it calls executeUpdate(); an int return gives the number of affected rows.
  • It needs a transaction, otherwise you get TransactionRequiredException. Repository methods default to read-only transactions, so put @Transactional on the method or call it from a transactional service.

2.3. Limits of JPQL

  • It doesn’t cover all of SQL: window functions, recursive CTEs, ON CONFLICT/ON DUPLICATE KEY, vendor hints, JSON types, full-text search.
  • It depends on the entity mapping: queries follow mapped associations; unmapped ones need explicit joins on a condition.
  • Harder to optimise: you don’t control the generated SQL directly, so you log it first.
Hibernate 6 HQL goes beyond standard JPQL

Hibernate 6 added a lot through HQL: CTEs, window functions, set operations. But that’s HQL, not standard JPQL, so it ties your code to Hibernate.

3. Native queries

Key idea: a native query is SQL run as written: every database feature is available, but you’re tied to that database and nothing is checked at build time. Whether JPQL or native, always use bind parameters.

3.1. What a native query is

A native query is plain SQL for your specific database: SELECT, INSERT, UPDATE, DELETE, upserts, CTEs, window functions, hints.

@Query(value = """
        select * from users
        where username = :username and status = :status
        """, nativeQuery = true)
Optional<User> findActiveNative(@Param("username") String username,
                                @Param("status") String status);

@Modifying
@Transactional
@Query(value = """
        insert into course_views (course_id, view_date, views)
        values (:courseId, current_date, 1)
        on duplicate key update views = views + 1
        """, nativeQuery = true)
void incrementViews(@Param("courseId") Long courseId); // MySQL-specific upsert
Native update, delete and a window-function report
@Modifying
@Transactional
@Query(value = "update users set status = :status where username = :username", nativeQuery = true)
int updateStatusNative(@Param("username") String username, @Param("status") String status);

@Modifying
@Transactional
@Query(value = "delete from users where status = :status and updated_date < :before", nativeQuery = true)
int deleteInactiveBefore(@Param("status") String status, @Param("before") LocalDateTime before);

The slides’ native delete example has only the SQL, with no method declaration, and mixes the table names user and users. deleteInactiveBefore is the complete version: @Modifying + @Transactional, @Param on each parameter, returning the number of deleted rows, consistently using users.

A reporting query with a window function, which standard JPQL can’t express:

public interface TopCourseRow {
    Long getCourseId();
    String getTitle();
    Long getEnrollments();
    Integer getRankInCategory();
}

@Query(value = """
        select c.id as courseId, c.title as title, count(e.id) as enrollments,
               rank() over (partition by c.category_id order by count(e.id) desc) as rankInCategory
        from courses c
        left join enrollments e on e.course_id = c.id
        group by c.id, c.title, c.category_id
        """, nativeQuery = true)
List<TopCourseRow> topCoursesPerCategory();

3.2. Limits of native queries

  • Database lock-in: moving to PostgreSQL means rewriting (on duplicate key becomes on conflict).
  • Lost abstraction: you need table and column names; renaming an entity field won’t break the query at compile time.
  • Harder maintenance: mistakes only show up at runtime, so you need integration tests (Testcontainers with your production database).
  • Paging needs an explicit countQuery.
  • Returning entities puts them in the persistence context; returning projections doesn’t.

3.3. Never concatenate SQL

Both JPQL and native queries must use bind parameters (:name, ?1). Concatenating user input into a query is SQL injection:

// WRONG: username = "x' or '1'='1" returns every user
em.createNativeQuery("select * from users where username = '" + username + "'");

// RIGHT
em.createNativeQuery("select * from users where username = :u", User.class)
  .setParameter("u", username);

Bind parameters can’t stand in for column names or sort direction. For dynamic sorting, map from a whitelist, or use Spring Data’s Sort (which checks that the property exists).

3.4. Which one to use

4. Dynamic queries with Specifications

Key idea: each filter is a small Specification that returns null when it has no value. Combine them and only the filters with values reach the WHERE clause.

The repository also extends JpaSpecificationExecutor:

public interface CourseRepository extends JpaRepository<Course, Long>,
                                          JpaSpecificationExecutor<Course> {}

public final class CourseSpecs {
    private CourseSpecs() {}

    public static Specification<Course> hasStatus(CourseStatus status) {
        return (root, q, cb) -> status == null ? null : cb.equal(root.get("status"), status);
    }

    public static Specification<Course> titleContains(String keyword) {
        return (root, q, cb) -> keyword == null || keyword.isBlank() ? null
                : cb.like(cb.lower(root.get("title")), "%" + keyword.toLowerCase() + "%");
    }

    public static Specification<Course> priceBetween(BigDecimal min, BigDecimal max) {
        return (root, q, cb) -> {
            if (min == null && max == null) return null;
            if (min == null) return cb.le(root.get("price"), max);
            if (max == null) return cb.ge(root.get("price"), min);
            return cb.between(root.get("price"), min, max);
        };
    }
}

// Service
Page<Course> page = courseRepository.findAll(
        Specification.allOf(
                CourseSpecs.hasStatus(filter.status()),
                CourseSpecs.titleContains(filter.keyword()),
                CourseSpecs.priceBetween(filter.minPrice(), filter.maxPrice())),
        PageRequest.of(filter.page(), Math.min(filter.size(), 100), Sort.by("createdAt").descending()));

Specification.allOf has existed since Spring Data JPA 3.0; older versions use Specification.where(a).and(b). Pagination combined with dynamic filters (Pageable, Page vs Slice, OR-ing price ranges, rejecting unknown sort columns) is in the pagination and dynamic filters post.

5. @Transactional: how it works and where it breaks

Key idea: @Transactional only works when the call goes through the Spring proxy wrapping the bean. Most bugs come from calls that skip the proxy, exceptions that don’t roll back, or transactions that hold a connection too long.

5.1. The mechanism

Spring wraps your bean in an AOP proxy (an object in front of the bean that intercepts calls to add behaviour). When outside code calls a method, the proxy starts a transaction: borrows a connection from the pool, setAutoCommit(false), binds it to the thread via a ThreadLocal. Then it calls the real method and commits or rolls back.

sequenceDiagram
  participant C as Controller
  participant P as CourseService proxy
  participant S as Real CourseService
  participant DB as Database
  C->>P: enroll()
  P->>DB: begin (borrow connection, autocommit=false)
  P->>S: enroll()
  S->>DB: SELECT / INSERT ...
  S-->>P: return (or throw)
  P->>DB: commit (or rollback on RuntimeException)
  P-->>C: result

5.2. The pitfalls

Propagation (how a method joins an existing transaction), the three you’ll actually use:

Propagation Behaviour
REQUIRED (default) Join the current transaction, or start one if there’s none
REQUIRES_NEW Always start a new one, suspending the outer transaction
NESTED A savepoint inside the current transaction (JDBC DataSourceTransactionManager only)

Put @Transactional on the service layer, where a business unit of work is defined, not on controllers or repositories. Spring Data repositories already wrap each method in its own transaction. Several repository calls with no service-level transaction each commit separately, and you lose atomicity.

More: non-public methods, readOnly = true and the full propagation table

Non-public or final methods. A proxy can’t intercept private methods. Since Spring 6, protected and package-private methods are supported with class-based (CGLIB) proxies, but final and static methods never are.

The remaining four propagation types:

Propagation Behaviour
SUPPORTS Join if there is one, otherwise run without
MANDATORY Requires an existing transaction, throws otherwise
NOT_SUPPORTED Suspend the current transaction and run without
NEVER Throw if a transaction exists

6. Locking: optimistic and pessimistic

Key idea: when two people read and then both write, the second overwrites the first (a lost update). Three ways to stop it: check a version on write, lock the row on read, or let one conditional UPDATE do it atomically.

The scenario: a course has one seat left and two students hit “enrol” at the same moment. Both read seats_left = 1, both write seats_left = 0, and the course ends up with 101 students.

6.1. Optimistic locking

Assumes conflicts are rare. Nothing is locked on read; on write, you check that nobody changed the row in the meantime. Like editing a shared document: only when you save do you find out someone saved a newer version.

@Entity
public class Course {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private int seatsLeft;

    @Version
    private long version;

    public void takeSeat() {
        if (seatsLeft <= 0) throw new CourseFullException(id);
        seatsLeft--;
    }
}

Hibernate generates:

UPDATE courses SET seats_left = 0, version = 8 WHERE id = 42 AND version = 7;
-- 0 rows affected → someone got there first → OptimisticLockException

Spring wraps it as ObjectOptimisticLockingFailureException. Handle it by returning 409 Conflict, or by retrying the whole transaction so it re-reads fresh data.

Retrying with @Retryable and @Version over HTTP (ETag)
@Retryable(retryFor = ObjectOptimisticLockingFailureException.class, maxAttempts = 3,
           backoff = @Backoff(delay = 50, multiplier = 2))
@Transactional
public void enroll(long courseId, long studentId) { ... }

Proxy order matters: the retry must wrap the transaction from outside, so each attempt is a fresh transaction. Spring Retry’s advice is ordered ahead of the transaction advice by default, so this works; splitting them into two beans is the bulletproof option.

@Version is also the right way to do optimistic concurrency over HTTP: expose the version as an ETag, have the client send it back with If-Match, and return 412 on a mismatch (see REST API design).

6.2. Pessimistic locking

Assumes conflicts are frequent. Lock the row as you read it; other transactions wait.

public interface CourseRepository extends JpaRepository<Course, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "3000"))
    @Query("select c from Course c where c.id = :id")
    Optional<Course> findForUpdate(@Param("id") Long id);
}

This issues SELECT ... FOR UPDATE. It only works inside a transaction; the lock is held until commit or rollback.

  • PESSIMISTIC_READ → a shared lock (FOR SHARE): many readers, no writers.
  • PESSIMISTIC_WRITE → an exclusive lock (FOR UPDATE).
  • Always set a lock timeout so you never wait forever.
  • When locking several rows, lock them in a consistent order (e.g. ascending id) to avoid deadlocks.
  • Job queues: FOR UPDATE SKIP LOCKED lets several workers grab different jobs without waiting on each other (in Hibernate: lock timeout -2).

6.3. Option three: let the database do it atomically

Often you don’t need read-then-write at all, just one conditional UPDATE:

@Modifying
@Transactional
@Query("update Course c set c.seatsLeft = c.seatsLeft - 1 where c.id = :id and c.seatsLeft > 0")
int takeSeat(@Param("id") Long id); // 0 means the course is full

One statement, a very short row lock, no lost update. The catch: it bypasses @Version and the persistence context (section 2.2).

6.4. Comparison

Application-level locking also interacts with the database’s isolation level (InnoDB defaults to REPEATABLE READ, PostgreSQL to READ COMMITTED). More on that in Database.

7. Open Session in View (OSIV)

Key idea: OSIV keeps the EntityManager open for the whole request so lazy loading “works” in controllers and during serialization. The price is long-held connections and hidden N+1, so turn it off.

Spring Boot defaults to spring.jpa.open-in-view=true and logs a warning at startup. When it’s on, an interceptor keeps the EntityManager open from the start of the request until the response is written.

sequenceDiagram
  participant F as OSIV interceptor
  participant C as Controller
  participant S as Service (@Transactional)
  participant J as Jackson
  F->>F: open EntityManager (held for the whole request)
  F->>C: handle request
  C->>S: getCourses()
  S-->>C: List<Course> (transaction already committed)
  C->>J: serialize entities
  J->>J: course.getTeacher().getName() → lazy query outside a transaction
  F->>F: close EntityManager after the response is written

Same root cause: an entity stored in the HTTP session has no persistence context on the next request, so touching a lazy field fails; the sessions and remember-me post explains why the session should hold only ids or DTOs.

Exercises

Finish the pet project

Task (paraphrased): complete the e-learning pet project: every API you designed must work against real data, using JPQL/native queries wherever derived queries aren’t enough. The overall brief is in Hands-on pet project.

I split it into four tasks, each using one technique from this post:

  1. A course search screen with many filters → Specifications + paging + @EntityGraph.
  2. Student counts per course for a given teacher → JPQL group by + DTO projection.
  3. Enrolment with limited seats that’s safe under concurrency → conditional UPDATE + unique constraint.
  4. Archive expired courses in bulk → a bulk @Modifying update triggered by a cron job.
Solutions for the four pet-project tasks

1. Multi-filter search → Specifications + paging (section 4), plus @EntityGraph to load the teacher in the same query:

public interface CourseRepository extends JpaRepository<Course, Long>, JpaSpecificationExecutor<Course> {
    @Override
    @EntityGraph(attributePaths = "teacher")
    Page<Course> findAll(Specification<Course> spec, Pageable pageable);
}

2. Per-teacher stats → JPQL with group by and a DTO projection:

public record CourseStats(Long courseId, String title, long students) {}

@Query("""
       select new com.vinhnq.elearning.dto.CourseStats(c.id, c.title, count(e))
       from Course c left join Enrollment e on e.course = c
       where c.teacher.id = :teacherId
       group by c.id, c.title
       order by count(e) desc
       """)
List<CourseStats> statsForTeacher(@Param("teacherId") Long teacherId);

3. Limited-seat enrolment → conditional UPDATE + unique constraint:

@Transactional
public void enroll(long courseId, long studentId) {
    if (courseRepository.takeSeat(courseId) == 0) {
        throw new CourseFullException(courseId);               // → 409
    }
    try {
        enrollmentRepository.saveAndFlush(new Enrollment(
                studentRepository.getReferenceById(studentId),
                courseRepository.getReferenceById(courseId),
                Instant.now()));
    } catch (DataIntegrityViolationException e) {
        throw new AlreadyEnrolledException(studentId, courseId); // unchecked → rolls back takeSeat too
    }
}

saveAndFlush makes a unique-constraint violation surface inside the method (otherwise it would only appear at commit, outside the try). The unchecked exception also rolls back the seat decrement.

4. Archive expired courses → a bulk update with @Modifying, triggered by a cron job (see Cron jobs):

@Modifying(clearAutomatically = true)
@Transactional
@Query("update Course c set c.status = :archived where c.status = :published and c.endDate < :today")
int archiveExpired(@Param("archived") CourseStatus archived,
                   @Param("published") CourseStatus published,
                   @Param("today") LocalDate today);

Verification: integration tests with Testcontainers MySQL, including one that fires 20 concurrent enrolment requests at a course with 5 seats and asserts that exactly 5 enrolments are created.

Remember

  • Can you pick between a derived query, JPQL, native SQL and a Specification for a given query, and say why?
  • Can you list the four things a bulk @Modifying bypasses, and what clearAutomatically/flushAutomatically fix?
  • Can you explain why self-invocation, checked exceptions and swallowed exceptions make @Transactional misbehave?
  • Do you know when to use @Version, FOR UPDATE or a conditional UPDATE for “limited seats”?
  • Can you explain why to turn off open-session-in-view and what replaces it?

Interview questions

Junior

How does JPQL differ from SQL?

JPQL is written against entities and Java fields and is database-agnostic; SQL (a native query) is written against tables and columns and ties you to one database.

Hibernate translates JPQL to your database’s SQL. A native query runs as-is and can use every feature of that database.

What is @Modifying for?

It marks a @Query as an UPDATE/DELETE (or INSERT for native queries) so Spring Data calls executeUpdate() instead of expecting results.

It must run inside a transaction and returns the number of affected rows.

When do derived queries stop being a good fit?

When method names get too long, when you need group by/having/subqueries/aggregates, or when filters are dynamic.

At that point switch to @Query (JPQL or native) or Specifications.

Mid

What side effects does a @Modifying bulk update have?

It goes straight to the database, so the persistence context doesn’t know, and it bypasses @Version, cascades, lifecycle callbacks and auditing.

Loaded entities keep stale values (use clearAutomatically); unflushed changes can be overwritten (use flushAutomatically).

Why doesn’t @Transactional work when a method calls another method in the same class?

Because transactions are applied by the proxy wrapping the bean, and this.method() calls the real object directly, skipping the proxy.

Fix it by moving the method to another bean or using TransactionTemplate.

Does a transaction roll back on a checked exception?

Not by default: Spring rolls back only on RuntimeException and Error.

Declare rollbackFor to roll back on checked exceptions. If the exception is caught and swallowed inside the method, the proxy never sees it and the transaction commits.

How do optimistic and pessimistic locking differ?

Optimistic takes no lock and detects conflicts at write time with @Version; pessimistic locks the row on read with SELECT ... FOR UPDATE.

Optimistic issues WHERE version = ?; a conflict throws and you retry. It suits rare conflicts and flows spanning several requests. Pessimistic makes other transactions wait; it suits highly contended resources in short transactions.

Senior

What is open-session-in-view and why turn it off?

OSIV keeps the EntityManager open for the whole request so lazy loading works in controllers and during serialization; turn it off because it holds connections long and hides N+1.

Under load the pool runs out, and the view layer hits the database outside a transaction. Turn it off with spring.jpa.open-in-view=false, fetch what you need in the service and return DTOs.

Explain UnexpectedRollbackException.

A REQUIRED inner method throws a RuntimeException through its proxy, so the transaction is marked rollback-only; the outer method swallows it and commits, Spring sees the flag, rolls back and throws UnexpectedRollbackException.

If the inner work should fail independently, use REQUIRES_NEW (accepting the extra connection) or move it to asynchronous processing.

What’s the risk of REQUIRES_NEW under heavy load?

A connection-pool deadlock: the outer transaction is suspended but keeps its connection, and the new transaction needs a second one.

If every pooled thread is in that state, nobody can get a second connection and the system hangs until timeouts fire. Mitigate by limiting REQUIRES_NEW, sizing the pool above the number of threads that can nest transactions, or redesigning to avoid nesting.

How would you design a “limited seats” booking feature that’s correct under concurrency?

It depends on contention; the simplest and fastest is a conditional UPDATE set seats = seats - 1 where id = ? and seats > 0, checking the affected row count.

  • Rich entity logic: @Version + retry for low contention, SELECT ... FOR UPDATE with a lock timeout for high contention.
  • Always back it with a unique constraint to block duplicate bookings.
  • Massive flash sale: move the counter to Redis (DECR) or a queue and write to the database asynchronously.

Useful phrases for interviews

  • “Derived queries are fine for simple lookups, but for dynamic filters I compose Specifications.”
  • “A bulk update bypasses the persistence context, so I clear it afterwards to avoid reading stale entities.”
  • “The annotation was ignored because of self-invocation; the call never went through the proxy.”
  • “I keep transactions short and push slow side effects to an after-commit listener.”
  • “For low contention I’d go with optimistic locking and a bounded retry; for a hot row, a pessimistic lock with a timeout.”
  • “The simplest correct solution here is a conditional UPDATE and checking the affected row count.”
  • “We turned off open-in-view because it was holding connections for the entire request.”
  • “The inner call marked the transaction as rollback-only, so the outer commit failed.”

Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.