Hi,
I'm very new to Hibernate and I have a question about Criteria and group by.

I have a little test project up with a table that contains some data. Let's say it is possible to have duplicate entries for certain properties (in my case, usernames -- just a test :D). Anyway, I want to select rows such that no duplicate user names appear.

I was able to do this with a standard HQL query using "group by username." But the code I will eventually have to fix uses Criteria. I read through the documentation and saw that Projections could this. However, I found that using the groupProperty() method acts more like a "select distinct username" in that I only get the username back.

How can I/Is there a way to get the entire row?

You just can't do this. I don't understand how your hql query was able to return the entire row with only a group by on a single property. In SQL when using "group by" everything on the select list has to be either an aggregate function or appear in the group by list. If you want to select everything then you must group by everything which means you'll get duplicate usernames. This really is a good thing. Otherwise you'd be leaving it upto the database to decide which of several matching rows to return - i.e. you might get different results each time you ran the query or if you changed databases.

Sounds to me like you need to ask yourself what you're really trying to retrieve.

I guess, in general, I want to be able to use my results as my own objects (let's say, in my case, a User). I noticed that when my query selects specific properties, it returns Objects. Is there a way around this?

Anyway, thanks for your reply. I'll try to find another solution.

The thing is, if you have 2 user records in your database with the same username then you have 2 users with the same username.

I think this is neat... you can select a subset of properties from the objects in you query and populate a bean (not an entity) using hql.

e.g. if you selected say username and age you could do the following:

Well, I guess I should say that the real project I'm going to work on deals with emails. Basically there's a table of users, and some email addresses appear multiple times. In this case, I don't necessarily care what the database returns (as you mentioned in your first response), but only that an email appears once so that it is only sent once to the same address.

It seems that you're suggesting there's no way to get unique query results of this nature, so it looks like I'll have to devise a method to weed out dupes.

Ok, that makes sense. However, it sounds like you're trying to solve a problem with the registration process (i.e. you can register more than once with same email address) at the point of email delivery. I'm guessing you might want additional info such as the person's real name, etc. which clashes with the requirement to send only 1 email per address.

If this is the case you should handle this in java. Group multiple database records by email address so you only send 1 email but include multiple salutations e.g. "Hello JD/John Denver/Jason Drew" indicating to the user that they have registered several times under different aliases. Or, just pick one of the names in the list.