[
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 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
