Hibernate JPA按子类属性筛选

Question

我有以下JPA实体，

@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "type", discriminatorType = DiscriminatorType.STRING)
public abstract class OrderItem {
// some inherited fields
}

@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorValue("TYPE1")
public class OrderItemTypeOne extends OrderItem {
@OneToOne(mappedBy = "orderItem")
@JsonManagedReference
private SomeTypeA a;
}

@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorValue("TYPE2")
public class OrderItemTypeTwo extends OrderItem {
@OneToOne(mappedBy = "orderItem")
@JsonManagedReference
private SomeTypeB b;
}

我想执行一个查询，在一个JPA查询中为我提供所有OrderItems，其中a='a'(对于OrderItemTypeOne(和b='b'(对于OrderItemTypeTwo(。我怎样才能做到这一点？到目前为止，我有这个查询，但它在运行时会给出一个SQLGrammarException。

@Query("SELECT oi FROM OrderItem oi "
+ "LEFT JOIN OrderItemTypeOne t1 "
+ "LEFT JOIN OrderItemTypeTwo t2 "
+ "WHERE t1.a = :a "
+ "AND t2.b = :b")
Page<OrderItem> findOrders(@Param("a") String a, @Param("b") String b);

我试过使用这种方法，

@Query("SELECT oi FROM OrderItem oi "
+ "WHERE treat(oi as OrderItemTypeOne).a = :a "
+ "AND treat(oi as OrderItemTypeTwo).b = :b")
Page<OrderItem> findOrders(@Param("a") String a, @Param("b") String b);

但这不起作用，因为treat((只在我给定子属性(例如treat(oi.someOtherProperty as SomeOtherType)(时起作用。

任何指导都将不胜感激！

Answer 1

我怀疑您应该在这里使用or-子句而不是and

select oi 
from OrderItem oi 
where treat(oi as OrderItemTypeOne).a=:a or treat(oi as OrderItemTypeTwo).b=:b

当您使用and-子句时，您将一无所获，因为OrderItem不能同时是OrderItemTypeOne和OrderItemTypeTwo的实例。

另一种方法是以这种方式使用交叉连接

select oi 
from OrderItem oi, OrderItemTypeOne t1, OrderItemTypeTwo t2
where (oi.id=t1.id or oi.id=t2.id) and (t1.a=:a or t2.b=:b)

其中oi.id-OrderItemid字段名称

Answer 2

使用以下内容：

@Query("SELECT oi FROM OrderItem oi "
+ "WHERE oi.a = :a "
+ "OR oi.b = :b")
Page<OrderItem> findOrders(@Param("a") String a, @Param("b") String b);