In this recipe, we will take a look at how to use subquery. Here, we will do the same thing as before; we will use the DetachedCriteria
class provided by the hibernate API. The DetachedCriteria
class works in detached mode
and is used to create a criteria query when the session is not available, as we can execute DetachedCriteria
with the existing session object.
We will create one scenario to show how DetachedCriteria
acts as a subquery.
The scenario is to get all the products whose categories have been recently added.
The preferred solution for this problem is as follows:
DetachedCriteria
to find the maximum createdOn
dateConsider the following code:
/* Line 1 */ DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Category.class); /* Line 2 */ detachedCriteria.setProjection(Projections.max("createdOn")); /* Line 4 */ Criteria criteria = session.createCriteria(Product.class); /* Line 5 */ criteria.createAlias("category", "cat"); /* Line 6 */ criteria.add(Subqueries.propertyEq("cat.createdOn", detachedCriteria)); List<Product> list = criteria.list(); for(Product product : list){ System.out.println(" Product id: " + product.getId()); System.out.println("Product name: " + product.getName()); System.out.println("Product price: " + product.getPrice()); System.out.println("Category name: " + product.getCategory().getName()); }
Hibernate: select this_.id as id0_1_, this_.category_id as category4_0_1_, this_.name as name0_1_, this_.price as price0_1_, cat1_.id as id1_0_, cat1_.created_on as created2_1_0_, cat1_.name as name1_0_ from product this_ inner join category cat1_ on this_.category_id=cat1_.id where cat1_.created_on = (select max(this_.created_on) as y0_ from category this_) Product id: 4 Product name: Business envelopes Product price: 40.92 Category name: Stationary Product id: 5 Product name: Paper clips Product price: 20.61 Category name: Stationary Product id: 6 Product name: Highlighters Product price: 30.0 Category name: Stationary
From the output, it's clear that we have the last inserted category, which is Stationary
; so, we got all the products under the Stationary
category.
Line 1
and 2
from the preceding code show that we want to create a DetachedCriteria
object for the Category
class. Here, the task of DetachedCriteria
is to find the maximum createdOn
date from the category table.
In Line 4
, we created the Criteria
object for the Product
class. In Line 6
, we passed an object of DetachedCriteria
in the SubQueries
object, so hibernate will create a subquery for DetachedCriteria
.