Partner – JPA Buddy – NPI (tag=JPA/Hibernate)
announcement - icon

The right tools can and will save a lot of time. As long as you are using Hibernate and IntelliJ IDEA you can boost your coding speed and quality with JPA Buddy. It will help in a lot of the day-to-day work:

  • Creating JPA entities that follow best practices for efficient mapping
  • Creating DTOs from entities and MapStruct mappers using convenient visual tools
  • Generating entities from the existing database or Swagger-generated POJOs
  • Visually composing methods for Spring Data JPA repositories
  • Generating differential SQL to update your schema in accordance with your changes in entities
  • Autogenerating Flyway migrations and Liquibase changelogs comparing entities with the database or two databases
  • … and a lot more

Simply put, you'll learn and use the best practices of Hibernate and surrounding technology and become a lot more!

Definitely visit the JPA Buddy site to see its features in action closer.

Persistence top

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

>> CHECK OUT THE COURSE

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.AUTO)
    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.AUTO)
    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.AUTO)
    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.

As always, the full source code of the article is available over on GitHub.

Persistence bottom

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

>> CHECK OUT THE COURSE
Persistence footer banner
Comments are closed on this article!