Using Hibernate 3.1.2 & MSSQL 2000 I've got a simple one-to-many relationship mapped between an Employee object and a LeaveRequest object (one object per-table) and it's generating some SQL I would have not expected to see...since this is very typical relationship.
SQL output (fields abbreviated w/ '*' for readability):
I would have expected to see a single "select x,y,z from requests where employee_id = ?" type of query...not a whole slew of them testing against a sub-query. Everything *works* but obviously, I'd like to see less queries being generated.
I would believe it's the n+1 selects problem but this is test data and only one of the employee records as leave request records...and that employee only has 3 records...so I'm probably just not seeing a goof in my mapping files somewhere.
I've tried tweaking it by removing the inverse relationship...but the problem returns. I can't use a join w/o making the employee show up 3 times in the master list...so I'd rather use select.
I'm using the official MSSQL 2000 JDBC driver...and this type of mapping is working elsewhere in my app w/o this type of strangeness.
What you are seeing is a side effect of lazy loading. When you declare an association in a mapping and define it as lazy="true" when you load that object from the DB Hibernate will hold a collection of IDs to the associated object. If you access a property on your associated object Hibernate will load that object by id, one at a time. Hence all the selects.
Remember that Hibernate only goes to the database if an object is not already in the Session. So if you know in advance that you are going to access an Employee and all its LeaveRequests use HQL and an explicit fetch or inner join. Alternatively you could configure batch fetching in the mapping, in which case Hibernate will return the amount of LeaveRequests defined wether or not any property of a LeaveRequest has been accessed. if you are considering this though, you probably don't want to define your association as lazy.
The big issue though if is this affecting the performance of your application adversely? Any ORM is a trade-off and this is one of Hibernate's. I know the extra selects look bad (that was my first reaction too) but only you'll be able to see if they actually are.