I’m building a REST service using Hibernate, Spring HATEOAS and Jackson. I am adding a method which returns a JSON representation of the results of a query like the one below;
SELECT ERRORS.DMN_NAM, CODES.MSG_TXT, FROM SERV_ERR ERRORS, EVENT_CD CODES WHERE ERRORS.SERV_RESP_CD_TXT = CODES.CD_TXT GROUP BY ERRORS.DMN_NAM, ERRORS.SERV_NAM, CODES.MSG_TXT, ERRORS.SERV_ERR_CNT, ERRORS.ERR_TS_NUM ORDER BY ERRORS.DMN_NAM, CODES.MSG_TXT
I currently have two objects defined (
EventCodeEntity) which map to the tables
So the results of this query will be a list, but not of
EventCodeEntity but rather an amalgamation of the two entities.
Up to now, my queries have all returned objects that map directly to one table like so:
public List<ErrorsEntity> getErrors(double daysPrevious, double hoursToShow);
What’s the best way to handle this in Hibernate where the results of a query aren’t objects that are mapped to a single table and how can I write this query in HQL?
It’s better to stick to an SQL query then, since HQL makes sense only when you plan on changing states from the resulted entities. In your case, the SQL is a better alternative, since it doesn’t really follow the standard and you only want a projection anyway. You could remove the group by with distinct but it will require a derived table, which can be done in plain SQL anyway.
List dtos = s.createSQLQuery( "SELECT " + " ERRORS.DMN_NAM AS dmnNam, " + " CODES.MSG_TXT AS msgTxt " + "FROM SERV_ERR ERRORS, EVENT_CD CODES " + "WHERE ERRORS.SERV_RESP_CD_TXT = CODES.CD_TXT " + "GROUP BY " + " ERRORS.DMN_NAM, " + " ERRORS.SERV_NAM, " + " CODES.MSG_TXT, " + " ERRORS.SERV_ERR_CNT, " + " ERRORS.ERR_TS_NUM " + "ORDER BY " + " ERRORS.DMN_NAM, " + " CODES.MSG_TXT " .addScalar("dmnNam") .addScalar("msgTxt") .setResultTransformer( Transformers.aliasToBean(MyDTO.class)) .list();
Make sure YourDTO has a matching constructor, and the types are the exactly like ee.dmn.nam and ece msgTxt.
Instead of group by I’d choose:
SELECT dmnNam, msgTxt FROM ( SELECT DISTINCT ERRORS.DMN_NAM AS dmnNam, ERRORS.SERV_NAM, CODES.MSG_TXT AS msgTxt, ERRORS.SERV_ERR_CNT, ERRORS.ERR_TS_NUM FROM SERV_ERR ERRORS, EVENT_CD CODES WHERE ERRORS.SERV_RESP_CD_TXT = CODES.CD_TXT ORDER BY dmnNam, msgTxt ) as DATA