Select Custom data with Cayenne return empty lines

Question

I'm trying to select data from 2 diff mysql tables:

SELECT 

    SUM(TIMESTAMPDIFF(HOUR, startdatetime, enddatetime)), 
    SUM(TIMESTAMPDIFF(HOUR, startdatetime, enddatetime) * hourly),

    SUM(CASE 
    WHEN TIME(startdatetime) BETWEEN '06:00:00' AND '12:00:00' THEN 'test', 

    SUM(CASE 
    WHEN TIMESTAMPDIFF(HOUR, startdatetime, enddatetime) <= 9 THEN 'TEST'
    ELSE 'TEST2'
    END) 

    FROM date, tarif
    GROUP BY userid, MONTH(startdatetime), YEAR(startdatetime)

In my code, I have :

SQLTemplate select = new SQLTemplate(DateInfo.class, query);
return CayenneUtil.getContext().performQuery(select);

I tried too :

CayenneUtil.getContext().performGenericQuery(select).firstList()

But I received 4 empty lines instead of 4 lines with data.

What should I do?

Answer 1

You should set select.setFetchingDataRows(true) as by default SQLTemplate will try to convert result into data objects (DateInfo in your case). After this flag set query will return List<DataRow> and DataRow is a Cayenne internal extension of plain java.util.HashMap.