skip to content

Which functions can you rely on in a portable JPQL query, and how do you call a database-specific function that JPQL does not define?

level: middleimportance: should knowfreq 44%

answer

  1. Small portable set: string/number/date/case
  2. FUNCTION('name', args) escape (JPA 2.1)
  3. CURRENT_TIMESTAMP = database clock
  4. SUBSTRING/LOCATE are 1-based
  5. Recurring vendor function → register it once

basics

~10 s

JPQL defines a small set: string (concat, substring, trim, lower, upper, length, locate), arithmetic (abs, mod, sqrt, size, index), datetime (current_date/time/timestamp), plus coalesce, nullif and CASE. Anything else goes through FUNCTION('name', args).

solid answer

~50 s

The portable set is deliberately small: - **String**: `CONCAT`, `SUBSTRING`, `TRIM`, `LOWER`, `UPPER`, `LENGTH`, `LOCATE`. - **Arithmetic**: `ABS`, `MOD`, `SQRT`, and the collection-oriented `SIZE`, plus `INDEX` for ordered collections. - **Temporal**: `CURRENT_DATE`, `CURRENT_TIME`, `CURRENT_TIMESTAMP` — evaluated by the database, not the JVM. - **Conditional**: `CASE ... WHEN ... THEN ... ELSE ... END`, `COALESCE`, `NULLIF`, and `TYPE()` for polymorphic checks. - **Aggregates**: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`. For anything else — `date_trunc`, `jsonb_extract_path`, `regexp_like`, a stored function — use the JPA 2.1 escape: `FUNCTION('date_trunc', 'month', o.createdAt)`. The name is passed to the database as written, so the query stops being portable and nothing validates the arguments. Hibernate additionally registers many functions per dialect that you can call directly in HQL, and lets you contribute your own through a `FunctionContributor`, which gives one name that maps to different SQL per database — the portable way to use a non-standard capability.

code

java · 10 lines
java
em.createQuery("""
    select upper(b.title),
           coalesce(b.subtitle, ''),
           case when b.price > 50 then 'PREMIUM' else 'STANDARD' end
      from Book b
     where length(b.title) > 10
       and function('date_trunc', 'month', b.publishedAt) = :month
    """, Object[].class)
  .setParameter("month", month)
  .getResultList();

go deeper

for a junior

List a few standard functions and know that non-standard ones need the FUNCTION escape.

for a middle

Give the portable inventory by category, use CASE/COALESCE, and explain the escape's syntax and its portability cost.

for a senior

Choose deliberately between standard function, dialect-registered function, custom registration and native query, and flag SIZE and 1-based indexing traps.

for a principal

Treat vendor-specific SQL as a boundary decision: centralise it in registered functions or native queries so the rest of the query layer stays portable and reviewable.

## Why the standard set is small JPQL must run unchanged on every supported database, so the specification standardises only functions that essentially all of them provide with compatible semantics. Everything richer — regular expressions, JSON access, date truncation, full-text search, window functions — is left out precisely because vendors disagree on names, argument order and edge behaviour. ## The portable inventory **Strings.** `CONCAT(a, b)`, `SUBSTRING(s, start, len)` (1-based), `TRIM([LEADING|TRAILING|BOTH] [char] FROM s)`, `LOWER(s)`, `UPPER(s)`, `LENGTH(s)`, `LOCATE(search, s [, start])` returning a 1-based position or 0. **Numbers.** `ABS(n)`, `MOD(a, b)`, `SQRT(n)`. Ordinary arithmetic operators work too. **Collections.** `SIZE(e.items)` returns the collection's element count as a subquery or join-based count; `INDEX(alias)` gives the position within an `@OrderColumn` list. Operators `IS EMPTY`, `MEMBER OF`, `EXISTS` complete the set — these have no direct SQL equivalent and are a genuine JPQL affordance. **Time.** `CURRENT_DATE`, `CURRENT_TIME`, `CURRENT_TIMESTAMP` render the database's clock functions. That matters: the value comes from the database server, so it does not depend on the application node's clock or time zone, and it is not something you can stub in a unit test. When determinism matters, bind an instant as a parameter instead. **Conditionals.** `CASE` in both simple and searched forms, `COALESCE(a, b, ...)`, `NULLIF(a, b)`. These are how you express branching inside a query without dropping to native SQL. **Aggregates.** `COUNT` (including `COUNT(DISTINCT x)`), `SUM`, `AVG`, `MIN`, `MAX`, used with `GROUP BY`/`HAVING`. ## The FUNCTION escape JPA 2.1 added a generic invocation form: ``` select o from Order o where function('date_trunc', 'month', o.createdAt) = :month ``` Rules and caveats: - The first argument is the **native function name**, passed through to SQL essentially verbatim. - Remaining arguments are JPQL expressions — paths, literals and parameters — so values are still bound properly. - Nothing validates the name or arity; a typo surfaces as a database error at execution time. - The return type is inferred loosely; you often need a cast or a careful comparison, and providers vary in how well they type the expression. - Portability is gone for that query. Switching databases means editing the query text. It is the right tool for a one-off, and the wrong tool if the same non-standard function appears in twenty queries. ## Hibernate's dialect function registry Hibernate maintains a registry of functions per dialect and lets you call registered ones by name directly in HQL — `str()`, `cast()`, `extract()`, `year()`, and a long list of dialect-specific entries. Hibernate 6 also standardises many functions across dialects, emitting whatever native SQL each database needs, which quietly restores portability for things like `extract(year from ...)`. For your own additions, implement a `FunctionContributor` (Hibernate 6; older versions used `MetadataBuilder.applySqlFunction` or a custom dialect). You register a logical name once, with an SQL template per dialect if needed, and then queries call that logical name. Now the query text is stable and the vendor difference lives in one configuration class — the same benefit the standard functions give you. ## Choosing between the options 1. If a standard JPQL function does the job, use it — free portability, fully typed. 2. If Hibernate registers it for your dialect, call it directly in HQL — no query-text vendor lock, mild provider lock. 3. If it is a genuine one-off vendor feature, use `FUNCTION(...)` and comment why. 4. If it recurs, register it once via a `FunctionContributor`. 5. If the query is fundamentally vendor-shaped — window functions over a JSON document, hierarchical traversal, full-text ranking — stop fighting and use a native query; that is what native queries exist for. ## Two traps worth naming `SUBSTRING` and `LOCATE` are **1-based**, unlike Java's `String.substring`, and off-by-one bugs from that mismatch are common. And `SIZE()` is not free: it becomes a correlated subquery or a grouped join, so in a WHERE clause over a large table it can dominate the query cost — `IS NOT EMPTY` or an `EXISTS` subquery is usually the cheaper spelling of "has at least one".

  • What do you lose by using FUNCTION('date_trunc', ...) in a query?
    Portability and validation. The name is emitted to the database as written, so the query only runs where that function exists, and a misspelling or wrong arity is discovered at execution time rather than at bootstrap. Registering a logical function with Hibernate instead keeps one query text and moves the vendor difference into configuration.
  • Why can SIZE(e.items) be expensive in a WHERE clause?
    It is rendered as a correlated subquery or a grouped join that counts child rows for every candidate parent, so on a large table it multiplies work. When the question is merely 'does it have any', IS NOT EMPTY or an EXISTS subquery lets the database stop at the first matching row instead of counting all of them.

saying these in an interview costs you the question

  • Assuming any database function can be called directly in portable JPQL
  • Treating SUBSTRING and LOCATE as 0-based like Java string methods
  • Believing CURRENT_TIMESTAMP is the application JVM's clock
  • Using FUNCTION(...) everywhere and still calling the code database-agnostic
  • Reaching for SIZE() when EXISTS or IS NOT EMPTY would answer the same question

context

resolving links…