[eclipselink-users] How to add a Window Function to a JPA Criteria Query

["Joachim Kanbach" <jo.ka@xxxxxx>]

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 );
   c.select(someEntity);

   [ ... 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.addBatchReadAttribute(jcb.toExpression(someEntity.get(SomeEntity_.joinedEntities)));
   objectQuery.setBatchFetchType(BatchFetchType.IN);
   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: https://www.eclipse.org/forums/index.php/t/158462/) 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:

   @Override
   public void customize(ClassDescriptor descriptor) throws Exception
   {
      TransformationMapping mapping = new TransformationMapping();
      mapping.setAttributeName("totalCount");
      mapping.setAttributeTransformer(new TotalCountTransformer()); // will be defined below
      descriptor.addMapping(mapping);
   }

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:

   @Override
   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