How to fetch hibernate query result as associative array of list or hashmap

Question

I am developing an application in struts 2 hibernate 3.

I have 3 tables

1. Inspection
2. InspectionMission
3. Timeline

Inspection is associated with InspectionMission and InspectionMission is associated with Timeline.

Now I have following problem. I have written following query in HQL

public List getQuartewiseInspectionList(){

Session session = HibernateUtil.getSessionFactory().getCurrentSession();

    Query q = session.createQuery(
            "select count(i.inspectionId) as tot_inspections,t.year,t.quarter" +
            " From Inspection as i " +
            " inner join i.inspectionMission as im inner join im.timeline as t" +
            " GROUP by t.year,t.quarter");

    return q.list();

}

I want to fetch result as following

result[0][tot_inspections] = "6" result[0][year] = "2009"; result[0][quarter] = "Q2";

result[0][tot_inspections] = "3" result[0][year] = "2009"; result[0][quarter] = "Q3";

and so on so that I can display it in jsp struts as follows

In JSP I have written following code

evenodd">

can anyone here help me in this matter.

Thanks.

Answer 1

You have to use the "new map" syntax (Hibernate Reference paragraph 14.6)

select new map(count(i.inspectionId) as tot_inspections, t.year as year, t.quarter as quarter) from ...

The rest of the query is the same. This will return a list of maps, where the key is the alias of the "column".

Answer 2

Another solution would be to define a data object just for displaying those results and let Hibernate create instances of those on the fly. This class would just need a matching constructor.

Example class (getters and fields omitted)

public class InspectionCount() {
    // fields
    public InspectionCount(int count, int year, int quarter) {
        // initialize instance
    }
    // getters
}

The query would then look

select new InspectionCount(count(i.inspectionId), t.year, t.quarter)
        from Inspection as i
        inner join i.inspectionMission as im inner join im.timeline as t
        group by t.year,t.quarter

As a result you would get a List of InspectionCounts.