Skip to main content

Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » How to add a Window Function to a JPA Criteria Query?
How to add a Window Function to a JPA Criteria Query? [message #1752962] Tue, 31 January 2017 11:48
Joachim Kanbach is currently offline Joachim KanbachFriend
Messages: 2
Registered: July 2009
Junior Member
Hi all,

I'm trying to combine a JPA Criteria Query with a Window Function. In addition, I also use Batch Fetching and a Fetch Graph. What I have looks conceptionally like this:

CriteriaQuery<SomeEntity> c = cb.createQuery( SomeEntity.class );
Root<SomeEntity> someEntity = c.from( SomeEntity.class );;

[ ... predicates constructed dynamically and applied here ... ]

TypedQuery<SomeEntity> query = em.createQuery(c);
query.setHint(QueryHints.JPA_FETCH_GRAPH, em.getEntityGraph(SomeEntity.SOME_FETCHGRAPH));
query.setFirstResult( [...] );
query.setMaxResults( [...] );

JpaQuery<?> jpaQuery = query.unwrap(JpaQuery.class);
ObjectLevelReadQuery objectQuery = (ObjectLevelReadQuery) jpaQuery.getDatabaseQuery();

objectQuery.setBatchFetchSize( [...] );

List<SomeEntity> result = query.getResultList();
[ ... ]

So far, this all works fine. But now I'd like to add the Window Function COUNT(*) OVER () to the query. I've fiddled with the different features for customization in EclipseLink (mostly inspired by this thread: and arrived at a solution that seemingly works, but as it appears to me, only by accident.

First, I added an attribute "totalCount" to my JPA entity SomeEntity, which is meant to hold the result of the Window Function:

@ReadTransformer(transformerClass = TotalCountTransformer.class)
public long getTotalCount()
   return totalCount;

Then I defined a DescriptorCustomizer like this:

public void customize(ClassDescriptor descriptor) throws Exception
   TransformationMapping mapping = new TransformationMapping();
   mapping.setAttributeTransformer(new TotalCountTransformer()); // will be defined below

I couldn't find a way to actually have COUNT(*) OVER () appended to the SELECT clause of my query using the DescriptorCustomizer. So I randomly tried to modify my objectQuery from above using this:

ExpressionBuilder eb = new ExpressionBuilder();
objectQuery.addAdditionalField(eb.postfixSQL("(COUNT (dbid) OVER ())").as("totalCount"));
// INTERNAL API, not meant to be used this way!?

This is actually picked up in the SELECT clause. My final struggle was to get hold of the value of this expression in my AttributeTransformer. Through debugging, I found that the ArrayRecord that is passed to buildAttributeValue uses a key of "*", so this implementation of an AttributeTransformer did the trick:

public Object buildAttributeValue(Record record, Object object, Session session)
   return record.get("*");

The constructed SomeEntity objects have their totalCount attribute filled with the correct result.

Could someone please give me directions on how to implement this *properly*? Note, I'm aware of CriteriaQuery.multiselect(), which I've sucessfully used elsewhere to select a Tuple, including a raw SQL expression like this Window Function. But this wouldn't work here because of the Fetch Graph (and I think the Batch Fetching too).

Best regards,
Joachim Kanbach
Previous Topic:Error unmarshaling xml with MTOM attachment
Next Topic:duplicate tenant_id in WHERE clause for @Multitenant
Goto Forum:

Current Time: Sat Aug 18 10:11:02 GMT 2018

Powered by FUDForum. Page generated in 0.02231 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top