JPA，NamedQueries在两个表上使用JOIN查询奇怪的beaviour

Question

这是我的第一个Java代码。我正在尝试获得一个RESTfull web服务，它返回一个JSON编码的数据库视图。

我想要的MySQL中的结果（不是json编码的）是使用以下查询获得的：

SELECT t0.id, t0.callsign, t0.description, t0.name, t0.noradID, t0.tle, t1.name
FROM scl t0
LEFT OUTER JOIN sccommchannel t1 ON t1.id_sc = t0.id

结果是与相关航天器（scl）的详细信息相关联的每个通道（sccommchannel）的一行。因此，每个航天器都有一个或多个通道的关联。

我创建了一个实体类（在netbeans的帮助下）如下：

一揽子实体；

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.Lob;
import javax.persistence.NamedQuery;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.SecondaryTable;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
@Entity
@Table(name = "scl")
@SecondaryTable(name = "sccommchannel", pkJoinColumns = {@PrimaryKeyJoinColumn(name = "id_sc", referencedColumnName = "id")})
@XmlRootElement
@NamedQuery(name = "Sclc.findAll", query = "SELECT d FROM Scl s, Sccommchannel c LEFT OUTER JOIN c.id_sc d")
public class Sclc implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @NotNull
    @JoinColumn
    @Column(name = "id", table = "scl")
    private Integer id;
    @Size(max = 255)
    @Column(name = "callsign", table = "scl")
    private String callsign;
    @Size(max = 100)
    @Column(name = "description", table = "scl")
    private String description;
    @Size(max = 50)
    @Column(name = "name", table = "scl")
    private String name;
    @Size(max = 100)
    @Column(name = "noradID", table = "scl")
    private String noradID;
    @Lob
    @Size(max = 2147483647)
    @Column(name = "tle", table = "scl")
    private String tle;
    @Column(name = "name", table = "sccommchannel")
    private String ch_name;
    public Sclc() {
    }
    public String getCh_name() {
        return ch_name;
    }
    public void setCh_name(String ch_name) {
        this.ch_name = ch_name;
    }
    public String getCallsign() {
        return callsign;
    }
    public void setCallsign(String callsign) {
        this.callsign = callsign;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getNoradID() {
        return noradID;
    }
    public void setNoradID(String noradID) {
        this.noradID = noradID;
    }
    public String getTle() {
        return tle;
    }
    public void setTle(String tle) {
        this.tle = tle;
    }
    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.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 Sclc)) {
            return false;
        }
        Sclc other = (Sclc) 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 "entities.Sclc[ id=" + id + " ]";
    }
}

在这段代码中，我通过@PrimaryKeyJoinColumn传递JOIN参数，这是正确的方式吗？

以及相关的RESTfull类：

package service;
import entities.Sclc;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
@Stateless
@Path("/sclc/all")
public class SclcFacadeREST extends AbstractFacade<Sclc> {
    @PersistenceContext(unitName = "GENSO_jsonPU")
    private EntityManager em;
    public SclcFacadeREST() {
        super(Sclc.class);
    }
    @GET
    @Override
    @Produces({"application/json"})
    public List<Sclc> findAll() {
        return super.findAll();
    }
    @java.lang.Override
    protected EntityManager getEntityManager() {
        return em;
    }
}

但当我发出http请求时，结果并不像我预期的那样：每个通道都有一行与右侧相关的航天器（因此行的总数是正确的），但每个航天器的通道名称始终相同。

示例：如果我在数据库中只有3个通道的ISS航天器

MySQL结果：

1 "CALLSIGN" "sometext" "ISS" "number" "Long_string" "Channel_1"
1 "CALLSIGN" "sometext" "ISS" "number" "Long_string" "Channel_2"
1 "CALLSIGN" "sometext" "ISS" "number" "Long_string" "Channel_3"

Java结果：

1 "CALLSIGN" "sometext" "ISS" "number" "Long_string" "Channel_1"
1 "CALLSIGN" "sometext" "ISS" "number" "Long_string" "Channel_1"
1 "CALLSIGN" "sometext" "ISS" "number" "Long_string" "Channel_1"

作为JPA查询，我已经尝试过：

SELECT s FROM Scl s
SELECT s, c FROM Scl s, Sccommchannel c WHERE s.id = c.id_sc

如果有人能告诉我如何查看从JPA生成的MySQL代码，用于调试，这也会很有用。

我可能犯了一个非常愚蠢的错误。。。提前感谢，Francesco

Answer 1

您的查询没有任何意义，

从Sclc s中选择d，Sccommchannel c LEFT OUTER JOIN c.id_sc d

您没有给出从s到c的任何联接，因此将获得每个Sccommchannel的所有Sclc。

此外，您正在使Sclc映射到两个表，因此将sccommchannel映射到类sccommchannel也没有任何意义。

不确定你想做什么，但也许只是

从Sclc的中选择

或者重新设计你的模型以使其合理。