JPA, NamedQueries strange beaviour with JOIN on two tables

this is my first Java code. I'm trying to obtain a RESTfull web service that returns a view on a database JSON encoded.

The result that i want (not json encoded) in MySQL is obtained using the following query:

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

Results are a row for each channel (sccommchannel) associated with the details of the associated spacecraft (scl). So there is an association of one or more channel for each spacecraft.

I created an entity class (with the help of netbeans) as follow:

package entities;

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

}

In this code I pass the JOIN parameters through the @PrimaryKeyJoinColumn, is it the right way?

And the related RESTfull class:

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;
    }

}

but when I make the http request, results are not as I expect: there is a row for each channel with the right associated spacecraft (so the total number of rows is right), but the name of the channel for each spacecraft is always the same.

Example: If I have only ISS spacecraft with 3 channels in the database

MySQL result:

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 result:

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"

As JPA query I already tried:

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

It could be useful also if some one can tell me how to see the MySQL code generated from the JPA, for debugging.

it is possible that I'm making a really stupid mistake... Thanks in advance, Francesco

---

Your query does not make any sense,

SELECT d FROM Sclc s, Sccommchannel c LEFT OUTER JOIN c.id_sc d

You are not giving any join from s to c, so will get all Sclc for every Sccommchannel.

Also you are making Sclc map to both tables, so also mapping sccommchannel to a class Sccommchannel does not make any sense.

Not sure what you are trying to do, but perhaps just,

SELECT s FROM Sclc s

Or rework your model to make sense.