CODEWITHSUNDEEP
javahibernatejpawindow-functionscriteria-api
Iaroslav Postovalov
Iaroslav Postovalov

Reputation: 2463

How do I use row_number (or anything with same behavior) with Hibernate Criteria API?

I want to implement a similar query with CriteriaBuilder API:

SELECT *, ROW_NUMBER() OVER( ORDER BY Student_Score) AS RowNumberRank
FROM StudentScore

The main problem is that JPQL doesn't support window functions and I don't know how to replicate their behavior...

Upvotes: 0

Views: 1794

Answers (2)

FutureFlash94
FutureFlash94

Reputation: 39

I just had the same problem and really wanted to use the Criteria API. My solution using SpringBoot 2 and hibernate 5:

  1. Create the StandardSQLFunction:
public class RowNumberSQLFunction extends StandardSQLFunction {
    
  public RowNumberSQLFunction() {
    super("row_number", StandardBasicTypes.BIG_INTEGER);
  }
    
  @Override
  public String render(Type firstArgumentType, List arguments, SessionFactoryImplementor sessionFactory) {
    return "row_number() over ( order by " + arguments.get(0) + ")";
  }
}

In the argument list can be anything, so you can pass what ever you like to this sql function. I use it to set the parameter for "partition by" and "order by".

  1. Register the new SqlFunction over the MetadataBuilderContributor:
public class CustomMetadataBuilderContributor implements MetadataBuilderContributor {
    
  @Override
  public void contribute(MetadataBuilder metadataBuilder) {
    metadataBuilder.applySqlFunction("row_number", new RowNumberSQLFunction());
  }
}
  1. Tell Hibernate, that there is a MetadataBuilderContributor: In my case it was over the SpringBoot application.properties:
spring.jpa.properties.hibernate.metadata_builder_contributor=path.to.CustomMetadataBuilderContributor
  1. Use the ROW_NUMBER function with criteriaBuilder.function():
  CriteriaBuilder cb = entityManager.getCriteriaBuilder();
  CriteriaQuery<Tuple> cq = cb.createTupleQuery();
  Root<StudentScore> from = cq.from(StudentScore.class);
  Expression<BigInteger> rownumber = cb.function("row_number", BigInteger.class, from.get("score"));
  cq.select(cb.tuple(from, rownumber));
  TypedQuery<Tuple> tq = em.createQuery(cq);
  List<Tuple> resultList = tq.getResultList();

Hope this helps everyone struggling around a nice solution

Upvotes: 3

FredvN
FredvN

Reputation: 544

Analytical functions are vendor specific. This makes is hard to implement with a CriteriaBuilder API. Using native query ties your java application to a specific database vendor. To avoid that you can create a vendor specific view and create in your java application a select with the criteria api using that view.

Beside the StudentScore entity you already have you had to define a (readonly) StudentScoreView entity with the extra attribute rowNumberRand which you can use in your JPQL query.

Upvotes: 2

Related Questions