[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Number of problems with query hint extensions
|
Hi Michael,
I noticed you posted a couple of times related to this thread. I'll answer
in this one.
The specification does not require JPA implementers to implement nested fetch
joins in the criteria API. EclipseLink does implement them. That means several
things to you:
1. You should be able to get the join you are looking for from EclipseLink
2. The API you use to do that will be compatible with other JPA providers
3. If you choose another JPA provider, you should check what their level of
support is.
Here is a simple criteria-API-based example. (Customer has 1-M to Order and
Order has 1-1 to SalesPerson
EntityManager em = createEntityManager();
CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Customer> cq = qb.createQuery(Customer.class);
Root<Customer> root = cq.from(Customer.class);
Fetch<Customer, Order> o = root.fetch("orders", JoinType.LEFT);
o.fetch("salesPerson");
List result = em.createQuery(cq).getResultList();
EclipseLink produces this SQL:
SELECT t1.CUST_ID, t1.NAME, t1.CITY, t1.CUST_VERSION, t0.ORDER_ID, t0.QUANTITY,
t0.SHIP_ADDR, t0.ORDER_VERSION, t0.CUSTOMER_CUST_ID, t0.SALESPERSON_ID,
t0.ITEM_ID, t0.BILLEDCUSTOMER_CUST_ID, t2.ID, t2.NAME
FROM CMP3_CUSTOMER t1
LEFT OUTER JOIN CMP3_ORDER t0 ON (t0.CUSTOMER_CUST_ID = t1.CUST_ID),
CMP3_SALESPERSON t2 WHERE (t2.ID = t0.SALESPERSON_ID)
As for comments about existing bugs, if you find legitimate bugs, please feel
free to file them. A test case definitely makes solving the issue easier.
I would also like to encourage you to vote for any bugs you find important.
We are prioritizing bugs with higher numbers of votes.
-Tom
Michael Simons wrote:
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
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
