Replicate union all with table per class strategy

Hibernate ORM
1

I’m working with Hibernate and have a set of polymorphic entities using the InheritanceType.TABLE_PER_CLASS strategy. I have an abstract BaseEvent class and several concrete classes like AreaEntryEvent, SignalChangeEvent, etc., each represented by its own table.

java 
@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class BaseEvent {
    @Id
    private String id;
    private Instant eventDate;
    // ... other common fields

    @Enumerated(EnumType.STRING)
    private EventType eventType;
    // ...
}

@Entity
@Table(name = "events_area_entry")
public class AreaEntryEvent extends BaseEvent {
    private String areaId;
    @Enumerated(EnumType.STRING)
    private AreaType areaType;
    // ... other fields specific to AreaEntryEvent
}

@Entity
@Table(name = "events_signal_change")
public class SignalChangeEvent extends BaseEvent {
    @Embedded
    private SignalChangeDetails signalDetails;
    // ... other fields specific to SignalChangeEvent

    public static class SignalChangeDetails {
        private String oldSignal;
        private String newSignal;
    }
}

enum EventType { AREA_ENTRY, SIGNAL_CHANGE, /* ... other types */ }
enum AreaType { POLYGON, RECTANGLE, /* ... other types */ }

I need to execute a query that retrieves all BaseEvent instances, combining data from all or a subset of the individual event tables. I have two key requirements:

  1. Type-Specific Filters: I need to apply filters specific to certain event types. For example, I might want to filter by areaId only when querying AreaEntryEvent instances. These filters should not affect other event types.
  2. Subset of Tables: I need the flexibility to select only a subset of the event tables for the query. Sometimes I might want to query only AreaEntryEvent and SignalChangeEvent, and other times a different combination. Filtering by eventType or class after the query is not sufficient; I need to control the tables accessed during the query.

I’m trying to avoid manual deserialization or handling tuples, especially as I have 23 subclasses of BaseEvent. I’d like Hibernate to handle the mapping back to the correct entity types automatically.

I’ve tried Criteria API and HQL, but I’m struggling to combine data from different tables while correctly applying type-specific filters. I’m also unsure how to dynamically select the tables to query. I’m aiming for something conceptually similar to the SQL generated by Hibernate’s table-per-class strategy (using UNION ALL), but I want to achieve it using Criteria API or HQL to avoid native SQL and let Hibernate handle the object mapping.

SELECT a1_0.id, a1_0.clazz_, a1_0.area_id, a1_0.old_signal, /* ... all other fields */
FROM (
    SELECT id, 2 as clazz_, ..., area_id, NULL as old_signal, ... FROM events_area_entry WHERE area_id = :areaId
    UNION ALL
    SELECT id, 3 as clazz_, ..., NULL as area_id, old_signal, ... FROM events_signal_change
    -- ... other event tables
) a1_0

How can I achieve this efficiently and correctly in Hibernate while avoiding manual deserialization?

Hibernate Version: 6.6.2.Final
dialect: mysql

2

What HQL or Criteria API queries did you try so far that did not match your expectations? Hibernate ORM goes to great lengths, trying to figure out what tables are actually accessed/needed by a query to alter the union query. The key parts that play into this are comparison predicates with the type() function and the usage of the treat() syntax.

A query like from BaseEvent e where type(e) in (AreaEntryEvent, SignalChangeEvent) should only query from the two tables events_area_entry and events_signal_change.
To access state of the subtypes, you can use type(e) <> AreaEntryEvent or ( treat(e as AreaEntryEvent).areaType = AREA_ENTRY )