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.
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.
Get this Hibernate Tip as a printable PDF!Join the free Thoughts on Java Library to get access to lots of member-only content, like a printable PDF for this post, lots of cheat sheets and 2 ebooks about Hibernate.
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
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 as a paperback, ebook or PDF.