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:
Location, each containing a field
name. We can choose one of our repository classes, say
CarRepository, and write a method like this:
"(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) " +
@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.