Implementing Unions in Hibernate

1. Introduction

In this tutorial, we’ll explore how to use the union operation in Hibernate to unify results from two related database entities. At Baeldung University, information about part-time lecturers and visiting researchers are stored in separate tables; however, in cases such as internal reporting or search, it’s helpful to query them as a single group.

We’ll learn how to do this using Hibernate 6’s native support for union, as well as alternative ways to manually implement union-like behavior when direct support isn’t available or practical.

2. Scenario and Setup

We’ll use two simple entities representing different types of people. The two entities share a similar structure, differing only in one attribute. These common attributes are essential when using the union statement.

2.1. Creating the Researcher Entity

We’ll start with the Researcher entity:

@Entity
public class Researcher {

    @Id
    private Long id;
    private String name;
    private boolean active;

    // default getters and setters
}

We’ll also need a repository interface:

@Repository
public interface ResearcherRepository extends JpaRepository<Researcher, Long> {}

2.2. Creating the Lecturer Entity

Then, let’s define the Lecturer class, which differs by having a facultyId field:

@Entity
public class Lecturer {

    @Id
    private Long id;
    private String name;
    private Integer facultyId;

    // default getters and setters
}

And now, let’s look at its repository:

@Repository
public interface LecturerRepository extends JpaRepository<Lecturer, Long> {}

2.3. Creating the DTO

We’ll use a DTO to represent a unified person. Also, we’ll include a role property we can use as a discriminator column:

public class PersonDto {

    private Long id;
    private String name;
    private String role;

    // default getters, setters and constructors
}

2.4. Creating the Service Class

We’ll centralize our approaches in a service class to make tests simpler:

@Service
public class UnionService {

    @PersistenceContext
    EntityManager em;

    @Autowired
    LecturerRepository lecturerRepository;

    @Autowired
    ResearcherRepository researcherRepository;

    @Autowired
    UnionService unionService;

    // ...
}

2.5. Creating the Test Data

Finally, we’ll include six rows for our test data, with one duplicate to differentiate union from union all. Let’s start with our test class:

@SpringBootTest
class UnionServiceIntegrationTest {

    @Autowired
    LecturerRepository lecturerRepository;

    @Autowired
    ResearcherRepository researcherRepository;

    @BeforeEach
    void setUp() {
        // ...
    }
}

Now, let’s create the data we’ll use in each test. We’ll include a person with the same ID and name in the lecturer and researcher repositories:

lecturerRepository.saveAll(
  List.of( 
    new Lecturer(1l, "Alice"), new Lecturer(2l, "Bob"), new Lecturer(3l, "Candace") 
  )); 

researcherRepository.saveAll( 
  List.of( 
    new Researcher(3l, "Candace"), new Researcher(4l, "Diana"), new Researcher(5l, "Elena") 
));

3. Using Union in JPQL Queries

Starting with Hibernate 6, we can use the union statement directly with createQuery(). Given an appropriate constructor, we can use a constructor expression inside our query; this automatically maps PersonDto in our results.

Let’s add a method to UnionService to see what it looks like:

public List<PersonDto> fetch() {
    return em.createQuery("""
      select new PersonDto(l.id, l.name) from Lecturer l
      union
      select new PersonDto(r.id, r.name) from Researcher r
      """, PersonDto.class)
      .getResultList();
}

Now we can test this:

@Test
void whenUnionQuery_thenUnifiedResult() {
    List<PersonDto> result = unionService.fetch();

    assertEquals(5, result.size());
}

Since the union statement removes duplicate rows, we get five results. We get six when using union all instead.

4. Simulating the Union Statement in Memory

We can manually merge two query results, simulating the behavior of the union statement. Also, we’ll see how the collector type affects the results.

4.1. Merging Into a List

We start by gathering the results of the two queries using the findAll() method we get from extending the JpaRepository:

public List<PersonDto> fetchManually() {
    List<Lecturer> lecturers = lecturerRepository.findAll();
    List<Researcher> researchers = researcherRepository.findAll();

    // ...
}

Then we use Stream.concat() to merge them, choosing an appropriate value for our role property. Since we’re merging both streams into a List, we’ll get the same result as the union all statement:

return Stream.concat( 
  lecturers.stream().map(l -> new PersonDto(l.getId(), l.getName(), "LECTURER")), 
  researchers.stream().map(r -> new PersonDto(r.getId(), r.getName(), "RESEARCHER"))) 
.toList();

The downsides are clear: slower performance due to making two queries instead of one. Also, it’s harder to paginate. So it’s only suitable for merging small tables.

4.2. Merging Into a Set

To remove duplicates, we can collect the results into a Set instead:

return Stream.concat(
  lecturers.stream().map(l -> new PersonDto(l.getId(), l.getName(), "LECTURER")),
  researchers.stream().map(r -> new PersonDto(r.getId(), r.getName(), "RESEARCHER")))
.collect(Collectors.toSet());

To remove duplicate rows, we must override equals() and hashCode() in PersonDto. We’ll check only the id and name fields, which are the common fields between entities, and leave our discriminator column out:

public int hashCode() {
    return Objects.hash(id, name);
}

public boolean equals(Object obj) {
    if (this == obj)
        return true;
    
    if (obj == null || getClass() != obj.getClass())
        return false;

    PersonDto other = (PersonDto) obj;
    return Objects.equals(id, other.id) && Objects.equals(name, other.name);
}

5. Mapping a View With Union

If our union query is too complex, we can create it in the database and only map it in Java.

5.1. Creating the View

For demonstration, we’ll create a simple view:

CREATE VIEW IF NOT EXISTS person_view AS 
SELECT id, name, 'LECTURER' AS role FROM Lecturer 
UNION 
SELECT id, name, 'RESEARCHER' AS role FROM Researcher

5.2. Calling the View With a Native Query

Then, we’ll create a new method in UnionService that maps the union results directly to PersonDto with createNativeQuery():

public List<PersonDto> fetchView() {
    return em.createNativeQuery(
      "select e.id, e.name, e.role from person_view e", 
      PersonDto.class)
    .getResultList();
}

5.3. Calling the View With a Repository Query

We can also create a projection interface with the @Query annotation. If we reuse PersonDto here, we’ll get a ConverterNotFoundException. So let’s create a new interface:

public interface PersonView {

    Long getId();
    String getName();
    String getRole();
}

Then, we’ll add a query method in LecturerRepository, setting nativeQuery to true and returning the PersonView type we created:

@Query(value = "select e.id, e.name, e.role from person_view e", nativeQuery = true)
List<PersonView> findPersonView();

6. Using CriteriaBuilder

Hibernate’s implementation of CriteriaBuilder also gained support for the union statement.

6.1. Unwrapping the Hibernate Session

Since the union statement isn’t part of the standard JPA API, we’ll need to unwrap our Session to access Hibernate’s implementation:

public List<PersonDto> fetchWithCriteria() {
    var session = em.unwrap(Session.class);
    var builder = session.getCriteriaBuilder();

    // ...
}

6.2. Generating the Sub-Queries

We’ll create each sub-query separately, starting with the one for lecturers. We’ll pass PersonDto to the builder.createQuery() method, and then compose a Root<Lecturer> from that:

CriteriaQuery<PersonDto> lecturerQuery = builder.createQuery(PersonDto.class); 
Root<Lecturer> lecturer = lecturerQuery.from(Lecturer.class);

Finally, we use the construct() method to build a similar PersonDto-based SELECT clause as before. We can use the literal() method to include columns that aren’t in the root type:

lecturerQuery.select(builder.construct(
  PersonDto.class, lecturer.get("id"), lecturer.get("name"), builder.literal("LECTURER")));

Then, we do the same for the researchers:

CriteriaQuery<PersonDto> researcherQuery = builder.createQuery(PersonDto.class); 
Root<Researcher> researcher = researcherQuery.from(Researcher.class); 

researcherQuery.select(builder.construct(
  PersonDto.class, researcher.get("id"), researcher.get("name"), builder.literal("RESEARCHER")));

6.3. Merging the Results

Ultimately, we’ll call unionAll() and return the results:

var unionQuery = builder.unionAll(lecturerQuery, researcherQuery); 
return session.createQuery(unionQuery).getResultList();

This approach is more verbose but especially useful when building dynamic queries since we have absolute control at every step.

7. Conclusion

In this article, we explored several ways to implement union queries in Hibernate, extending the Baeldung University schema as an example. We saw how Hibernate 6+ offers native support for union in both JPQL and the Criteria API, efficiently combining results from different entities.

For cases where native support isn’t available or practical, we’ve discussed alternatives such as merging results in-memory or mapping a database view. Each approach comes with trade-offs in performance, maintainability, and pagination support.