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.

public class Employee implements Serializable{
   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', 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

No comments:

Post a Comment

OSX show used ports or listening applications with their PID

On OSX you can display applications listening on a given port using the lsof the commands described below will show listening application...