Custom Database function using JPA JPQL and eclipselink

Sometimes it's useful to exploit some of the native functions provided by database vendors while using JPA and JPQL.

And although it's possible to execute native queries, you'll loose some of the JPA magic in the process.

Thankfully Eclipselink (and probably Hibernate) allow some degree of flexibility through special operators.

According to the official documentation the FUNC operator allows for a database function to be call from JPQL. It allows calling any database functions not supported directly in JPQL, and calling user or library specific functions.

@Entity
public class Employee implements Serializable{
   
   @Id
   private Long id;   
   private String firstname;
   private String lastname;

  // Setters - Getters

}

Below is a quick example on how to use FUNC to call Oracle's TO_CHAR function and convert all the ids in the query to a String.

Let's say that for an obscure reason we would like to get a list of all the employees id's but not as a list of java.lang.Long but as a list of java.lang.String.

We could write the query like so :

List employeesId = 
     em.createQuery("SELECT FUNC('TO_CHAR', e.id) FROM Employee e ").getResultList();

The usage is pretty straightforward the first argument is the name of the native function followed by the function arguments

More information about this can be found in Eclipselink's official documentation here

It's good no note that FUNC will be changed to FUNCTION in JPA 2.1