Posts

Showing posts from November, 2012

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 o…

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 so…

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…

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 sp…