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

JPA Eclipselink optimizing query performance on a large resultset

Recently I had to look for ways of optimizing a very slow database request on a very large ResultSet (more than 5000 results with several @OneToMany associations).

While reading EclipseLink's documentation I stumbled upon an interesting piece of information regarding handling large ResultSets. EclipseLink recommends using Streamed Cursors when handling large resultsets

According to the documentation : Cursored streams provide the ability to read back a query result set from the database in manageable subsets, and to scroll through the result set stream.

The initial query would load practically the entire database into memory in one request (all the associations are marked with QueryHint.LEFT_FETCH in order to eagerly fetch the associations upon the initial request).

I will not go into details regarding the process since it's irrelevant to this post not the goal of this post, but basically the program would query an Oracle Database transform every JPA Entity and store it in a somewhat different object in a Mongo Database

The process was really long (more than 1 hour between recovering all the objects from the Oracle Database and processing them into the Mongo Database

The DAO class

public CursoredStream getMyLargeResultset(){

   Query programsQuery = em.createNamedQuery("MyNamedQuery");
   // tell the EntityManager to return a StreamedCursor
   programsQuery.setHint("eclipselink.cursor", true);    
   return (CursoredStream) programsQuery.getSingleResult();
}

The client

public void synchronizeDatabases(){


    final int PAGE_SIZE = 50;

    //get the cursor
    CursoredStream cursor= myDao.getMyLargeResultset();
      
    // iterate through curor 
    cursor(!cursor.atEnd()){ 
 
        // get the next batch of objects and
        // cast it to the target entity 
        List myEntities = (List)(List)cursor.next(PAGE_SIZE);
 processEntities(myEntities);
      }

    cursor.close();

}

By using this technique I was able to reduce by a factor of 10 the process time of this operation!

PS: I realize that the double casting on the List is not very pretty and I could use a different approach by using the next() method without arguments but the existent method processEntities() accepted a List as a parameter and I wasn't allowed to modify that method

Documentation sources

• EclipseLink Advanced Query API
• EclipseLink Advanced Query API (Cursors)

MongoDB $where clause to query array length

There is no direct way in mongodb to return all the documents in where a sub collection has at least X number of entries

  {
    "name" : "Terry Brooks",
    "books" : [ 
              "The Sword of Shannara", 
               "The Elfstones of Shannara",
               "The Wishsong of Shannara"
              ]
  },
 {
    "name" : "Oscar Wilde",
    "books" : [ 
              "The Picture of Dorian Gray"
             
              ]
  }

Let's say that I want all the authors that have written more than 1 book. There is no direct way in mongodb to do this. it needs to be done either by map reduce or perhaps with the new aggregation framework but you cannot combine $gt and $size operators like so :

    db.AUTHORS.find({"books" : {$size : {$gt : 1}}});

It doesn't work, you wont get any error messages but an empty result. MongoDb allows Javascript evaluation through the $where operator although it's significantly slower than native operators it's very flexible and a quick way of executing a query without using map reduce or other means :

    db.AUTHORS.find({$where : "this.books.length > 1"});

But when this query was executed the following error kept coming up :

{
    "error": {
        "$err": "erroroninvocationof$wherefunction: JSError: TypeError: this.bookshasnopropertiesnofile_a: 0",
        "code": 10071
    }
}

The error is not very helpfull (at least to me) and as it turns out the origin of the problem was the fact that not all Author documents in my database had the "books" array. So in order to execute a length query on the "books" array it's necessary to ensure that the array field (books) exists :

    db.AUTHORS.find({"books" : {$exists: true}, $where : "this.books.length > 0"});

EclipseLink (JPA) Spring - No persistence exception translators exception

I recently stumbled upon a Spring Exception while working with Spring Data JPA and attempting to deploy a WAR file on glassfish. I have a JTA datasource defined in my glassfish which is used by Spring (through JNDI) to instantiate my entity manager. The JPA configuration was pretty straight forward but every time I tried to deploy the app on Glassfish I stumbled upon the error :

org.springframework.beans.factory.BeanCreationException: 
Error creating bean with name 
'org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor#0': 
Initialization of bean failed; 
nested exception is java.lang.IllegalStateException: 
No persistence exception translators found in bean factory.
Cannot perform exception translation.

Apparently when using Hibernate there is an easy fix for this : Declaring a Hibernate Exception Translator in the spring beans config file :

When using EclipseLink though this doesn't work... So I looked for implementations of the spring interface :

org.springframework.dao.support.PersistenceExceptionTranslator 

And found out that there is not an EclipseLinkExceptionTranslator (as for Hibernate) but there is a EclipseLinkJpaDialect

   org.springframework.orm.jpa.vendor.EclipseLinkJpaDialect

That implements the interface so I created a bean in the spring configuration file and I was able to deploy the app on the glassfish.