bugfix> java > 投稿

(非効率的ですが動作している)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 + " ]";
    }
}

前もって感謝します。

回答 1 件
  • htshameが投稿した素晴らしいヒントの後、Eclipselink Native SQLクエリについて調査し、以下のように問題を回避しました。

       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の名前付きクエリの問題を解決できれば、より良いでしょう。

あなたの答え