[
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,
This issue looks very similar to:
https://bugs.eclipse.org/bugs/show_bug.cgi?id=301599
Do you agree? If so, I suggest you simply add some comments about your
scenario to that bug and, if you want, vote for that bug.
-Tom
Michael Simons wrote:
Hello Tom,
playing around with JPA 2.0, I faced the first issue.
The following CriteriaQuery is mapped to the SQL statement below.
Model:
Scenario (1:N) Depot (1:N) Vehicle (1:N) Tour (1:N) TourPosition (<|LoadStop, <|OrderStop)
JPQ:
CriteriaQuery<Depot> cq = cfab.createQuery (Depot.class);
cq.distinct (false);
Root<Depot> depot = cq.from (Depot.class);
depot.fetch ("locatedVehicles", JoinType.LEFT).fetch ("tours", JoinType.LEFT).fetch
("positions", JoinType.INNER);
cq.where (crit.equal (depot.get("scenario").get ("id"), 1)); // ! //
SQL:
SELECT t1.depot_id, ...
FROM depot t1
LEFT OUTER JOIN vehicle t0 ON (t0.depot_id = t1.depot_id)
LEFT OUTER JOIN tour t2 ON (t2.vehicle_id = t0.vehicle_id),tour_position t3
LEFT OUTER JOIN load_stop t4 ON (t4.tour_position_id = t3.tour_position_id)
LEFT OUTER JOIN order_stop t5 ON (t5.tour_position_id = t3.tour_position_id),
scenario t6
WHERE ((t6.scenario_id = ?)
AND ((t6.scenario_id = t1.scenario_id)
AND (t3.tour_id = t2.tour_id)));
The join of the scenario is redundant, a simple "where t1.scenario_id = ?") is enough.
Specifying the id of the scenario directly
cq.where (crit.equal (depot.get("scenario_id"), 1));
is not possible, as an IllegalArgumentException is thrown:
"The attribute [scenario_id] from the managed type [EntityTypeImpl@12112029:Depot [ javaType:
class net.uniopt.domain.ot.Depot descriptor: RelationalDescriptor(net.uniopt.domain.ot.Depot -->
[DatabaseTable(depot)]), mappings: 20]] is not present."
Should I open an issue on this?
Kind Regards, Michael
Tom Ware schrieb:
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
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
