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

Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, 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.
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!
Responses