JPQL equivalent of SQL query using unions and sele

Question

I've written a SQL query that basically selects from a number of tables to determine which ones have rows that were created since a particular date. My SQL looks something 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

That works well in SQL but I recently found that, while JPA may use unions to perform "table per class" polymorphic queries, JPQL does not support unions in queries. So that leaves me wondering whether JPA has an alternative I could use to accomplish the same thing.

In reality, I would be querying against a dozen tables, not just two, so I would like to avoid doing separate queries. I would also like to avoid doing a native SQL query for portability reasons.

In the question I linked to above, it was asked whether the entities that map to widget_a and widget_b are part of the same inheritance tree. Yes, they are. However, if I selected from their base class, I don't believe I would have a way of specifying different string constants for the different child entities, would I? If I could select an entity's class name instead of a string I provide, that might serve my purpose too. But I don't know if that's possible either. Thoughts?

Answer 1

I did a little more searching and found a (seemingly obscure) feature of JPA that serves my purpose perfectly. What I found is that JPA 2 has a type keyword that allows you to limit polymorphic queries to a particular subclass, like so:

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

I've found that JPA (or at least Hibernate as a JPA implementation) allows you to use type not only in constraints but also in select lists. This is approximately what my query ended up looking like:

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

That query returns a list of Class objects. My original query was selecting string literals because that's closest to what I might have done in SQL. Selecting Class is actually preferable in my case. But if I did prefer to select a constant based on an entity's type, that is the exact scenario that Oracle's documentation uses to illustrate case statements:

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

Answer 2

Some JPA providers do support UNION,

http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#UNION

but your query seems very complex, and non object-oriented, so using a native SQL query would probably be best.