Fetching specific columns using list() hibernate

Question

I have Hibernate criteria query as follows:

Criteria criteria = session.createCriteria(Test.class);
ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("Month"));
projList.add(Projections.property("Year"));
criteria.setProjection(Projections.distinct(projList));

Here I am trying to fetch distinct values using Month and year similar to

select distinct Month, Year from table;

And finally after complete query I am using list() to fetch the criteria as:

criteriaList = criteria.list();

Now I have the criteriaList as Object with two column data Month and Year. I want to list the results of the query in criteriaList with only Month into a List<Integer>.

I have tried looping through the criteriaList and converting the fetched Object to List<Integer>, but it didn't work. How can I get only first column (Month) from the returned two columns?

Note: Setting projections to only one column (Month) will not help as I need distinct on both Month and Year

Update:

I have used the following types:

List<Object[]> criteriaList = Collections.emptyList();
List<Integer> Month = Collections.emptyList();

And since the number of months returned would be varying, I would like to add the fetched Months into a List<Integer> to perform further operations on.

malaguna · Accepted Answer · 2015-12-24T10:00:18.627

0

When using ProjectionList as you shown, hibernate returns List<Object[]> on criteria.list(). In your case, object array first element (0 index) is month, and second element (1 index) is year.

So you have to deal with a list of object array, as following:

List<Object[]> criteriaList = criteria.list();
List<Integer> monthList = Collections.emptyList();

for (Object[] row : criteriaList) {
    try{
        BigDecimal month = row[0];
        monthList.add(month.intValueExact());
    catch(Exception e){
        //To deal with casting Exception, NPE and 
        //ArithmeticException if int conversion fails
    }

    //Do what you want ...
}

Of course, I am supposing Projections.property("Month") is an Integer object, but I don't know, so you have to cast properly. The same for Year.

Of course, you can also use a resultTransformer.

Edit

I just edited my suggested block after you give more information on your comments.

edited Dec 24 '15 at 10:00

answered Dec 24 '15 at 09:26

malaguna

4,183
1
17
33

Thanks. Can you please suggest something based on my updated question? – Hemang Dec 24 '15 at 09:33
What is the type of `month` property in `Test.class`? – malaguna Dec 24 '15 at 09:35
I got the point and changed the type of month list to `List Month = Collections.emptyList();` but while adding `row[0]` to `Month` I get `UnsupportedOperationException` – Hemang Dec 24 '15 at 09:39
Well, try to cast to `BigDecimal` in a temporal variable, and then convert to `Integer` in order to add to the list. See this for conversion http://stackoverflow.com/questions/4043579/converting-bigdecimal-to-integer – malaguna Dec 24 '15 at 09:39
But when I get the value in `criteria.list()` its object, and if I am looping through the `Object[]` can I not directly add it to the `list`, of course after casting `Integer` to it? – Hemang Dec 24 '15 at 09:44
I edited my answer, it is not clear to me what do you want to do, but I think this answer your question. – malaguna Dec 24 '15 at 10:00
Yes I did the same. Thanks for help. Since your answer is similar to what I already did, accepting your answer. – Hemang Dec 24 '15 at 10:18

Fetching specific columns using list() hibernate

1 Answers1

Edit