2

I am experimenting the joined table inheritance in JPA (EclipseLink 2.6.0 having JPA 2.1).

If I execute the following JPQL statement on the root entity Vehicle,

List<Vehicle> vehicleList = entityManager.createQuery("SELECT v FROM Vehicle v", Vehicle.class).getResultList();

The EclipseLink provider generates the following SQL statements.

SELECT DISTINCT vehicle_type FROM vehicle
SELECT t0.vehicle_id, t0.vehicle_type, t0.manufacturer, t1.vehicle_id, t1.no_of_passengers, t1.saddle_height FROM vehicle t0, BIKE t1 WHERE ((t1.vehicle_id = t0.vehicle_id) AND (t0.vehicle_type = ?))
    bind => [Bike]
SELECT t0.vehicle_id, t0.vehicle_type, t0.manufacturer, t1.vehicle_id, t1.no_of_doors, t1.no_of_passengers FROM vehicle t0, CAR t1 WHERE ((t1.vehicle_id = t0.vehicle_id) AND (t0.vehicle_type = ?))
    bind => [Car]
SELECT t0.vehicle_id, t0.vehicle_type, t0.manufacturer, t1.vehicle_id, t1.load_capacity, t1.no_of_containers FROM vehicle t0, TRUCK t1 WHERE ((t1.vehicle_id = t0.vehicle_id) AND (t0.vehicle_type = ?))
    bind => [Truck]

If an ORDER BY clause is added to the given JPQL statement like the following,

List<Vehicle> vehicleList = entityManager.createQuery("SELECT v FROM Vehicle v ORDER BY v.vehicleId DESC", Vehicle.class).getResultList();

the EclipseLink provider however, generates the following single SQL statement with outer joins.

SELECT t0.vehicle_id, 
       t0.vehicle_type, 
       t0.manufacturer, 
       t1.vehicle_id, 
       t1.load_capacity, 
       t1.no_of_containers, 
       t2.vehicle_id, 
       t2.no_of_passengers, 
       t2.saddle_height, 
       t3.vehicle_id, 
       t3.no_of_doors, 
       t3.no_of_passengers 
FROM   vehicle t0 
       LEFT OUTER JOIN truck t1 
                    ON ( t1.vehicle_id = t0.vehicle_id ) 
       LEFT OUTER JOIN bike t2 
                    ON ( t2.vehicle_id = t0.vehicle_id ) 
       LEFT OUTER JOIN car t3 
                    ON ( t3.vehicle_id = t0.vehicle_id ) 
ORDER  BY t0.vehicle_id DESC 

This is mentioned in the JPA Wiki Book.

The poorest performing queries will be those to the root or branch classes. Avoiding queries and relationships to the root and branch classes will help to alleviate this burden. If you must query the root or branch classes there are two methods that JPA providers use, one is to outer join all of the subclass tables, the second is to first query the root table, then query only the required subclass table directly. The first method has the advantage of only requiring one query, the second has the advantage of avoiding outer joins which typically have poor performance in databases.

When a particular JPA provider determines which method to apply to generate/produce SQL statement(s), when a root entity is queried -- why does the ORDER BY clause in this particular case make a difference?

I have not yet given it a try on other JPA providers.

I presumed the entity classes are unrelated but let me know, if you need the inheritance hierarchy of classes used in these examples.

Tiny
  • 27,221
  • 105
  • 339
  • 599
  • 1
    Makes sense. Applying ordering across all entities at the database level will require one unified resultset hence the outer joins. Without this you could only have ordering applied to each individual resultset at the database level and would need additional in-memory sorting to order the aggregated collection. – Alan Hay May 22 '15 at 12:39

0 Answers0