Hi All,
Just wondering if there is a more efficient way to create many to many mappings than i'm currently using?
For example, I have setup 2 objects, Car and Driver.
Car has Id, LicensePlate and a list of the Driver ids.
Driver has Id, Name and a list of the Car Ids.
I have not linked the objects as i do not want any automatic loading at all.
This is the mapping:
Code:
<class name="TestSecurity.Car, TestSecurity" table="Car">
<id name="Id" type="Int32" unsaved-value="0">
<generator class="native" />
</id>
<property name="LicensePlate" />
<set name="DriverIds" table="CarDriver">
<key column="CarId" />
<element column="DriverId" type="Int32" />
</set>
</class>
<class name="TestSecurity.Driver, TestSecurity" table="Driver">
<id name="Id" type="Int32" unsaved-value="0">
<generator class="native" />
</id>
<property name="Name" />
<set name="CarIds" table="CarDriver">
<key column="DriverId" />
<element column="CarId" type="Int32" />
</set>
</class>
And when i execute
Code:
Find("From Car c Where c.Id IN (1,2,3,4)");
I can see in the sql trace that 5 sql statements have been executed, 1 to retrieve the list of cars, and another for each car to retrieve the drivers.
Code:
select car0_.Id as Id, car0_.LicensePlate as LicenseP2_ from Car car0_ where (car0_.Id IN(1 , 2 , 3 , 4))
exec sp_executesql N'SELECT cardrive0_.CarId as CarId__, cardrive0_.DriverId as DriverId__ FROM CarDriver cardrive0_ WHERE cardrive0_.CarId = @p0', N'@p0 int', @p0 = 4
exec sp_executesql N'SELECT cardrive0_.CarId as CarId__, cardrive0_.DriverId as DriverId__ FROM CarDriver cardrive0_ WHERE cardrive0_.CarId = @p0', N'@p0 int', @p0 = 3
exec sp_executesql N'SELECT cardrive0_.CarId as CarId__, cardrive0_.DriverId as DriverId__ FROM CarDriver cardrive0_ WHERE cardrive0_.CarId = @p0', N'@p0 int', @p0 = 2
exec sp_executesql N'SELECT cardrive0_.CarId as CarId__, cardrive0_.DriverId as DriverId__ FROM CarDriver cardrive0_ WHERE cardrive0_.CarId = @p0', N'@p0 int', @p0 = 1
If i were to do this by hand, i would probably do 2 queries, one to retrieve the cars, another to retrieve all the car drivers, such as
Code:
SELECT Id, LicensePlate FROM Car WHERE (Id IN(1, 2, 3, 4))
SELECT CarId, DriverId FROM CarDriver WHERE (CarId IN (1, 2, 3, 4)
Is there any way to make hibernate do this, ie, get the collection items for the car (in this case the driver list) in a single query for multiple items? Is there a better mapping strategy that would reduce the number of queries needed?
Cheers -Mike
Login
FAQ
Search
