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:
- First, we will create
DetachedCriteriato find the maximumcreatedOndate - Then, we will use the result of the first query to check the date of the product's category
Consider 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.