DB2 Query to Hibernate Criteria

Question

Hi,

I have a specific DB2 query, and I would like to execute this query using criteria.

The query:

SELECT
 sum(units) as volume,
 location_id, 
 aged
FROM (
 SELECT
   units,
   location_id,

   CASE
    WHEN daysinstock < 61 THEN 'NOT_AGED'
    WHEN daysinstock < 91 THEN 'AGED'
    ELSE 'OVER_AGED'
   END AS AGED 

  FROM 
   STOCK_TABLE
) x
group by location_id, aged

the STOCK_TABLE contains the following fields: units, location_id, daysinstock. This table is matched by a StockDataSource object, with the same fields.

Answer 1

This is not available in the good ol' Hibernate Criteria API. Better write a HQL for this. Or, if you're actually using Hibernate for JPA2, then you can use CriteriaBuilder.Case.