Foreign key problem in Java Persistance API with Netbeans 6.9.1

Question

Hi All,

I have an error in setting up Java Persistence in Netbeans. I have used MySQL as Back-End Database Server.

My Code is as below,

Database

CREATE TABLE `hub` (
 `hub_ID` INT(11) NOT NULL AUTO_INCREMENT,
 `hub_Name` VARCHAR(45) NOT NULL,
 PRIMARY KEY (`hub_ID`),
 UNIQUE INDEX `hub_Name` (`hub_Name`)
)ENGINE=MyISAM

CREATE TABLE `company` (
 `company_ID` INT(11) NOT NULL AUTO_INCREMENT,
 `company_Name` VARCHAR(45) NOT NULL,
 `company_hub_ID` INT(11) NOT NULL,
 PRIMARY KEY (`company_ID`),
 INDEX `fk_company_hub1` (`company_hub_ID`)
 )

Persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"&gt;
  <persistence-unit name="testJPAPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>testjpa.Hub</class>
    <class>testjpa.Company</class>
    <properties>
      <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/logistikumdispatch"/>
      <property name="javax.persistence.jdbc.password" value="rootroot"/>
      <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
      <property name="javax.persistence.jdbc.user" value="root"/>
    </properties>
  </persistence-unit>
</persistence>

Hub.java (Entity Class in Project)

    /*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package testjpa;

import java.io.Serializable;
import java.util.Collection;
import javax.persistence.Basic;
import javax.persistence.CascadeType;
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.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Table;

/**
 *
 * @author Anjum
 */
@Entity
@Table(name = "hub")
@NamedQueries({
    @NamedQuery(name = "Hub.findAll", query = "SELECT h FROM Hub h"),
    @NamedQuery(name = "Hub.findByHubID", query = "SELECT h FROM Hub h WHERE h.hubID = :hubID"),
    @NamedQuery(name = "Hub.findByHubName", query = "SELECT h FROM Hub h WHERE h.hubName = :hubName")})
public class Hub implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "hub_ID")
    private Integer hubID;
    @Basic(optional = false)
    @Column(name = "hub_Name")
    private String hubName;

    @OneToMany(mappedBy="hub", cascade=CascadeType.ALL)
    @JoinColumn(name="company_hub_ID")
    private Collection<Company> companies;

    public Hub() {
    }

    public Hub(Integer hubID) {
        this.hubID = hubID;
    }

    public Hub(Integer hubID, String hubName) {
        this.hubID = hubID;
        this.hubName = hubName;
    }

    public Integer getHubID() {
        return hubID;
    }

    public void setHubID(Integer hubID) {
        this.hubID = hubID;
    }

    public String getHubName() {
        return hubName;
    }

    public void setHubName(String hubName) {
        this.hubName = hubName;
    }

    public Collection<Company> getCompanies()
    {
        return companies;
    }

    public void setCompanies(Collection<Company> companies)
    {
        this.companies = companies;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (hubID != null ? hubID.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 Hub)) {
            return false;
        }
        Hub other = (Hub) object;
        if ((this.hubID == null && other.hubID != null) || (this.hubID != null && !this.hubID.equals(other.hubID))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "testjpa.Hub[hubID=" + hubID + "]";
    }

}

Company.java (Entity Class in Project)

package testjpa;

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.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

/**
 *
 * @author Anjum
 */
@Entity
@Table(name = "company")
@NamedQueries({
    @NamedQuery(name = "Company.findAll", query = "SELECT c FROM Company c"),
    @NamedQuery(name = "Company.findByCompanyID", query = "SELECT c FROM Company c WHERE c.companyID = :companyID"),
    @NamedQuery(name = "Company.findByCompanyName", query = "SELECT c FROM Company c WHERE c.companyName = :companyName"),
    @NamedQuery(name = "Company.findByCompanyhubID", query = "SELECT c FROM Company c WHERE c.companyhubID = :companyhubID")})
public class Company implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "company_ID")
    private Integer companyID;
    @Basic(optional = false)
    @Column(name = "company_Name")
    private String companyName;
    @Basic(optional = false)
    @Column(name = "company_hub_ID")
    private int companyhubID;
    @ManyToOne()
    @JoinColumn(name="hub_ID", insertable=false, updatable=false)
    private Hub hub;
    public Company() {
    }

    public Company(Integer companyID) {
        this.companyID = companyID;
    }

    public Company(Integer companyID, String companyName, int companyhubID) {
        this.companyID = companyID;
        this.companyName = companyName;
        this.companyhubID = companyhubID;
    }

    public Integer getCompanyID() {
        return companyID;
    }

    public void setCompanyID(Integer companyID) {
        this.companyID = companyID;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public int getCompanyhubID() {
        return companyhubID;
    }

    public void setCompanyhubID(int companyhubID) {
        this.companyhubID = companyhubID;
    }

    public Hub getHub()
    {
        return hub;
    }

    public void setHub(Hub hub)
    {
        this.hub = hub;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (companyID != null ? companyID.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 Company)) {
            return false;
        }
        Company other = (Company) object;
        if ((this.companyID == null && other.companyID != null) || (this.companyID != null && !this.companyID.equals(other.companyID))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "testjpa.Company[companyID=" + companyID + "]";
    }

}

Main Program

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package testjpa;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

/**
 *
 * @author Anjum
 */
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("testJPAPU");
        EntityManager em = emf.createEntityManager();

        em.getTransaction().begin();

        Company c1 = new Company();
        c1.setCompanyName("ays");


        Hub h1 = new Hub();
        h1.setHubName("abc");
        em.persist(h1);
        c1.setHub(h1);

        em.persist(c1);
        em.flush();

    }
}

Output

[EL Info]: 2010-09-03 14:39:02.639--ServerSession(3199106)--EclipseLink, version: Eclipse Persistence Services - 2.0.2.v20100323-r6872
[EL Info]: 2010-09-03 14:39:03.371--ServerSession(3199106)--file:/C:/Users/Anjum/Documents/NetBeansProjects/testJPA/build/classes/_testJPAPU login successful

Problem

My problem is that, the foreign key company.company_hub_ID is updated in database with comapany.company_name = "ays". But there is always 0 in company.company_hub_ID.

Hub is also updated properly. Means there is no connectivity or JPA problem. Problem lies within the relationship only.

Thanks in advance for helping me.

Answer 1

Something does not line up between your mapping and the database schema. Is the database schema generated by the JPA provider?

The mapping defines a hub_id join column on the company class, yet the database schema is missing that column (it has "company_hub_ID" instead).

Try the following:

Remove the @JoinColumn from the side of the @OneToMany. @OneToMany+@JoinColumn is only used for mapping unidirectional onetomany associations on a foreign key (without a join table). Your association is a bidirectional onetomany, thus @OneToMany with mappedBy on the inverse side and @ManyToOne + @JoinColumn (optional) on the owning side. Do not set insertable/updatable to false.

Answer 2

I agree with romanb's suggestion.

Since the association is bidirectional, make sure you set "both sides of the link" as pointed out in this post.

Try changing your code like this:

public class Hub {
    ...
    @OneToMany(mappedBy="hub", cascade=CascadeType.ALL)
    private Collection<Company> companies = new ArrayList<Company>();

    ...
    public void addCompany(Company company) {
        company.setHub(this);
        this.companies.add(company);
    }       
    ...
}

public class Company {
    ...         
    @ManyToOne
    @JoinColumn(name="company_hub_ID")
    private Hub hub;    
    ...
}

public class Main {

    public static void Main(String[] args) {
        ...
        Company c1 = new Company();
        c1.setCompanyName("ays");

        Hub h1 = new Hub();
        h1.setHubName("abc");
        h1.addCompany(c1);
        em.persist(h1);
    }

}

Also, use an Id generation strategy other than GenerationType.IDENTITY, as the identifier is not accessible until after the insert has occurred.

Answer 3

As already mentioned, you are not supposed to use a JoinColumn on both sides of your OneToMany / ManyToOne association, but only on the owning side (the many side). Remove it from the OneToMany side and fix the column name.

You're also not supposed to have a companyhubID attribute in the Company entity to hold the foreign key (such things are actually supposed to be hidden at the object level). Remove both the attribute and the insertable=false, updatable=false from the JoinColumn.

So the mappings should be:

@Entity
...
public class Hub implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "hub_ID")
    private Integer hubID;
    @Basic(optional = false)
    @Column(name = "hub_Name")
    private String hubName;

    @OneToMany(mappedBy="hub", cascade=CascadeType.ALL)
//    @JoinColumn(name="company_hub_ID") // THIS IS WRONG
    private Collection companies = new HashSet()
    ...
    // method to set both sides of the link of the bidirectional association
    public void addToCompanies(Company company) {
        company.setHub(this);
        this.companies.add(company);
    }
}

And

@Entity
...
public class Company implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "company_ID")
    private Integer companyID;
    @Basic(optional = false)
    @Column(name = "company_Name")
    private String companyName;
//    @Basic(optional = false) 
//    @Column(name = "company_hub_ID")
//    private int companyhubID;          // NO, DON'T

    @ManyToOne()
    @JoinColumn(name="company_hub_ID"/*, insertable=false, updatable=false*/)
    private Hub hub;
    ...
}

Some additional remarks:

• Using the IDENTITY strategy for ID generation is fine.
• You should use InnoDB tables for referential integrity.

Also note that your demo code is incorrect, you're not setting the association appropriately. It should be:

Company c1 = new Company();
c1.setCompanyName("ays");
Hub h1 = new Hub();
h1.setHubName("abc");
h1.addToCompanies.add(c1);  // set the bidirectional association

em.persist(h1);             // JPA will cascade the persist
em.flush();

Answer 4

Hi,

Finally I have solved that problem with romanB's support.

Thanks for your help "ROMANB".

Look below how did I solved that, you can use this technique to implement persistence with foreign keys in netbeans.

public class Company implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "company_ID", nullable=true)
    private Integer companyID;
    @Basic(optional = false)
    @Column(name = "company_Name")
    private String companyName;
    @Basic(optional = false)
    @Column(name = "company_hub_ID", nullable=false, updatable=false, insertable=false)
    private int companyhubID;
    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="company_hub_ID")
    private Hub hub_ref;
..........
..........

public class Hub implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "hub_ID")
    private Integer hubID;
    @Basic(optional = false)
    @Column(name = "hub_Name")
    private String hubName;

    @OneToMany(mappedBy="hub_ref", cascade=CascadeType.ALL)
    private Collection<Company> companies;

    ................
    ................
  \\ in Hub class
public void addCompany(Company company)
    {
        if(this.companies == null)
        {
            this.companies = new ArrayList<Company>();
        }
        this.companies.add(company);
        company.setHub(this);
    }

And finally the main class

 public static void main(String[] args) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("testJPAPU");
        EntityManager em = emf.createEntityManager();



        Company c1 = new Company();
        c1.setCompanyName("ays");

        Hub h1 = new Hub();
        h1.setHubName("abc");

        em.getTransaction().begin();

        h1.addCompany(c1);
        em.persist(h1);

        em.flush();

    }

Use eclipse link 2.0 to get JPA 2.0 support.

Thanks again, and thanks to stack overflow.

Anjum Shrimali.