How can I create this query in Hibernate?

Question

I have an SQL query and I'd like to translate it into Hibernate, the group by property is making me crazy:

select idestadoseccion,estado_1,estado_2,estado_3,estado_4,estado_5,fechaalta,idpaciente from estado_secciones 
where fechaalta between ? and ? and idestadoseccion 
in (select max(idestadoseccion) from estado_secciones where (estado_1=0 or estado_2=0 or estado_3=0 
or estado_4=0) group by idpaciente)

Answer 1

Hibernate maps your returned values to POJOs, you should specify what are you going to see in the result and add code of your POJOs for estado_secciones and idestadoseccion tables. After that it will be possible to rewrite this request to HQL.

Answer 2

Ok I solved it by making a native SQL query. The problem was that I couldn' add the group by property using hibernate. If I use a detachedcriteria with a Projection, Hibernate includes the group by property in the select sentence

select max(idestadoseccion),idpaciente from estado_secciones where (estado_1=0 or estado_2=0 or estado_3=0 or estado_4=0) group by idpaciente

And I don't want that. I only need to add it in the group by clause