DEV Community

Cover image for Hibernate criteria with subquery – using MAX projection and subqueries
Pawel Pawlak
Pawel Pawlak

Posted on • Edited on

8 4

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:

Image description

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();


Enter fullscreen mode Exit fullscreen mode

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();


Enter fullscreen mode Exit fullscreen mode

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();
  }


Enter fullscreen mode Exit fullscreen mode

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/

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

AWS GenAI Live!

GenAI LIVE! is a dynamic live-streamed show exploring how AWS and our partners are helping organizations unlock real value with generative AI.

Tune in to the full event

DEV is partnering to bring live events to the community. Join us or dismiss this billboard if you're not interested. ❤️