代码之家 › 专栏 › 技术社区 › jMarcel

jpql子查询-异常此表达式在此上下文中的表无效

jpql subquery jpa java

jMarcel · 技术社区 · 8 年前

我有一个(内部的,但正在工作的)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查询还可以。

有人能帮我解决这个问题吗?

我研究过的一些链接:

Using IN with a subquery

我使用的是Eclipse版本2.5.2(与NETBease80.2集成)、GalasFISH 4.1、Java1.7.0Y7.1。

更新

UORPS实体:

@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 + " ]";
    }
}

事先谢谢。

1 回复 | 直到 8 年前

jMarcel 8 年前

在HTshame发布的伟大提示之后,我研究了 Eclipselink Native SQL Queries 解决方法如下:

    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命名的查询问题,那就更好了。