Spring JPA – Select Multiple Entities With a Single Query

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.

Leave a Reply

Your email address will not be published. Required fields are marked *