[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Number of problems with query hint extensions
|
Hello,
guess what I fall over while trying to understand the JPA 2 Spec, Chapter 6.5.4:
"Multiple levels of fetch joins are not required to be supported by an implementation of this
specification. Applications that use multi-level fetch joins will not be portable."
I don't understand at all, why this restriction is made in the specification.
Does anyone know this?
Does EclipseLink implement the "multi-level fetch joins"?
We absolutely need "multi-level fetch joins". So if EL does not support them, there's no sense
in migrating to JPA 2.0.
Kind Regards, Michael
Tom Ware schrieb:
> Hi Michael,
>
> I just realized there is another JPA-based way you can do this. I
> believe the JPA 2.0 criteria API allows nested fetch joins in a way that
> could allow this to work as well. (EclipseLink 2.0 or better) I don't
> have a good documentation link aside from the specification itself, but
> here's a chunk of code from our tests that does a fetch.
>
> CriteriaBuilder qb = em.getCriteriaBuilder();
> CriteriaQuery<Employee> cq = qb.createQuery(Employee.class);
> Root<Employee> root = cq.from(Employee.class);
> root.fetch("phoneNumbers", JoinType.LEFT);
> List result = em.createQuery(cq).getResultList();
>
> I think that playing around with the fetch API should allow you to get
> the query you want.
>
> For native code, here is a sample using a slightly different Object
> model. Customer has a 1-M to Order and Order has a 1-1 to salesPerson.
>
> Start with this:
>
> EntityManager em = createEntityManager();
> Query query = em.createQuery("select c from Customer c");
>
> The following replicates your issue:
>
> query.setHint(QueryHints.FETCH, "c.orders");
> query.setHint(QueryHints.FETCH, "o.orders.salesPerson");
> List results = query.getResultList();
>
> The follow native API avoids it:
>
> // get the underlying Eclipse query
> ReadAllQuery raq =
> (ReadAllQuery)((JpaQuery)query).getDatabaseQuery();
>
> // get the EclipseLink ExpressionBuilder for the query
> ExpressionBuilder customerBuilder = raq.getExpressionBuilder();
>
> // build an expression to get the orders
> // orders is 1-m so use anyOf() for join or anyOfAllowingNull() for
> outer join
> Expression ordersExp = customerBuilder.anyOf("orders");
>
> // build an expression for salesPerson using the already-created
> // ordersExp to indicate to use the same join
> // use get() because this is a 1-1 or getAllowingNull() for outer join
> Expression salesPersonExp = ordersExp.get("salesPerson");
>
> // fetch join these two attributes
> raq.addJoinedAttribute(ordersExp);
> raq.addJoinedAttribute(salesPersonExp);
>
> // back to standard JPA
> List results = query.getResultList();
>
> There is some documentation here:
>
> http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#How_to_Use_EclipseLink_Query_API_in_JPA_Queries
>
>
> -Tom
>
> Michael Simons wrote:
>> Hello Tom,
>>
>> I've tried the possibility mentioned by Andrei, but that seems to slow
>> because of the
>> unnecessary outer-joins. So it only works for QueryHints.FETCH.
>> So the Native EclipseLink API definitely is an option for us. Can You
>> please tell where to get
>> information about it.
>>
>> Kind Regards, Michael
>>
>>
>> Tom Ware schrieb:
>>> Hi Michael,
>>>
>>> The problem here is that each of your hints is being treated as
>>> individual.
>>>
>>> i.e.
>>> x.locatedVehicles causes one fetch and x.locatedVehicles.tours causes a
>>> completely independent fetch and so on. There is probably something we
>>> could do here to enhance the framework to allow expressions like
>>> x.locatedVehicles and x.locatedVehicles.tours to refer to the same
>>> thing. (maybe some kind of aliasing strategy like JPQL uses for its
>>> non-fetch joins) Please feel free to enter an enhancement.
>>>
>>> To workaround, the only thing I can think of is to use Native
>>> EclipseLink API. Let me know if that is an option for you and I can
>>> help out.
>>>
>>> As for batching, that works a little differently, you should not see
>>> any SQL related to the batch on your original query, but when you
>>> traverse the relationship that is batched, you will get all the objects
>>> on the other side of the relationship as a batch.
>>>
>>> -Tom
>>>
>>> Michael Simons wrote:
>>>> 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, "x.locatedVehicles.tours");
>>>> query.setHint (QueryHints.FETCH, "x.locatedVehicles.tours.positions");
>>>>
>>>> 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:
>>>>> Hello,
>>>>>
>>>>> 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,
>>>>> "x.locatedVehicles.tours");
>>>>>
>>>>> 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, "x.locatedVehicles.tours");
>>>>>
>>>>> 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@xxxxxxxxxxx
>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>>>
>>>>
>>>> _______________________________________________
>>>> eclipselink-users mailing list
>>>> eclipselink-users@xxxxxxxxxxx
>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@xxxxxxxxxxx
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>>
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
