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.

Join the Persistence Hub!

---

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.

Question:

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

Solution:

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

Function	Description
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] -
    select
        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_
    from
        Author author0_ cross
    join
        BookAuthor books1_
    where
        author0_.id=books1_.authorId
    group by
        author0_.id

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.