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, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, 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.
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.
