CODEWITHSUNDEEP
javahibernatejpaormhql
conorgriffin
conorgriffin

Reputation: 4339

How do I form this complex query in Hibernate?

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 (ErrorsEntity and EventCodeEntity) which map to the tables SERV_ERR and EVENT_CD.

So the results of this query will be a list, but not of ErrorsEntity or 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?

Upvotes: 1

Views: 679

Answers (1)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154190

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

Upvotes: 2

Related Questions