 Java, Spring and Web Development tutorials  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.
As always, the source code is available over on GitHub. The post Implementing Unions in Hibernate first appeared on Baeldung.
Content mobilized by FeedBlitz RSS Services, the premium FeedBurner alternative. | 
