Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Number of problems with query hint extensions

Instead of the three query hints specify a single query hint:
query.setHint (QueryHints.FETCH, "");

----- Original Message ----- From: "Michael Simons" <michael.simons@xxxxxxxxxxx>
To: "EclipseLink User Discussions" <eclipselink-users@xxxxxxxxxxx>
Sent: Friday, February 26, 2010 11:03 AM
Subject: Re: [eclipselink-users] Number of problems with query hint extensions

Hello once more,

when I expand the query to also get the stops within a tour like this:
query = em.createQuery("SELECT DISTINCT x FROM Depot x WHERE x.scenario = :currentScenario");
query.setHint (QueryHints.FETCH, "x.locatedVehicles");
query.setHint (QueryHints.FETCH, "");
query.setHint (QueryHints.FETCH, "");

things get even worse, as you can see by the SQL statement below. As you might guess that
statement runs almost for ever.
That's because the statement is much too complicated, e.g. the vehicle is joined three times!
But that's not necessary at all, it's bloated.
Why is this done?

SELECT DISTINCT t1.depot_id, t1.bot_pump_rate, t1.available, ...
FROM tour_position t6
LEFT OUTER JOIN order_stop t7 ON (t7.tour_position_id = t6.tour_position_id) LEFT OUTER JOIN load_stop t8 ON (t8.tour_position_id = t6.tour_position_id), tour t5, vehicle
t4, tour t3, vehicle t2, depot t1, vehicle t0
WHERE ((t1.scenario_id = ?)
AND ((((((t0.depot_id = t1.depot_id) AND (t2.depot_id = t1.depot_id))
AND (t3.vehicle_id = t2.vehicle_id)) AND (t4.depot_id = t1.depot_id))
AND (t5.vehicle_id = t4.vehicle_id)) AND (t6.tour_id = t5.tour_id)))

kind Regards, Michael

Michael Simons schrieb:

Given the follwing JPQL Query:
"SELECT DISTINCT d FROM Depot d WHERE d.scenario = :currentScenario"

with the following classes:
@Entity @Table(name="depot") class Depot {
  List<Vehicle> locatedVehicles;

@Entity @Table(name="vehicle") class Vehicle {
  List<Tour> tours;

@Entity @Table(name="tour") class Tour {

We want EL to query not only the depots within a given scenario but also all vehicles located at
these depots, and all tours driven by theses vehicles (trucks).
So, we set the following hints:
query.setHint (QueryHints.LEFT_FETCH, "x.locatedVehicles");
query.setHint (QueryHints.LEFT_FETCH, "");

The following SQL-statement is created by EL:
SELECT DISTINCT t1.depot_id, t1..., t0.vehicle_id, t0..., t2.vehicle_id, t2..., t3.tour_id, t3...
FROM depot t1
LEFT OUTER JOIN vehicle t0 ON (t0.depot_id = t1.depot_id)
LEFT OUTER JOIN vehicle t2 ON (t2.depot_id = t1.depot_id)
LEFT OUTER JOIN tour t3 ON (t3.vehicle_id = t2.vehicle_id)
WHERE (t1.scenario_id = ?)

Why is vehicle joined twice?

On the other hand, when specifying
query.setHint (QueryHints.BATCH, "x.locatedVehicles");
query.setHint (QueryHints.BATCH, "");

neither vehciles nor tours are selected at all.

Is there anything wrong with these hints?
Is there a detailed documentation on how to use QueryHints.BATCH / .FETCH / .LEFT_FETCH ?

Kind Regards, Michael
eclipselink-users mailing list

eclipselink-users mailing list

Back to the top