I have this query in mysql:
select distinct Month(date), year(date), field3
from table
I wrote this code:
Query q = em.createQuery(
"select distinct function('month',t.date) as month, function('year',t.date) as year, field3 as field3 from Table t"
);
I tried to cast the results into an HashMap<String, String>
but jpql return a ClassCastException
from Object to HashMap.
How can I retrieve these results? Should I create a custom class that contains the three fields? If it is correct, what will return month and year?
P.S.: I'm using jdk 1.8, jpa 2.1, mysql and eclipselink
You can retrieve the result by typing the expected result of the query.
A generic way is to indicate that you want retrieve a List of Object[].
The List corresponds to each line returned by the request and the Object[] contains values returned by each row. The object are ordered in the same order that values returned.
Alias are not needed because not used in the result. So I remove them.
And you should not query from the table name otherwise it looks like a SQL native query (which is possible to do with JPA) but you should rather specify in the FROM clause the entity name. So I modified it for the principle.
Here the modified query with the handling of the result :
TypedQuery<Object[]> query = em.createQuery(
"select distinct function('month',t.date) ,
function('year',t.date) , field3 from YourEntity t", Object[].class);
List<Object[]> results = query.getResultList();
for (Object[] result : results) {
System.out.println("month: " + result[0] + ", year: " + result[1]
+ ", field3:"+ result[2]);
}
It is not the most beautiful way to retrieve the data but if it matches to your need, you don't need to do more.
Should I create a custom class that contains the three field?
it's practical to do it if this custom class is reused elsewhere. Otherwise, it looks like more a overhead.