Add a Group by to a Hibernate Criteria-Query without Projection

Question

I have a Criteria-Query, which joins a second table B, to select entities from table A. The problem is, that this query returns some entities from table A multiple times. But I need the results to be distinct.

Using Criteria.DISTINCT_ROOT_ENTITY is useless, becaus this filters out the multiple occurences after the SQL-Query was executed. So, when I limit my results to 20 hits, I end up with only 4, though there are more entries, that match my query.

In pure SQL I simply can add a "GROUP BY ID" to the query and everything is fine, because the join of table B is only used, to select the entities from table A. But with the Criteria-API I cannot do this. The only way to add a "GROUP BY" is by using Projections. But then, I end up with scalar values, not with a real instance of my class. Using a SQL-restriction does not work either, because hibernate adds a bogous "1=1" after my "GROUP BY"-clause. :(

Any ideas?

Answer 1

It is possible to write actual SQL Queries which Hibernate can use to return entities. So if you really need to, you can bypass HQL and write exactly the query you want with your GROUP BY in it.

See here for details.

For example you can define a query something like this in your hbm.xml file:

<sql-query name="exampleQuery">
<query-param name="param" type="int"/>
<return alias="x" class="foo.bar.X"/>
<return alias="y" class="foo.bar.Y"/>
    <![CDATA[
        select {x.*}, {y.*}
        from XEntity x
        inner join YEntity y on y.xId = x.id
        where y.value = :param
    ]]>
</sql-query>

Note the {x.} and {y.} shorthand syntax for selecting all the properties of entity X and entity Y