javahibernatedaohibernate-criterianhibernate-projections

How to build a query using Hibernate Criteria and Projections


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

Solution

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