Confused about distinct/aggregate queries with (N)Hibernate

Question

I'm not sure how to approach queries that don't map 1:1 to my persistent entities - in other words, distinct and aggregate queries. For example, I need to retrieve a distinct list of property values for populating a drop-down list. Should I write a class and a mapping for the "entities" that are returned by this query? Or should I just use the native DB provider and work with native data sets instead?

Answer 1

If I understand correctly, your problem can be solved by scalar queries in HQL. For example:

Query q = session.createQuery("select i.id, i.description, i.initialPrice" +
    "from Item i where i.endDate > current_date()");

Iterator results = q.list().iterator();
while ( results.hasNext() ) {
  Object[] result = (Object[]) results.next();
  Long id = (Long) result[0];
  String description = (String) result[1];
  BigDecimal price = (BigDecimal) result[1];
}

You can also use distinct in such queries.

Here is another example.

Of course the same can also be done using native SQL from within Hibernate.

Answer 2

If the aggregate query is really complex, create a view from the aggregate query and a read-only mapping to the view.