Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 39 ยท HQL, Criteria and Native Queries

HQL Functions, CASE Expressions and Bulk Operations

Use HQL functions, CASE, COALESCE and date expressions, then run bulk update, delete and insert statements and learn why they leave the persistence context stale.

The earlier HQL lessons selected entities, joined them and aggregated. Real reports need more: upper-casing a name, replacing a null with a default, bucketing prices into "cheap" and "premium", or truncating a date. HQL has portable functions and CASE expressions for that. This lesson then covers bulk statements (update, delete, insert ... select), which are powerful and dangerous in equal measure because they skip the persistence context.

Hibernate is not available in the browser, so its code here is read-only. The runnable programs use JDBC and H2 to show the same SQL ideas and to reproduce the "stale entity" effect of bulk statements with a tiny hand-written session cache. Both are stand-ins, labelled as such.

Functions in HQL

HQL knows the functions every database offers and translates them into the dialect you run on. Some of the most useful:

Kind Examples
String upper(b.title), lower, length, trim, substring(b.title, 1, 3), concat(a, ' ', b), locate('x', b.title), replace
Numeric abs, round(b.price, 1), mod(a, b), sqrt, floor, ceiling
Date and time current_date, current_timestamp, extract(year from o.placedOn), year(o.placedOn)
Null handling coalesce(a, b), nullif(a, b), ifnull
Collections size(a.books), index(...), maxelement, element(...)
Cast cast(b.id as String)
Java
List<Object[]> rows = session.createQuery("""
    select upper(b.title), coalesce(b.subtitle, '(none)'), extract(year from b.publishedOn)
    from Book b
    where length(b.title) > 5
    """, Object[].class).getResultList();

If a function is missing from HQL, Hibernate lets you call a database function directly with function('name', args), and a custom FunctionContributor can register new ones. Reaching for function(...) ties the query to one database, so prefer the portable names.

Arithmetic and string concatenation

HQL supports +, -, *, /, % and the || operator for strings. Be careful with /: in HQL 6, integer division follows the Java types of the operands, so a / b with two integers is an integer division. Cast one side when you want a fraction.

CASE expressions

HQL has the two SQL forms, a simple case and a searched case:

Java
// searched case: bucket books by price
select b.title,
       case
         when b.unitPrice < 20 then 'cheap'
         when b.unitPrice < 40 then 'regular'
         else 'premium'
       end
from Book b

// simple case: compare one expression against values
select case b.status when 'D' then 'draft' when 'P' then 'published' else 'other' end
from Book b

CASE can appear in the select, where, order by and even inside aggregates, which gives you "conditional counts":

Java
select a.name,
       sum(case when b.unitPrice >= 40 then 1 else 0 end) as premiumBooks,
       count(b) as allBooks
from Author a left join a.books b
group by a.name

coalesce is case in short form: coalesce(x, y) returns the first non-null argument. nullif(x, y) returns null when both are equal and is often used to avoid division by zero: total / nullif(count, 0).

Hibernate 6 also added if-style helpers such as ifnull, format(date as 'yyyy-MM-dd') and left/right string functions. Check the HQL guide of your version before you rely on one of them.

Java Functions and CASE in SQL (H2 stand-in for HQL)

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.

Bulk update, delete and insert

Normally you change data by loading entities and letting dirty checking write the changes. For "give every book of this author a 10% discount" that would load thousands of rows. HQL has set-based statements:

Java
int updated = session.createMutationQuery("""
    update Book b set b.unitPrice = b.unitPrice * 0.9
    where b.author.id = :author
    """).setParameter("author", 7L).executeUpdate();

int removed = session.createMutationQuery("delete from Book b where b.status = 'D' and b.createdOn < :cut")
    .setParameter("cut", cutOff).executeUpdate();

session.createMutationQuery("""
    insert into BookArchive (id, title, archivedOn)
    select b.id, b.title, current_date from Book b where b.status = 'D'
    """).executeUpdate();

executeUpdate() returns the number of affected rows. These statements are one SQL statement each, so they are fast, but three things change compared with entity-based writes.

  1. The persistence context is not updated. Entities already loaded in the session keep their old values. If you load a Book, run the bulk update and read the book again, you see the old price from the first-level cache.
  2. No lifecycle callbacks (@PreUpdate, @PostRemove) and no cascades. Deleting rows with a bulk statement does not remove their children; the database foreign keys decide what happens.
  3. No @Version bump unless you write it yourself: add versioned after update (update versioned Book b set ...) to increment the version column.

The safe pattern is to run the bulk statement first in a fresh transaction, or to call session.clear() (or refresh the entities you still hold) right afterwards.

Second-level cache regions of the affected entities are invalidated by HQL bulk statements. Native SQL is different: Hibernate does not know which tables it touches unless you declare them (see Native SQL, Named Queries and Projections).

A bulk delete or update without a where clause touches every row of the table. Put a where in the query on purpose, and run it first on a test database.

Java Why a bulk update leaves entities stale (hand-written session stand-in)

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.

Choosing between bulk and entity updates

Situation Better choice
Change thousands of rows with one rule bulk update
Need callbacks, cascades, auditing or @Version load entities and change them
Archive or purge old data bulk insert ... select and delete, in batches
Few rows, complex logic in Java entity updates

For very large purges, a single delete can lock a table for a long time and grow the transaction log. Delete in slices (where id between :a and :b) and commit between them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which HQL construct returns the first non-null of its arguments?

    coalesce returns the first argument that is not null. nullif returns null when its two arguments are equal.
  2. 2. After a bulk HQL update, what is true for entities already loaded in the same session?

    Bulk statements go straight to the database and do not touch the persistence context, so loaded entities are stale.
  3. 3. Which of these is NOT triggered by a bulk delete in HQL?

    Callbacks and cascades only run for entity-based operations. The cache region is invalidated, and executeUpdate returns the row count.
  4. 4. How do you make an HQL bulk update increment the @Version column?

    The versioned keyword tells Hibernate to add the version increment to the update.

Summary

  • HQL offers portable string, numeric, date and null functions; function('name', ...) reaches database-specific ones.
  • CASE, COALESCE and NULLIF let one query compute buckets, defaults and conditional counts.
  • executeUpdate() runs bulk update, delete and insert-select statements as single SQL statements and returns the row count.
  • Bulk statements bypass the persistence context, callbacks, cascades and (unless versioned) the version column.
  • Clear or refresh the session after a bulk statement, and purge big tables in slices.

Next: The Session Cache, Dirty Checking and Flushing