hibernate - 使用 IN 运算符运行 JPQL 查询时出现 sql 错误

Question

我有以下 2 个 JPA 类:

@Entity
public class AnalysisPolicy {
    private Set rules;
    //stuff omitted
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    public Set getRules() {
        return rules;
    }
}

@Entity
public class AnalysisRule {
    //stuff omitted
}

和以下 JPQL 查询:

select p from AnalysisPolicy p, AnalysisRule r where r.id=:ruleId and r in p.rules

它接受 java.util.UUID 类型的单个参数 ruleId(这是我用作所有实体的 @Id 的类)。尝试执行查询我得到以下结果:

Hibernate:     /* select        p     from        AnalysisPolicy p,        AnalysisRule r     where        r.id=:ruleId         and r in p.rules */ select            analysispo0_.f_id as f1_12_,            analysispo0_.f_lastmodified as f2_12_,            analysispo0_.global as global12_         from            AnalysisPolicy analysispo0_ cross         join            AnalysisRule analysisru1_ cross         join            AnalysisPolicy_AnalysisRule rules2_, AnalysisRule analysisru3_         where            analysispo0_.f_id=rules2_.AnalysisPolicy_f_id             and rules2_.rules_f_id=analysisru3_.f_id             and analysisru1_.f_id=?             and (                analysisru1_.f_id in (                    .                )            ) limit ?[org.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: 42601[org.hibernate.util.JDBCExceptionReporter] ERROR: syntax error at or near "."

这意味着我的 JPQL 被 hibernate 接受，但由于某种原因它生成的 SQL 无效(“analysisru1_.f_id in (.)”部分)。

我正在为数据库使用 hibernate 3.6 和 postgresql 9。我做错了什么？

Answer 1

查询错误，IN不能那样用。你应该写

select p from AnalysisPolicy p, AnalysisRule r 
    where r.id=:ruleId and r in elements(p.rules)

或

select p from AnalysisPolicy p, AnalysisRule r 
    where r.id=:ruleId and r member of p.rules

但下面的查询可能是最好的查询:

select p from AnalysisPolicy p join p.rules r where r.id=:ruleId