While Spring JPA simplifies things for the developer, performance is often unnecessarily sacrificed in the process. For example, say we need to fetch 3 different entities from our database. We know how to do this with a repository for each entity class and make 3 separate calls to get our 3 entities. Unfortunately, this results in 3 separate queries being sent to the database. Our aim should be to write a single query which will fetch all 3 at once.
This is possible using JPQL.
Say we have 3 entities classes: Car
, Driver
, Location
, each containing a field id
and name
. We can choose one of our repository classes, say CarRepository
, and write a method like this:
@Query("Select" +
"(SELECT c FROM Car c WHERE c.name = :carName), " +
"(SELECT d FROM Driver d WHERE d.name = :driverName), " +
"(SELECT l FROM location l WHERE l.name = :locationName) " +
"(FROM Car")
List<Object[]> getCarAndDriverAndLocation(
@Param("carName") String carName,
@Param("driverName") String driverName,
@Param("locationName") String locationName);
Now all we have to do is call this getCarAndDriverAndLocation
method and we’ll receive a list of these entities if found. What is nice about putting all the search conditions in the subselect is that if there no matches for a specific entity, then it will return null
for that entity but still give us the other results. If we don’t use the subselect, then no rows will be returned if one of the entities don’t exist.
An interesting thing to note is the FROM
clause. In standard SQL this wouldn’t be needed, but JPQL requires a FROM
clause. Therefore we need to pass some table to it. The choice is completely arbitrary and can be any existing table.