Question 113468 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Unable to join two table JPA

I have two table and I try to join use column idsubscriber but show some error.

Class Subscriber:

@Entity
@Table(name = "subscriber")
@XmlRootElement
@NamedQueries({
      @NamedQuery(name = "Subscriber.findAll", query = "SELECT s FROM Subscriber s")
    , @NamedQuery(name = "Subscriber.findById", query = "SELECT s FROM Subscriber s WHERE s.id = :id")
    , @NamedQuery(name = "Subscriber.findByIdsubscriber", query = "SELECT s FROM Subscriber s WHERE s.idsubscriber = :idsubscriber")
    , @NamedQuery(name = "Subscriber.findByName", query = "SELECT s FROM Subscriber s WHERE s.name = :name")
    , @NamedQuery(name = "Subscriber.findByPhone", query = "SELECT s FROM Subscriber s WHERE s.phone = :phone")
    , @NamedQuery(name = "Subscriber.findByLoc", query = "SELECT s FROM Subscriber s WHERE s.loc = :loc")
    , @NamedQuery(name = "Subscriber.findByNum", query = "SELECT s FROM Subscriber s WHERE s.num = :num")
    , @NamedQuery(name = "Subscriber.findByType", query = "SELECT s FROM Subscriber s WHERE s.type = :type")
    , @NamedQuery(name = "Subscriber.findByDate", query = "SELECT s FROM Subscriber s WHERE s.date = :date")})
public class Subscriber implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    private Integer id;
    @Basic(optional = false)
    @Column(name = "idsubscriber", nullable = false, length = 50)
    private String idsubscriber;
    @Basic(optional = false)
    @Column(name = "name", nullable = false, length = 200)
    private String name;
    @Basic(optional = false)
    @Column(name = "phone", nullable = false, length = 45)
    private String phone;
    @Basic(optional = false)
    @Column(name = "loc", nullable = false, length = 255)
    private String loc;
    @Basic(optional = false)
    @Column(name = "num", nullable = false)
    private int num;
    @Column(name = "type")
    private Integer type;
    @Basic(optional = false)
    @Column(name = "date", nullable = false, length = 50)
    private String date;
    
     @OneToOne(targetEntity = Deon.class, cascade = CascadeType.ALL)
    @JoinColumn(name = "idsubscriber",referencedColumnName="idsubscriber")
    private Deon deon;

    public Subscriber() {
    }

    public Subscriber(Integer id) {
        this.id = id;
    }

    public Subscriber(Integer id,String name, String phone,int type, String loc, int num, String date) {
        this.id = id;
        this.name = name;
        this.phone = phone;
        this.type=type;
        this.loc = loc;
        this.num = num;
        this.date = date;
    }
    
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getIdsubscriber() {
        return idsubscriber;
    }

    public void setIdsubscriber(String idsubscriber) {
        this.idsubscriber = idsubscriber;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }

    public int getNum() {
        return num;
    }

    public void setNum(int num) {
        this.num = num;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }
    public Deon getItems() {
        return deon;
    }

    public void setItems(Deon deon) {
        this.deon = deon;
    }

    @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 Subscriber)) {
            return false;
        }
        Subscriber other = (Subscriber) 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 "hiber.Subscriber[ id=" + id + " ]";
    }
    
}

Class Deon:

@Entity
@Table(name = "deon", catalog = "mngp", schema = "")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Deon.findAll", query = "SELECT d FROM Deon d")
    , @NamedQuery(name = "Deon.findById", query = "SELECT d FROM Deon d WHERE d.id = :id")
    , @NamedQuery(name = "Deon.findByIdsubscriber", query = "SELECT d FROM Deon d WHERE d.idsubscriber = :idsubscriber")
    , @NamedQuery(name = "Deon.findByPrice", query = "SELECT d FROM Deon d WHERE d.price = :price")
    , @NamedQuery(name = "Deon.findBySprice", query = "SELECT d FROM Deon d WHERE d.sprice = :sprice")
    , @NamedQuery(name = "Deon.findByDate", query = "SELECT d FROM Deon d WHERE d.date = :date")})
public class Deon implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    private Integer id;
    @Basic(optional = false)
    @Column(name = "idsubscriber", nullable = false, length = 50)
    private String idsubscriber;
    @Basic(optional = false)
    @Column(name = "price", nullable = false)
    private int price;
    @Column(name = "sprice")
    private Integer sprice;
    @Basic(optional = false)
    @Column(name = "date", nullable = false, length = 50)
    private String date;

    public Deon() {
    }

    public Deon(Integer id) {
        this.id = id;
    }

    public Deon(Integer id, String idsubscriber, int price, String date) {
        this.id = id;
        this.idsubscriber = idsubscriber;
        this.price = price;
        this.date = date;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getIdsubscriber() {
        return idsubscriber;
    }

    public void setIdsubscriber(String idsubscriber) {
        this.idsubscriber = idsubscriber;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    public Integer getSprice() {
        return sprice;
    }

    public void setSprice(Integer sprice) {
        this.sprice = sprice;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    @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 Deon)) {
            return false;
        }
        Deon other = (Deon) 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 "another.Deon[ id=" + id + " ]";
    }
    
}

Class Main:

List<Subscriber>ll=em.createQuery("SELECT s.name,d.price FROM Subscriber s join s.Deon d ").getResultList();
                 for(Subscriber f: ll){
                      System.out.println(f.getName()+ " -- " +f.getItems().getPrice());
                 }

Error:

org.eclipse.persistence.exceptions.DescriptorException
Exception Description: Multiple writable mappings exist for the field [subscriber.idsubscriber].  Only one may be defined as writable, all others must be specified read-only.
Mapping: org.eclipse.persistence.mappings.ManyToOneMapping[deon]
Descriptor: RelationalDescriptor(hiber.Subscriber --> [DatabaseTable(subscriber)])
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    i sloved

    i added in class subscriber

    @OneToOne(fetch = FetchType.EAGER,mappedBy="subscriber")
    private Deon deon;

    and added in class deon

    
@OneToOne(cascade={CascadeType.ALL})
   @JoinColumn(name="idsubscriber", insertable=false, updatable=false)
   private Subscriber subscriber;

    my query

    SELECT DISTINCT(s.id),s.name,s.type,s.num,d.price,d.date FROM Subscriber s join Deon d on s.idsubscriber=d.idsubscriber

  2. 0

    Do not use join here rather than you can rewrite that query as follows because hibernate/JPA do mapping itself for you.

    SELECT s.name , s.deon.price from subscriber s
    Login or Signup to reply.
  3. 0

    Issue from the error is you have the Subscriber "idsubscriber" column mapped with two separate java properties – the "deon" OneToOne property and the "idsubscriber" String property.

    If you meant for the Deon table to have a foreign key column that holds the subscriber ID value, try something more like:

    public class Subscriber implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    .. //(all other properties mapped the same, excluding idsubscriber!)
    @OneToOne(mappedBy="subscriber", cascade = CascadeType.ALL)
    private Deon deon;
    ..
}


public class Deon implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    private Integer id;
    //If you really need a string for this:
    @Column(name = "idsubscriber", nullable = false, length = 50, updatable=false, insertable=false)
    private String idsubscriber;
    @JoinColumn(name = "idsubscriber",referencedColumnName="idsubscriber")
    private Subscriber subscriber;//this reference is what now controls and sets the "idsubscriber" fk column.
    .. //other properties, mappings and methods unchanged
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search