I have this hierarchy:

<class name="ie.simtel.jazz.dto.Artist" table="ARTIST">
..
<set name="albums" inverse="true">
<key column="ARTIST_ID" />
<one-to-many class="ie.simtel.jazz.dto.Album" />
</set>
</class>

<class name="ie.simtel.jazz.dto.Album" table="ALBUMS">
..
<many-to-one name="artista" class="ie.simtel.jazz.dto.Artist" column="ARTIST_ID" />
</class>

And I need a list of albums with the associate artist ordered by name of the artist (1) or by title of the album (2):
(1)list = sess.createQuery("from Album as album order by album.artist.name").list();

(2)list = sess.createQuery("from Album as album order by album.title").list();

It works well, but I think Hibernate uses a select more than expected:

select all albums (ok)
for each album:
select the artist (ok)
for each artist:
select albums (why?)

The last group of select seems unnecessary. Hibernate has already
fetched all data of all albums with the first select, why does it query again the db? Is this behaviour by design?

Thank you

Hibernate needs to populate the objects you return with the necessary data. Use a lazy collection to avoid that if its not wanted.

Hibernate has already fetched data (albums) from the db with the first select. So it should not need the last group of selects: it already has that...
If I wrote sql by hand, I would write:

select all albums (same as hibernate)
for each album:
select the artist (same as hibernate)
for each artist:
put the albums of the artist (found with the first select)
into the artist's list of albums.

I know I'm asking a lot from hibernate, I was just wondering if
this is the expected behaviour...

Yes this is expected.

Note that something like you suggest, searching through the previously obtained data for every artist may be easily more time consuming than leting this do the database which has the appropriate indexes and data structures.

Actually my idea was not to search "through the previously obtained data for every artist", but to add the album to the found artist:

select all albums
for each album look for the artist:

if the artist is not already fetched->fetch it

insert into its list of albums the current album.


This way you can avoid the last group of selects...