Unable to join two table JPA

Question

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)])

Removed references to JavaFX (from title and tags), as your error is not related to the UI framework. — Slaw, Sep 27 '22 at 09:10
You have two writable mappings for the Subscriber "idsubscriber" column - both the idsubscriber property and the deon property will attempt to set that column with a value. Your deon OneToOne mapping will attempt to pull it from the referenced Deon.idsubscriber String property, so it isn't clear which controls or should be setting the value - how are you setting a idsubscriber? Did you maybe mean for the Deon to have a 1:1 reference (foreign key) to Subscriber, with it having a FK idsubscriber that references the Subscriber Id value? — Chris, Sep 27 '22 at 22:00

score 2 · Answer 1 · answered Sep 27 '22 at 09:07

2

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

answered Sep 27 '22 at 09:07

YJR

1,099
3
13

I did that but still same error – Adm Sep 27 '22 at 10:31
@OneToOne(mappedBy = "Deon") private Subscriber subscriber; – YJR Sep 27 '22 at 10:48
Can you add above to deon class.Then check whether it work. – YJR Sep 27 '22 at 10:49
i did that also but still not work, – Adm Sep 27 '22 at 11:00
inside join column annotation change referencecolumnname to id and remove name param in it with value. Because it should map through id That is primary key. – YJR Sep 27 '22 at 11:09
same error : Multiple writable mappings exist for the field [subscriber.idsubscriber]. Only one may be defined as writable, all others must be specified read-only. – Adm Sep 27 '22 at 11:36
@Adm Try to create a much simpler [mre] that demonstrates the problem (for instance, I suspect you don't need all those columns for the example). Show your JPA configurations and how you execute the query. Basically, give us something that we can easily copy-and-paste onto our computer so that we can reproduce the error ourselves. And by creating this example, you may even find what you're doing wrong. – Slaw Sep 27 '22 at 12:20
1

@Adm Though this may be relevant: https://stackoverflow.com/questions/7952115/multiple-writable-mappings-exception-in-eclipselink – Slaw Sep 27 '22 at 12:22

score 0 · Answer 2 · answered Sep 27 '22 at 22:11

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
}

score 0 · Accepted Answer · answered Sep 29 '22 at 12:30

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

answered Sep 29 '22 at 12:30

Adm

13
3

You had to have made other changes to your model than just adding these two properties - Subscriber had both an 'id' and 'idSubscriber' properties (and DB columns) you were attempting to use in these mappings. You also have to have other mappings to the idSubscriber column in Deon as "JoinColumn(name="idsubscriber", insertable=false, updatable=false)" means it cannot be set from this reference. So you must be manually setting the FK value instead of letting JPA do it for you when it assigns primary keys (from the ID value in Subscriber based on the previous mappings). – Chris Nov 29 '22 at 16:39

Unable to join two table JPA

3 Answers3