JPA – SQL query using Union and select constants equivalent to SQL query

I wrote an SQL query that basically selects from many tables to determine which tables have rows created since a specific date My SQL looks like this:

SELECT widget_type FROM(
  SELECT 'A' as widget_type
  FROM widget_a
  WHERE creation_timestamp > :cutoff
  UNION
  SELECT 'B' as widget_type
  FROM widget_b
  WHERE creation_timestamp > :cutoff
) types
GROUP BY widget_type
HAVING count(*)>0

This works well in SQL, but I recently found that although JPA may use Federation to execute "per type table" polymorphic queries, jpql does not support unions So it makes me wonder if JPA has an alternative to do the same thing

In fact, I will query more than a dozen tables, not just two tables, so I want to avoid querying alone I also want to avoid native SQL queries for portability reasons

In the question I linked above, someone asked about mapping to widgets_ A and widgets_ Whether the entity of B is part of the same inheritance tree Yes, they are However, if I choose from their base class, I don't believe I will specify different string constants for different sub entities, will I? If I can choose the class name of the entity instead of the string I provide, it may also be in line with my purpose But I don't know if it's possible reflection?

Solution

I did some searches and found that a (seemingly vague) function of JPA fully met my purpose I found that JPA 2 has a type keyword that allows you to limit polymorphic queries to specific subclasses, as shown below:

SELECT widget
FROM BaseWidget widget
WHERE TYPE(widget) in (WidgetB,WidgetC)

I found that JPA (or at least hibernate as a JPA Implementation) allows you to use types not only in constraints, but also in selection lists This is about my query result as follows:

SELECT DISTINCT TYPE(widget)
FROM BaseWidget widget
WHERE widget.creationTimestamp > :cutoff

This query returns a list of class objects My original query was to select string text, because it is closest to what I did in SQL In my example, class is actually preferable However, if I prefer to select a constant according to the type of entity, that is the exact scenario used by Oracle's documentation to describe the case statement:

SELECT p.name
CASE TYPE(p)
  WHEN Student THEN 'kid'
  WHEN Guardian THEN 'adult'
  WHEN Staff THEN 'adult'
  ELSE 'unkNown'
END
FROM Person p