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] - 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
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!