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