Sunday, October 30, 2011

JPA @Lob lazy loading

Suppose you have an Entity with (amongst others) a Lob field. How can you make sure the lob field is lazily loaded when your query returns a list of entity instances and you're only interrested in the other columns?
The first thing that comes to mind is instructing JPA to lazy load the field like this:
@Basic(FetchType.Lazy)
@Lob
byte[] attachment;

The problem with this is that in order to work the persistence provider needs to support it. When using Hibernate as JPA provider you have to instrument the bytecode at compile time.

What are the alternatives?

  • Create a separate Entity class containing all fields except the lob field and use this for the collection query.
  • Use a query with a projection to only include the non-lob fields

Sunday, October 23, 2011

JPA join queries need foreign key relationship

I needed a SQL query which is a projection of fields from five tables and looks like this in native SQL: (I reduced the number of tables in the code below to 2 for clearity)
SELECT refpoint.refid,refpoint.x,result.y 
FROM refpoint
LEFT OUTER JOIN result on result.refid = refpoint.refid
WHERE refpoint.refid IN (?,?)
Using QueryDsl and JPA I wrote this equivalent query:
QRefpoint refpoint = QRefpoint.refpoint;
QResult result = QResult.result;
JPQLQuery query = new JPQLQuery(entityManager);
long count = query(refpoint)
.leftJoin(result)
.with(result.refid.eq(refpoint.refid)
.where(refpoint.refid in (1,2)).count();
Running this code gave me:
16:07:20.619 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - Path expected for join! antlr.SemanticException: Path expected for join! at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:360) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final]
After much experimentation I found out that JPA (and Hibernate) do not support Joining without a mapped relationship. In this case the tables Refpoint and Result have a weak relationship based on refid and although a join in native sql is possible you cannot do this from JPA and/or Hibernate.
The solution I came up with was creating a Database View and corresponding JPA entity like this:
CREATE VIEW RefpointResult as 
SELECT refpoint.refid,refpoint.x,result.y 
FROM refpoint
LEFT OUTER JOIN result on result.refid = refpoint.refid
and in Java:
QRefpointResult refresult = QRefpointResult.refpointresult;
JPQLQuery query = new JPQLQuery(entityManager);
long count = query(refresult)
.where(refresult.refid in (1,2)).count();