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.

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.

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