JPQL等效于使用联合和选择常量的SQL查询 [英] JPQL equivalent of SQL query using unions and selecting constants

问题描述

我编写了一个SQL查询，该查询基本上从许多表中进行选择，以确定哪些表具有自特定日期以来创建的行.我的SQL看起来像这样:

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

这在SQL中效果很好，但我最近发现，尽管JPA可以使用联合来执行每类表"多态查询，但

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.

实际上，我将针对十几个表进行查询，而不仅仅是两个表，因此我希望避免执行单独的查询.由于可移植性的原因，我也希望避免执行本机SQL查询.

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.

在我上面链接的问题中，有人问到映射到widget_a和widget_b的实体是否属于同一继承树.对，他们是.但是，如果我从他们的基类中选择，我不相信有办法为不同的子实体指定不同的字符串常量，对吗?如果我可以选择一个实体的类名而不是我提供的字符串，那也可以达到我的目的.但是我也不知道是否有可能.有想法吗?

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?

推荐答案

我做了一些进一步的搜索，发现JPA的(看似晦涩的)功能可以很好地满足我的目的.我发现JPA 2有一个type关键字，它允许您将多态查询限制为特定的子类，例如:

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)

我发现JPA(或至少将Hibernate作为JPA实现)允许您不仅在约束中而且在选择列表中使用type.这大概是我的查询最终看起来像的样子:

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

该查询返回Class对象的列表.我最初的查询是选择字符串文字，因为这与我在SQL中所做的最接近.在我的情况下，选择Class实际上是更可取的.但是，如果我确实希望根据实体的类型选择一个常量，那么这就是 Oracle的文档用于说明case语句:

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

这篇关于JPQL等效于使用联合和选择常量的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！