Hibernate is a Java ORM tool that allows interaction with relational databases using entity objects. However, it lacks native support for SQL UNION in JPQL/HQL. Let us delve into understanding Hibernate unions and how they can be implemented effectively with consistent results across different approaches.
1. What is Hibernate?
Hibernate is a powerful object-relational mapping (ORM) tool for Java that allows developers to interact with relational databases using object-oriented principles. It abstracts away the boilerplate SQL code and provides a more intuitive way to perform CRUD operations by working with Java classes and annotations. Hibernate supports features like lazy loading, caching, transactions, and automatic schema generation. However, certain SQL operations like UNION are not directly supported in JPQL or the Criteria API, and developers often need to use native queries or alternative patterns to achieve similar results.
2. Database Setup (MySQL)
Before implementing union queries in Hibernate, we need a simple relational schema to demonstrate the concept. This setup uses two employee tables: one for full-time employees and another for part-time employees. Later, we’ll combine their data using different approaches in Hibernate.
2.1 Schema
We create two tables with overlapping fields but different payment models—full_time_employee has a fixed salary, and part_time_employee is paid hourly.
CREATE TABLE full_time_employee (
id BIGINT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
salary DOUBLE NOT NULL
);
CREATE TABLE part_time_employee (
id BIGINT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
hourly_rate DOUBLE NOT NULL
);
2.2 Sample Data
The following inserts create sample employees to demonstrate the merging logic later in the application.
-- Full-time employees INSERT INTO full_time_employee (id, name, salary) VALUES (1, 'Alice Johnson', 90000), (2, 'Bob Smith', 85000); -- Part-time employees INSERT INTO part_time_employee (id, name, hourly_rate) VALUES (3, 'Charlie Young', 40), (4, 'Diana Lee', 35);
2.3 View Definition
To simulate SQL UNION behavior directly at the database level, we define a view that combines both tables into one unified structure using UNION ALL. This view includes additional fields to differentiate employee types and preserve salary/hourly information.
CREATE VIEW employee_union_view AS
SELECT
id,
name,
'FULL_TIME' AS type,
salary,
NULL AS hourly_rate
FROM full_time_employee
UNION ALL
SELECT
id,
name,
'PART_TIME' AS type,
NULL AS salary,
hourly_rate
FROM part_time_employee;
3. Project Setup
This section outlines the dependencies needed to create a basic Hibernate-based Java application that interacts with the MySQL database defined above. Make sure your pom.xml includes the Hibernate core, Jakarta Persistence API, and the MySQL JDBC driver.
<dependencies>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>latest_version</version>
</dependency>
<dependency>
<groupId>jakarta.persistence</groupId>
<artifactId>jakarta.persistence-api</artifactId>
<version>latest_version</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>latest_version</version>
</dependency>
</dependencies>
4. Entity Classes
To represent the database structure in Java, we define three entity classes: one for each table and one for the union view. These classes are annotated to map directly to their respective database tables or views.
4.1 FullTimeEmployee.java
The FullTimeEmployee entity maps to the full_time_employee table and contains fields such as id, name, and salary.
@Entity
@Table(name = "full_time_employee")
public class FullTimeEmployee {
@Id private Long id;
private String name;
private Double salary;
// Getters and Setters
}
4.2 PartTimeEmployee.java
This entity maps to the part_time_employee table and stores part-time employee data, including hourlyRate.
@Entity
@Table(name = "part_time_employee")
public class PartTimeEmployee {
@Id private Long id;
private String name;
private Double hourlyRate;
// Getters and Setters
}
4.3 UnifiedEmployee.java
This read-only entity maps to the employee_union_view view, allowing you to fetch combined data in a structured format. It includes type differentiation and conditional fields for salary or hourly rate.
@Entity
@Table(name = "employee_union_view")
@Immutable
public class UnifiedEmployee {
@Id
private Long id;
private String name;
private String type;
private Double salary;
private Double hourlyRate;
@Override
public String toString() {
return name + " (" + type + ") - " +
(type.equals("FULL_TIME") ? "Salary: " + salary : "Hourly Rate: " + hourlyRate);
}
}
5. DTO Class
The EmployeeDTO class is used to transfer unified employee data across layers. It mirrors the fields from both employee types, including type, name, salary, and hourly rate.
public class EmployeeDTO {
private String name;
private String type;
private Double salary;
private Double hourlyRate;
public EmployeeDTO(String name, String type, Double salary, Double hourlyRate) {
this.name = name;
this.type = type;
this.salary = salary;
this.hourlyRate = hourlyRate;
}
@Override
public String toString() {
return name + " (" + type + ") - " +
(type.equals("FULL_TIME") ? "Salary: " + salary : "Hourly Rate: " + hourlyRate);
}
}
6. Main Class
The Main class showcases three different strategies to simulate SQL UNION using Hibernate. Each approach unifies employee data in a different way—DTO projection, SQL view mapping, and CriteriaBuilder queries.
public class Main {
private static EntityManagerFactory emf;
public static void main(String[] args) {
emf = Persistence.createEntityManagerFactory("default");
EntityManager em = emf.createEntityManager();
try {
useDTOProjection(em);
useSQLView(em);
useCriteriaBuilder(em);
} finally {
em.close();
emf.close();
}
}
private static void useDTOProjection(EntityManager em) {
System.out.println("---- DTO Projection ----");
List<EmployeeDTO> result = new ArrayList<>();
result.addAll(em.createQuery(
"SELECT new com.example.dto.EmployeeDTO(e.name, 'FULL_TIME', e.salary, null) FROM FullTimeEmployee e",
EmployeeDTO.class).getResultList());
result.addAll(em.createQuery(
"SELECT new com.example.dto.EmployeeDTO(e.name, 'PART_TIME', null, e.hourlyRate) FROM PartTimeEmployee e",
EmployeeDTO.class).getResultList());
result.forEach(System.out::println);
}
private static void useSQLView(EntityManager em) {
System.out.println("---- SQL View (employee_union_view) ----");
List<UnifiedEmployee> list = em.createQuery("FROM UnifiedEmployee", UnifiedEmployee.class).getResultList();
list.forEach(System.out::println);
}
private static void useCriteriaBuilder(EntityManager em) {
System.out.println("---- CriteriaBuilder ----");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<EmployeeDTO> query1 = cb.createQuery(EmployeeDTO.class);
Root<FullTimeEmployee> root1 = query1.from(FullTimeEmployee.class);
query1.select(cb.construct(EmployeeDTO.class,
root1.get("name"),
cb.literal("FULL_TIME"),
root1.get("salary"),
cb.nullLiteral(Double.class)));
List<EmployeeDTO> list1 = em.createQuery(query1).getResultList();
CriteriaQuery<EmployeeDTO> query2 = cb.createQuery(EmployeeDTO.class);
Root<PartTimeEmployee> root2 = query2.from(PartTimeEmployee.class);
query2.select(cb.construct(EmployeeDTO.class,
root2.get("name"),
cb.literal("PART_TIME"),
cb.nullLiteral(Double.class),
root2.get("hourlyRate")));
List<EmployeeDTO> list2 = em.createQuery(query2).getResultList();
List<EmployeeDTO> merged = new ArrayList<>();
merged.addAll(list1);
merged.addAll(list2);
merged.forEach(System.out::println);
}
}
7. Configuration
The Hibernate configuration file provides database connection details and maps entity classes used in this application. Ensure that your hibernate.cfg.xml includes all required settings and class mappings.
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test_db</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">password</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQL8Dialect</property>
<property name="hibernate.hbm2ddl.auto">none</property>
<property name="hibernate.show_sql">true</property>
<mapping class="com.example.entity.FullTimeEmployee"/>
<mapping class="com.example.entity.PartTimeEmployee"/>
<mapping class="com.example.entity.UnifiedEmployee"/>
</session-factory>
</hibernate-configuration>
8. Code Output
Once the Main class is executed, the console output shows that all three approaches successfully unify and display employee data while preserving type, salary, and hourly rate information.
---- DTO Projection ---- Alice (FULL_TIME) - Salary: 90000.0 Bob (FULL_TIME) - Salary: 85000.0 Charlie (PART_TIME) - Hourly Rate: 40.0 Diana (PART_TIME) - Hourly Rate: 35.0 ---- SQL View (employee_union_view) ---- Alice (FULL_TIME) - Salary: 90000.0 Bob (FULL_TIME) - Salary: 85000.0 Charlie (PART_TIME) - Hourly Rate: 40.0 Diana (PART_TIME) - Hourly Rate: 35.0 ---- CriteriaBuilder ---- Alice (FULL_TIME) - Salary: 90000.0 Bob (FULL_TIME) - Salary: 85000.0 Charlie (PART_TIME) - Hourly Rate: 40.0 Diana (PART_TIME) - Hourly Rate: 35.0
9. Conclusion
Hibernate doesn’t natively support the UNION keyword in JPQL or Criteria queries. However, this article demonstrated three consistent ways to simulate union operations while preserving full employee information, including their type, salary, or hourly rate. By ensuring that each approach—DTO projection, SQL view, and CriteriaBuilder—retrieves the same unified fields, we enable more robust and interchangeable querying strategies within Hibernate’s ORM model.
