How to Return Multiple Entities in JPA Query

1. Overview

In this short tutorial, we’ll see how to return multiple different entities in JPA Query. 

First, we’ll create a simple code example containing a few different entities. Then, we’ll explain how to create a JPA Query that returns multiple different entities. Finally, we’ll show a working example in Hibernate’s JPA implementation.

2. Example Configuration

Before we explain how to return multiple entities in a single Query, let’s build an example that we’ll work on.

We’ll create an app that allows its users to buy subscriptions for specific TV channels. It consists of 3 tables: Channel, Subscription, and User.

First, let’s look at the Channel entity:

@Entity
public class Channel {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String code;

    private Long subscriptionId;

   // getters, setters, etc.
}

It consists of 3 fields that are mapped to corresponding columns. The first and the most important one is the id, which is also the primary key. In the code field, we’ll store Channel‘s code.

Last but not least, there is also a subscriptionId column. It’ll be used to create a relation between a channel and a subscription it belongs to. One channel can belong to different subscriptions.

Now, let’s see the Subscription entity:

@Entity
public class Subscription {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String code;

   // getters, setters, etc.
}

It’s even simpler than the first one. It consists of the id field, which is the primary key, and the subscription’s code field.

Let’s also look at the User entity:

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String email;

    private Long subscriptionId;

    // getters, setters, etc.
}

Besides the primary key id field, it consists of email and subscriptionId fields. The latter is used to create a relation between a user and a subscription that they have chosen.

3. Returning Multiple Entities in Query

3.1. Creating the Query

In order to create a query returning multiple different entities, we need to do 2 things.

Firstly, we need to list entities that we want to return in the SELECT part of the SQL Query, separated by a comma.

Secondly,  we need to connect them with each other by their primary and corresponding foreign keys.

Let’s look at our example. Imagine that we want to fetch all Channels assigned to Subscriptions that were bought by the user with the given email. The JPA Query does look like this:

SELECT c, s, u
  FROM Channel c, Subscription s, User u
  WHERE c.subscriptionId = s.id AND s.id = u.subscriptionId AND u.email=:email

3.2. Extracting Results

A JPA Query that selects multiple different entities returns them in an array of Objects. What’s worth pointing out is that the array keeps the order of entities. It’s crucial information because we need to manually cast returned Objects to specific entity classes.

Let’s see that in action. We created a dedicated repository class that creates a query and fetches results:

public class ReportRepository {
    private final EntityManagerFactory emf;

    public ReportRepository() {
        // create an instance of entity manager factory
    }

    public List<Object[]> find(String email) {
        EntityManager entityManager = emf.createEntityManager();
        Query query = entityManager
          .createQuery("SELECT c, s, u FROM  Channel c, Subscription s, User u" 
          + " WHERE c.subscriptionId = s.id AND s.id = u.subscriptionId AND u.email=:email");
        query.setParameter("email", eamil);

        return query.getResultList();
    }
}

We’re using an exact query from the previous section. Then, we set an email parameter to narrow down the results. Finally, we fetch the result list.

Let’s see how we can extract individual entities from the fetched list:

List<Object[]> reportDetails = reportRepository.find("[email protected]");

for (Object[] reportDetail : reportDetails) {
    Channel channel = (Channel) reportDetail[0];
    Subscription subscription = (Subscription) reportDetail[1];
    User user = (User) reportDetail[2];
    
    // do something with entities
}

We iterate over the fetched list and extract entities from the given object array. Having in mind our JPA Query and the order of entities in its SELECT section, we get a Channel entity as a first element, a Subscription entity as a second, and a User entity as the last element of the array.

4. Conclusion

In this article, w discussed how to return multiple entities in the JPA query. Firstly, we created an example that we worked on later in the article. Then, we explained how to write a JPA query to return multiple different entities. Finally, we showed how to extract them from the result list.