Hibernate criteria with subquery – using MAX projection and subqueries

Here is an example how hibernate criteria can be used with subquery. Lets imagine we have an application that is used for presenting to the user in the UI different types of poster templates for different kind of events. You can imagine that a rock concert will be having a bit different poster then a night at the opera. So there are several poster template types like: concert, outdoor, piknik, theatre, church, running, and many many more. User can find them all in the table which shows him the type, creation date, who created/uploaded given image/poster, and a picture thumbnail.

Data table for this kind of product could look like that:

Simple criteria query that is used for getting all poster templates should look like that

List<PosterTemplate> posters = DetachedCriteria.forClass(PosterTemplate.class)
        .addOrder(Order.asc("creationDate"))
        .getExecutableCriteria(entityManager.unwrap(Session.class))
        .list();

For now each of this template can be downloaded and overwritten/uploaded by the user.

Now there comes new change requirements, those are:

• user can upload a new version of given poster type, old one is kept, and store as old version

• user is provided with the latest version of given temple type

• user by selecting right click on given template can see its
  available versions and select/download it if needed

This change requires changing the query, to deliver to the users all template types but with the latest/bigger version numer.

First of all we add new column VESRION_NUMBER to the db with default value set to 1. Each time user upload new template of given type version number is increased by one.

Now I figure out first of all a classic way using hql to get those templates. My query looked like that:

entityManager.createQuery("select pt1 from PosterTemplate pt1 where pt1.versionNumber ="
           + " (select max(pt2.versionNumber) from PosterTemplate pt2 where pt2.type = t1.type) ",
            PosterTemplate.class).getResultList();

This query gives me what I wanted. Although I have already a working solution, I was tempted to rewrite it, using the hibernate criteria. I am for sure not an expert in that area, but I wanted to challenge myself and figure a way to have the same results but with the use of the criteria. After some time of thinking I was able to come up with this working solution:

public List<PosterTemplate> getAllPosterTemplatesWithLatestVersions() {
    DetachedCriteria subCriteria = DetachedCriteria.forClass(PosterTemplate.class, "inner")
        .add(Restrictions.eqProperty("inner.type", "outer.type"))
        .setProjection(Projections.max("inner.versionNumber"));

    return DetachedCriteria.forClass(PosterTemplate.class, "outer")
       .add(Subqueries.propertyEq("outer.versionNumber", subCriteria))
       .getExecutableCriteria(entityManager.unwrap(Session.class)).list();
  }

This implementation should give us the same output as the one provided before using the hql.

Hope it can be useful for someone.

https://developersmill.com/