如何更正子查询

Question

我正在尝试获取与登录用户在同一年级的同学列表。

SELECT * FROM ziak WHERE idRocnik = (
SELECT idRocnik FROM Ziak WHERE email = "[e-mail]")

我有这个功能

  public List<T> findSpoluziakov(){        
            String loggedUser = FacesContext.getCurrentInstance().getExternalContext().getRemoteUser();
            Query q = getEntityManager().createQuery("SELECT z FROM Ziak z WHERE z.idRocnik = (SELECT z.idRocnik FROM Ziak z WHERE z.email = :email)");
            q.setParameter("email", loggedUser);
            List<T> vysledok = q.getResultList();
             return vysledok;
    }

但我得到了错误：

原因：异常[EclipseLink-6069]（Eclipse Persistence Services-2.5.0.V20130507-3FAAC2B):org.Eclipse.Persistence.exceptions.QueryException异常说明：此表达式中的字段[Rocnik.IdRocnik]在此上下文中的表无效。

我认为问题是，我的子查询不是像我说的那样只得到一列结果（在我的子查询中是“select z.idrocnik”），而是整个列表。

另外，我的函数试图使用“rocnik”实体（我的另一个实体具有主键idRocnik），但我在一个名为“ziak”的实体中拥有所有我需要的东西（有一个引用rocnik.idRocnik的外键）

我的“Ziak”实体：

@Entity
@Table(name = "ziak", schema="diplomovkadb")
@XmlRootElement
public class Ziak implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "idZiak")
private Integer idZiak;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 45)
@Column(name = "Meno")
private String meno;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 45)
@Column(name = "Priezvisko")
private String priezvisko;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 45)
@Column(name = "Rodne_Cislo")
private String rodneCislo;
@Size(max = 4)
@Column(name = "Pohlavie")
private String pohlavie;
@Size(max = 45)
@Column(name = "Email")
private String email;
@Size(max = 45)
@Column(name = "Telefon")
private String telefon;
@Size(max = 45)
@Column(name = "Mesto")
private String mesto;
@Size(max = 45)
@Column(name = "Ulica")
private String ulica;
@Size(max = 45)
@Column(name = "CisloUlice")
private String cisloUlice;
@Size(max = 45)
@Column(name = "PSC")
private String psc;
@JoinColumn(name = "IdRocnik", referencedColumnName = "IdRocnik")
@ManyToOne
private Rocnik idRocnik;
@JoinColumn(name = "IdOdbor", referencedColumnName = "IdOdbor")
@ManyToOne
private Odbor idOdbor;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "ziak")
private Collection<Skuskaziak> skuskaziakCollection;
public Ziak() {
}
public Ziak(Integer idZiak) {
    this.idZiak = idZiak;
}
public Ziak(Integer idZiak, String meno, String priezvisko, String rodneCislo) {
    this.idZiak = idZiak;
    this.meno = meno;
    this.priezvisko = priezvisko;
    this.rodneCislo = rodneCislo;
}
public Integer getIdZiak() {
    return idZiak;
}
public void setIdZiak(Integer idZiak) {
    this.idZiak = idZiak;
}
public String getMeno() {
    return meno;
}
public void setMeno(String meno) {
    this.meno = meno;
}
public String getPriezvisko() {
    return priezvisko;
}
public void setPriezvisko(String priezvisko) {
    this.priezvisko = priezvisko;
}
public String getRodneCislo() {
    return rodneCislo;
}
public void setRodneCislo(String rodneCislo) {
    this.rodneCislo = rodneCislo;
}
public String getPohlavie() {
    return pohlavie;
}
public void setPohlavie(String pohlavie) {
    this.pohlavie = pohlavie;
}
public String getEmail() {
    return email;
}
public void setEmail(String email) {
    this.email = email;
}
public String getTelefon() {
   return telefon;
}
public void setTelefon(String telefon) {
    this.telefon = telefon;
}
public String getMesto() {
    return mesto;
}
public void setMesto(String mesto) {
    this.mesto = mesto;
}

public String getUlica() {
    return ulica;
}
public void setUlica(String ulica) {
    this.ulica = ulica;
}
public String getCisloUlice() {
    return cisloUlice;
}
public void setCisloUlice(String cisloUlice) {
    this.cisloUlice = cisloUlice;
}
public String getPsc() {
    return psc;
}

public void setPsc(String psc) {
    this.psc = psc;
}
public Rocnik getIdRocnik() {
    return idRocnik;
}
public void setIdRocnik(Rocnik idRocnik) {
    this.idRocnik = idRocnik;
}
public Odbor getIdOdbor() {
    return idOdbor;
}
public void setIdOdbor(Odbor idOdbor) {
    this.idOdbor = idOdbor;
}
@XmlTransient
public Collection<Skuskaziak> getSkuskaziakCollection() {
    return skuskaziakCollection;
}
public void setSkuskaziakCollection(Collection<Skuskaziak> skuskaziakCollection) {
    this.skuskaziakCollection = skuskaziakCollection;
}
@Override
public int hashCode() {
    int hash = 0;
    hash += (idZiak != null ? idZiak.hashCode() : 0);
    return hash;
}

@Override
public boolean equals(Object object) {
    // TODO: Warning - this method won't work in the case the id fields are not set
    if (!(object instanceof Ziak)) {
        return false;
    }
    Ziak other = (Ziak) object;
    if ((this.idZiak == null && other.idZiak != null) || (this.idZiak != null && !this.idZiak.equals(other.idZiak))) {
        return false;
    }
    return true;
}

@Override
public String toString() {
    return "Entity.jpa.Ziak[ idZiak=" + idZiak + " ]";
}
}

我也试图用entityManager实现这一点，但得到了相同的结果。

Answer 1

将=更改为“in”如何：

    Query q = getEntityManager().createQuery("SELECT z FROM Ziak z WHERE z.idRocnik in (SELECT z.idRocnik FROM Ziak z WHERE z.email = :email)");

或者（因为JPQL是ORM，所以有时您不需要它像SQL一样）

Select z from Ziak z where z.email = :email;