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.