How to build a query using Hibernate Criteria and Projections

348 views Asked by At

I want to build a query like below using Hibernate Projections attribute. Can someone check the below. I have written java code like.

DetachedCriteria dCriteria = DetachedCriteria.forClass(FinancialYearQuater.class, "FinancialYearQuater");
        dCriteria.add(Restrictions.eq("FinancialYearQuater.finYear", year));
        dCriteria.addOrder(Order.asc("finYear"));
        dCriteria.setResultTransformer(Projections.distinct(Projections.property("id")));
        List<FinancialYearQuater> list = (List<FinancialYearQuater>) findAll(dCriteria);

Here's the SQL query:

select
 distinct
        this_.FINY_NAME,
        this_.FINY_YEAR,
        this_.QTR_NAME,
        this_.QTR_NO,
        this_.QTR_PERIOD 
    from
        V_FINYR_QTR this_ 
    where
        this_.FINY_YEAR=2016
    order by
        this_.FINY_YEAR asc
1

There are 1 answers

0
Vimal Panchal On BEST ANSWER

I have written below code. Is that the correct way get the distinct data?

DetachedCriteria dCriteria = DetachedCriteria.forClass(FinancialYearQuater.class, "FinancialYearQuater");
        dCriteria.add(Restrictions.eq("FinancialYearQuater.finYear", year));
        ProjectionList projList = Projections.projectionList();
        projList.add(Projections.property("FinancialYearQuater.finYear"), "finYear");
        projList.add(Projections.property("FinancialYearQuater.finYearName"), "finYearName");
        projList.add(Projections.property("FinancialYearQuater.qtrNo"), "qtrNo");
        projList.add(Projections.property("FinancialYearQuater.qtrPeriod"), "qtrPeriod");
        dCriteria.setProjection(Projections.distinct(projList));
        dCriteria.addOrder(Order.asc("finYear"));
        List<FinancialYearQuater> list = (List<FinancialYearQuater>) findAll(dCriteria);