how to create a hibernate distinct query

Question

I have taken a look around the web and can not really find the clear answer on this.

I have two tables A and B. B is a child of A. I need to get a list of distinct attributes from B based on some restrictions of A.

For example:

SQL:

select distinct sirm.attribute
from store_item_received_material sirm
where sirm.store_item_id in (select si.id from store_item si where si.program_id = 9 and si.customer_id = 1 and si.date_processed is not null);

Of course the SQL works great.

Now, I need to run this in my project.

I am running hibernate 3.3.1. I tried the following:

@NamedNativeQueries ({
    @NamedNativeQuery (name = "select.distinct.sirm.for.customer.program", query = "select distinct(sirm.attribute) as attribute from store_item_received_material as sirm where sirm.store_item_id in (select si.id from store_item as si where si.customer_id = ? and si.program_id = ? and si.date_processed is not null)")
})

But that failed with the following error:

nested exception is org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported

So I tried the following:

@NamedNativeQueries ({
    @NamedNativeQuery (name = "select.distinct.sirm.for.customer.program", query = "select distinct(sirm.attribute) as attribute from store_item_received_material as sirm where sirm.store_item_id in (select si.id from store_item as si where si.customer_id = ? and si.program_id = ? and si.date_processed is not null)", resultClass=StoreItemReceivedMaterial.class)
})
@SqlResultSetMapping(name = "select.distinct.sirm.for.customer.program", entities=@EntityResult(entityClass = StoreItemReceivedMaterial.class))

But that does not work either since the object is a entity object and does not have an ID column.

So, any help in how to do this

Looks like a duplication of http://stackoverflow.com/questions/263850/how-do-you-create-a-distinct-query-in-hql — FoxyBOA, Feb 15 '11 at 05:38

score 0 · Answer 1 · answered Feb 15 '11 at 08:57

For scalar queries you need resultset mapping with @ColumnResult:

@NamedNativeQueries ({
    @NamedNativeQuery (name = "select.distinct.sirm.for.customer.program",
        query = "select distinct(sirm.attribute) as attribute from store_item_received_material as sirm where sirm.store_item_id in (select si.id from store_item as si where si.customer_id = ? and si.program_id = ? and si.date_processed is not null)", resultSetMapping = "select.distinct.sirm.for.customer.program" }) 

@SqlResultSetMapping(name = "select.distinct.sirm.for.customer.program",
    columns = @ColumnResult(name = "attribute"))

how to create a hibernate distinct query

1 Answers1