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?
Don’t want to read? You can watch it here!
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.