Hibernate Tips: How to call a standard function in a JPQL query

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question you like me to answer, please leave a comment below.


How do I call a database function in a JPQL query?


JPQL supports the following set of database functions that you can use in the SELECT and WHERE clause of your queries.

upper(String s)Transforms String s to upper case
lower(String s)Transforms String s to lower case
current_date()Returns the current date of the database
current_time()Returns the current time of the database
current_timestamp()Returns a timestamp of the current date and time of the database
substring(String s, int offset, int length)Returns a substring of the given String s
trim(String s)Removes leading and trailing whitespaces from the given String s
length(String s)Returns the length of the given String s
locate(String search, String s, int offset)Returns the position of the String search in s. The search starts at the position offset
abs(Numeric n)Returns the absolute value of the given number
sqrt(Numeric n)Returns the square root of the given number
mod(Numeric dividend, Numeric divisor)Returns the remainder of a division
treat(x as Type)Downcasts x to the given Type
size(c)Returns the size of a given Collection c
index(orderdCollection)Returns the index of the given value in an ordered Collection

The following code snippet shows a query that calls the size function on the books association.

Query q = em.createQuery("SELECT a, size(a.books) FROM Author a GROUP BY a.id");
List<Object[]> results = q.getResultList();

The size function is JPA specific. You can use it to count the elements in a mapped association. As you can see in the log message, Hibernate generates a JOIN statement to join the associated table and calls the SQL count function to count the number of associated records in the book table.

05:47:23,682 DEBUG [org.hibernate.SQL] -
        author0_.id as col_0_0_,
        count(books1_.authorId) as col_1_0_,
        author0_.id as id1_0_,
        author0_.firstName as firstNam2_0_,
        author0_.lastName as lastName3_0_,
        author0_.version as version4_0_
        Author author0_ cross
        BookAuthor books1_
    group by

Learn more

JPQL supports only a subset of the functions supported by the SQL standard and no database-specific functions. Since JPA 2.1, you can use the function function to call functions unsupported by the JPA standard in a CriteriaQuery.

Hibernate Tips Book

Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.