|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=301599Do 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
Back to the top