JPQL 子查询 - 例外 此表达式在此上下文中具有无效的表

Question

我有一个(无辜的，但有效的(T-SQL (子(查询

SELECT * FROM DIAGE.ade.UorPos WHERE Prefixo IN
(SELECT PrefixoJurisdicionada FROM DIAGE.ade.Jurisdicionadas WHERE Prefixo =
(SELECT Prefixo FROM DIAGE.ade.Jurisdicionadas WHERE PrefixoJurisdicionada = 8922)) 
AND CodComissao IN (4345, 4346, 4347)

我已经建立了相应的JPQL

SELECT u FROM UorPos u WHERE u.prefixo IN 
(SELECT j.prefixoJurisdicionada FROM Jurisdicionadas j WHERE j.prefixo = 
(SELECT j.prefixo FROM Jurisdicionadas j WHERE j.prefixoJurisdicionada = :prefixo)) 
AND u.codComissao IN (4345, 4346, 4347)

尽管进行了编译，但在运行时启动异常：

[...]
Caused by: Exception [EclipseLink-6069] (Eclipse Persistence Services - 
2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.QueryException
Exception Description: The field [DIAGE.ade.Prefixos.Prefixo] in this 
expression has an invalid table in this context.
Query: ReadAllQuery(name="UorPos.findUorPosExecutivosByPrefixo" 
referenceClass=UorPos jpql="SELECT u FROM UorPos u WHERE u.prefixo IN 
(SELECT j.prefixoJurisdicionada FROM Jurisdicionadas j WHERE j.prefixo = 
(SELECT j.prefixo FROM Jurisdicionadas j WHERE j.prefixoJurisdicionada = 
:prefixo)) AND u.codComissao IN (4345, 4346, 4347)") at 
org.eclipse.persistence.exceptions.QueryException. invalidTableForFieldInExpression (QueryException.java:749)
at org.eclipse.persistence.internal.expressions.FieldExpression.validateNode(FieldExpression.java:296)
at org.eclipse.persistence.expressions.Expression.normalize(Expression.java:3275)
at org.eclipse.persistence.internal.expressions.DataExpression.normalize(DataExpression.java:369)
at org.eclipse.persistence.internal.expressions.FieldExpression.normalize(FieldExpression.java:223)

我做了一些研究，据我了解，我的 JPQL 查询还可以。

谁能帮我解决这个问题？

我研究过的一些链接：

• 项目 2.5.15

• 如何执行 JPQL 子查询？

• 项目5

• 项目 10.2.5.15

将 IN 与子查询一起使用

我使用的是EclipseLink版本2.5.2(与Netbeans 8.0.2集成(，Glassfish 4.1，Java 1.7.0_71。

更新

UorPos 实体：

@Entity
@Table(name = "UorPos", catalog = "DIAGE", schema = "ade")
@XmlRootElement
@NamedQueries({
@NamedQuery(name="UorPos.findXByY", query="SELECT u FROM UorPos u WHERE u.prefixo IN
(SELECT j.prefixoJurisdicionada FROM Jurisdicionadas j WHERE j.prefixo =
(SELECT j.prefixo FROM Jurisdicionadas j WHERE j.prefixoJurisdicionada = :prefixo))
AND u.codComissao IN (4345, 4346, 4347)")
})
public class UorPos implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 8)
@Column(name = "Matricula")
private String matricula;
@Size(max = 200)
@Column(name = "Nome")
private String nome;
@Size(max = 200)
@Column(name = "NomeGuerra")
private String nomeGuerra;
@Column(name = "CodComissao")
private Integer codComissao;
@Size(max = 25)
@Column(name = "NomeComissao")
private String nomeComissao;
@Size(max = 4)
@Column(name = "CodNivel")
private String codNivel;
@Size(max = 50)
@Column(name = "DescNivel")
private String descNivel;
@Size(max = 50)
@Column(name = "eMailFuncionario")
private String eMailFuncionario;
@Column(name = "DataCaptura")
@Temporal(TemporalType.TIMESTAMP)
private Date dataCaptura;
@Column(name = "DataPermissaoAcesso")
@Temporal(TemporalType.TIMESTAMP)
private Date dataPermissaoAcesso;
@ManyToMany(mappedBy = "uorPosCollection")
private Collection<Demandas> demandasCollection;
@ManyToMany(mappedBy = "uorPosCollection1")
private Collection<Demandas> demandasCollection1;
@ManyToMany(mappedBy = "uorPosCollection2")
private Collection<Demandas> demandasCollection2;
@JoinColumn(name = "UORpos", referencedColumnName = "UORpos")
@ManyToOne(optional = false)
private Divisoes uORpos;
@JoinColumn(name = "idPermissaoAcesso", referencedColumnName = "idPermissaoAcesso")
@ManyToOne
private PermissoesAcesso idPermissaoAcesso;
@JoinColumn(name = "Prefixo", referencedColumnName = "Prefixo")
@ManyToOne
private Prefixos prefixo;
@OneToMany(mappedBy = "matricula")
private Collection<Anotacoes> anotacoesCollection;
@OneToMany(mappedBy = "matricula")
private Collection<Log> logCollection;
public UorPos() {
}
public UorPos(String matricula) {
this.matricula = matricula;
}
public String getMatricula() {
return matricula;
}
public void setMatricula(String matricula) {
this.matricula = matricula;
}
public String getNome() {
return nome;
}
public void setNome(String nome) {
this.nome = nome;
}
public String getNomeGuerra() {
return nomeGuerra;
}
public void setNomeGuerra(String nomeGuerra) {
this.nomeGuerra = nomeGuerra;
}
public Integer getCodComissao() {
return codComissao;
}
public void setCodComissao(Integer codComissao) {
this.codComissao = codComissao;
}
public String getNomeComissao() {
return nomeComissao;
}
public void setNomeComissao(String nomeComissao) {
this.nomeComissao = nomeComissao;
}
public String getCodNivel() {
return codNivel;
}
public void setCodNivel(String codNivel) {
this.codNivel = codNivel;
}
public String getDescNivel() {
return descNivel;
}
public void setDescNivel(String descNivel) {
this.descNivel = descNivel;
}
public String getEMailFuncionario() {
return eMailFuncionario;
}
public void setEMailFuncionario(String eMailFuncionario) {
this.eMailFuncionario = eMailFuncionario;
}
public Date getDataCaptura() {
return dataCaptura;
}
public void setDataCaptura(Date dataCaptura) {
this.dataCaptura = dataCaptura;
}
public Date getDataPermissaoAcesso() {
return dataPermissaoAcesso;
}
public void setDataPermissaoAcesso(Date dataPermissaoAcesso) {
this.dataPermissaoAcesso = dataPermissaoAcesso;
}
@XmlTransient
public Collection<Demandas> getDemandasCollection() {
return demandasCollection;
}
public void setDemandasCollection(Collection<Demandas> demandasCollection) {
this.demandasCollection = demandasCollection;
}
@XmlTransient
public Collection<Demandas> getDemandasCollection1() {
return demandasCollection1;
}
public void setDemandasCollection1(Collection<Demandas> demandasCollection1) {
this.demandasCollection1 = demandasCollection1;
}
@XmlTransient
public Collection<Demandas> getDemandasCollection2() {
return demandasCollection2;
}
public void setDemandasCollection2(Collection<Demandas> demandasCollection2) {
this.demandasCollection2 = demandasCollection2;
}
public Divisoes getUORpos() {
return uORpos;
}
public void setUORpos(Divisoes uORpos) {
this.uORpos = uORpos;
}
public PermissoesAcesso getIdPermissaoAcesso() {
return idPermissaoAcesso;
}
public void setIdPermissaoAcesso(PermissoesAcesso idPermissaoAcesso) {
this.idPermissaoAcesso = idPermissaoAcesso;
}
public Prefixos getPrefixo() {
return prefixo;
}
public void setPrefixo(Prefixos prefixo) {
this.prefixo = prefixo;
}
@XmlTransient
public Collection<Anotacoes> getAnotacoesCollection() {
return anotacoesCollection;
}
public void setAnotacoesCollection(Collection<Anotacoes> anotacoesCollection) {
this.anotacoesCollection = anotacoesCollection;
}
@XmlTransient
public Collection<Log> getLogCollection() {
return logCollection;
}
public void setLogCollection(Collection<Log> logCollection) {
this.logCollection = logCollection;
}
@Override
public int hashCode() {
int hash = 0;
hash += (matricula != null ? matricula.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
if (!(object instanceof UorPos)) {
return false;
}
UorPos other = (UorPos) object;
if ((this.matricula == null && other.matricula != null) || (this.matricula != null && !this.matricula.equals(other.matricula))) {
return false;
}
return true;
}
@Override
public String toString() {
return "br.com.bb.uop.dcvipat.ade.entity.UorPos[ matricula=" + matricula + " ]";
}
}

法理实体：

@Entity
@Table(name = "Jurisdicionadas", catalog = "DIAGE", schema = "ade")
@XmlRootElement
@NamedQueries({
@NamedQuery(name="Jurisdicionadas.findAll", query="SELECT j FROM Jurisdicionadas j")})
public class Jurisdicionadas implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@GeneratedValue (strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Integer id;
@Column(name = "PrefixoJurisdicionada")
private Integer prefixoJurisdicionada;
@Size(max = 200)
@Column(name = "NomePrefixoJurisdicionada")
private String nomePrefixoJurisdicionada;
@JoinColumn(name = "Prefixo", referencedColumnName = "Prefixo")
@ManyToOne
private Prefixos prefixo;
public Jurisdicionadas() {
}
public Jurisdicionadas(Integer id) {
this.id = id;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getPrefixoJurisdicionada() {
return prefixoJurisdicionada;
}
public void setPrefixoJurisdicionada(Integer prefixoJurisdicionada) {
this.prefixoJurisdicionada = prefixoJurisdicionada;
}
public String getNomePrefixoJurisdicionada() {
return nomePrefixoJurisdicionada;
}
public void setNomePrefixoJurisdicionada(String nomePrefixoJurisdicionada) {
this.nomePrefixoJurisdicionada = nomePrefixoJurisdicionada;
}
public Prefixos getPrefixo() {
return prefixo;
}
public void setPrefixo(Prefixos prefixo) {
this.prefixo = prefixo;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
if (!(object instanceof Jurisdicionadas)) {
return false;
}
Jurisdicionadas other = (Jurisdicionadas) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "br.com.bb.uop.dcvipat.ade.entity.Jurisdicionadas[ id=" + id + " ]";
}
}

提前谢谢。

Answer 1

在htshame发布了很好的提示之后，我研究了Eclipselink Native SQL Query，并解决了这个问题，如下所示：

public List<UorPos> findUorPosExecutivosByPrefixo(Prefixos prefixo) {
return (List<UorPos>) getEntityManager().createNativeQuery("SELECT * FROM DIAGE.ade.UorPos WHERE prefixo IN (SELECT prefixoJurisdicionada FROM DIAGE.ade.Jurisdicionadas WHERE prefixo = (SELECT prefixo FROM DIAGE.ade.Jurisdicionadas WHERE prefixoJurisdicionada = ?)) AND codComissao IN (4345, 4346, 4347) ORDER BY nome, matricula", UorPos.class).setParameter(1, prefixo.getPrefixo()).getResultList();
}

但是，如果有人可以解决 JPQL 命名查询问题，那就更好了。