How to group selected data?

I have a comments tables from which I want to select and group comments by开发者_JS百科 its foreign key profile_id.

I'm using jpa 2/hibernate and the tables have a one-to-many relationship from profile to comment.

Currently, the sql query retuns a list of comments and through the many-to-one relationship I get the profile for each comment.

I'd appreciate help in answering the following regarding post and pre query execution sorting:

1. How can I sort the returned list of comments, i.e. List<Comment>, by the profile_id (foreign key) of the comments table or the id (primary key) of the profile table?
2. How can/should I construct my sql query to sort the comments by profile?
3. Which of the two - pre or post sort - is a better practice?

Let the database do the sort for you : it's very efficient at doing it.

The HQL for this is very simple :

select c from Comment c order by c.profile.id

"sorting" (post sort) will be done by java in memory while "order by" (pre sort) will be done by the rdbms. Letting the rdbms do it with SQL is more efficient in most cases. The java sort will be controlled by the compareTo method of whatever object you are sorting

The SQL is as simple as adding an order by clause to your HQL like this:

from Comments order by profile_ID

or, using annotations, you can specify in the mapping how to order by:

@org.hibernate.annotations.OrderBy(
     clause = "profile_id asc"
)

to have your results sorted in memory by java, you could map the collection of comments as a SortedMap or SortedSet

@org.hibernate.annotations.Sort(
     type=org.hibernate.annotations.SortType.NATURAL
)
private SortedSet<Comment> comments = new TreeSet<Comment>();

The "NATURAL" in this case would expect Comment class to make sure the comments were sorted by profile_id by using the compareTo method.

you could also use SortType.CoMPARATOR for even more control if your Comments class implemented comparator.

referenced: Java Persistence with Hibernate and http://henko.net/imperfection/sorted-collections-in-hibernate/