First of all, this is the first time that I use Hibernate so my questions might seem naive.

I have two tables, DsJobs and DsEvents. Events has a foreign key relationship on the DsJobs table (EID column in DsJobs table corresponds to the ID column of DsEvents table).

I have creted the mapping with anotations, what I care about is unilateral relation, so I have a 'dsEvents' property in DsJobs table annotated with @ManyToOne:



I want to retrieve the list of Jobs records from the databse and restricte them with setFirstResult and setMaxResults. This is the call i make:




This code returns an ArrayList of DsJobs object and inside every DsJobs object there is a DsEvents object, correctly instantiated.

My issues are two:

1) When I see the HQL executed there are unnecesery SELECT calls to the database, one for every DsJobs record. So for each DsJobs record, there is a SELECT in the DsEvents to get the coresponding event, by event id. This is unacceptable for performance, since with simple JDBC I can get all the data I need with a inner join SELECT stament. I am probably doing something completely wrong, please let me know what and if you know how I should proceed.

2) The setFirstResult and setMaxResults seem to be completeley ignored by Hibernate, I always get all records from the database. How can I fix that?

I will greatly appreciate any help.

Thank you in advance.

What database dialect are you using?

Note that setFirstResults/setMaxResults only works on the primary SQL statement. Your secondary SQL statements are because you told Hibernate to eagerly fetch the results. You didn't tell it to use a join, and your setup may dictate a secondary select for eager fetching by default.

Note that you don't actually want to use an INNER JOIN, because that will completely omit any DsJobs rows that have EID set to null (you didn't indicate nullable=false in your mapping).

http://community.jboss.org/wiki/AShortP ... Strategies

Alternatively you can do a join fetch:

Thank you very much for your answer, this is exactly what I used in my case, namely "from DsJobs log left join fetch log.dsEvents" and it worked. It kind of seems strange to me that this is not something very easy to find in the documentation (I actually found it an hour or so after posting here).