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.
Employee mapping:
Code:
<hibernate-mapping>
<class name="com.app.Employee" table="ed_employee">
<id name="Id" column="employee_id" type="int" unsaved-value="0">
<generator class="identity"/>
</id>
<property name="DirectoryId" column="directory_id"/>
<property name="FirstName" column="first_name"/>
<property name="MidInitial" column="mid_initial"/>
<property name="LastName" column="last_name"/>
<property name="Extension" column="extension"/>
<property name="Status" column="status"/>
<property name="Comment" column="comment"/>
<bag
name="LeaveRequests"
table="ed_leave_request"
lazy="true"
inverse="true"
fetch="select"
cascade="none">
<key column="employee_id" />
<one-to-many class="com.app.LeaveRequest" />
</bag>
</class>
</hibernate-mapping>
LeaveRequest mapping:
Code:
<hibernate-mapping>
<class name="com.app.LeaveRequest" table="ed_leave_request">
<id name="Id" column="rfl_id" type="int" unsaved-value="0">
<generator class="identity"/>
</id>
<property name="EmployeeId" column="employee_id"/>
<property name="LeaveDate" column="leave_date"/>
<property name="ReturnDate" column="return_date"/>
<property name="DaysTotal" column="days_total"/>
<property name="Reason" column="reason"/>
<property name="ReasonOther" column="reason_other"/>
<property name="RequestDate" column="request_date"/>
<property name="Approved" column="is_approved"/>
<many-to-one
name="Employee"
column="employee_id"
lazy="false"
insert="false"
update="false"
class="com.app.Employee" />
</class>
</hibernate-mapping>
SQL output (fields abbreviated w/ '*' for readability):
Code:
select * from ed_employee this_ order by this_.first_name asc, this_.last_name asc
select * from ed_leave_request leavereque0_ where leavereque0_.employee_id in (?, ?, ?, ?, ?, ?, ?, ?)
select * from ed_leave_request leavereque0_ where leavereque0_.employee_id in (?, ?, ?, ?, ?, ?, ?, ?)
select * from ed_leave_request leavereque0_ where leavereque0_.employee_id in (?, ?, ?, ?, ?, ?, ?, ?)
select * from ed_leave_request leavereque0_ where leavereque0_.employee_id in (?, ?, ?, ?, ?, ?, ?, ?)
select * from ed_leave_request leavereque0_ where leavereque0_.employee_id in (?, ?, ?, ?, ?, ?, ?, ?)
select * from ed_leave_request leavereque0_ where leavereque0_.employee_id in (?, ?, ?, ?, ?, ?)
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.
Any ideas?
Hibernate version: 3.1.2
Name and version of the database you are using: MSSQL 2000
Login
FAQ
Search
