How to build a query using Hibernate Criteria and Projections

Question

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

I formatted the code. Added tags. Edited the title of the question, to explain better what the question is about — CocoNess, Dec 22 '16 at 18:18
I am not facing any issue but is my answer code is appropriate to get the distinct data from the table. I am a bit worried that if the data are huge in the table it will be slow. I am looking alternate to my code if I can improve there. — Vimal Panchal, Dec 27 '16 at 05:40

score 0 · Accepted Answer · answered Dec 22 '16 at 17:33

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

How to build a query using Hibernate Criteria and Projections

1 Answers1