Why will foreign key data will not persist to mysql database?

Question

I am working with a basic mySQL relational database using java. I have attached the entity classes for two tables that have a 1:M relationship. I have defined the 1 is to many and many is to 1 relationships in the entity tables. In the Projects class, I wanted to declare the foreign key (private int contractor_id) along with getters and setters (as shown commented out), but I kept getting a compile error stating the following *Multiple writable mappings exist for the field [projects.contractor_id]. Only one may be defined as writable, all others must be specified read-only.* I therefore commented them out as their value is set already in the BusinessAccount class. The data is now persisting to the database using the 'addProject()' method I have shown. However, the field contractor_id (ie the foreign key) is being passed as null to the Projects table. I have a value for contractor_id for the session (named sessionContractorId) but I am unable to pass it to the database as I have no setter for this table. Any suggestions would be appreciated on how to persist the value for the foreign key to the database?

@Entity
@Table(name = "business_accounts")
public class BusinessAccount {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

@Column(name = "first_name")
private String firstName;

@Column(name = "surname")
private String surname;

@OneToMany(mappedBy = "businessAccount", fetch = FetchType.EAGER, cascade = { CascadeType.ALL })
private List<Projects> projects;



public int getId() {
    return id;
}

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

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public String getSurname() {
    return surname;
}

public void setSurname(String surname) {
    this.surname = surname;
}

public List<Projects> getProjects()
{
    if (projects == null)
    {
        projects = new ArrayList<Projects>();
    }

    return projects;
}

public void setProjects(List<Projects> projects)
{
    this.projects = projects;
}

}



@Entity
@Table(name = "projects")
public class Projects {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int project_id;

@Column(name = "project_name")
private String projectName;

@Column(name = "project_description")
private String projectDescription;

//@Column(name = "contractor_id")
//private int contractorId;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({ @JoinColumn(name = "contractor_id", referencedColumnName="id") })
private BusinessAccount businessAccount;


public BusinessAccount getBusinessAccount() {
    if (businessAccount == null) {
        businessAccount = new BusinessAccount();
    }
    return businessAccount;
}

public void setBusinessAccount(BusinessAccount businessAccount) {
    this.businessAccount = businessAccount;
}

public int getProject_id() {
    return project_id;
}

public void setProject_id(int project_id) {
    this.project_id = project_id;
}

public String getProjectName() {
    return projectName;
}

public void setProjectName(String projectName) {
    this.projectName = projectName;
}

public String getProjectDescription() {
    return projectDescription;
}

public void setProjectDescription(String projectDescription) {
    this.projectDescription = projectDescription;
}

//public int getContractorId() {
    //return contractorId;
//}

//public void setContractorId(int contractorId) {
    //this.contractorId = contractorId;
//}

}



@ManagedBean
@ViewScoped
public class ProjectBean implements Serializable {

private static final long serialVersionUID = -2107387060867715013L;
private static final String PERSISTENCE_UNIT_NAME = "NeedABuilderUnit";
private static EntityManagerFactory factory;

private Projects projects;


private List<BusinessAccount> businessAccount;

public ProjectBean() {
    factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
    EntityManager em = factory.createEntityManager();

    List<BusinessAccount> businessAccount = em.createQuery("from BusinessAccount a", BusinessAccount.class)
            .getResultList();
    em.close();
    setBusinessAccount(businessAccount);
}

@PostConstruct
public void init() {
    projects = new Projects();
}

public String addProject() {
    factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
    EntityManager em = factory.createEntityManager();   
        em.getTransaction().begin();

        String sessionEmail=Util.getEmail();
        Query myQuery = em.createQuery("SELECT u FROM BusinessAccount u WHERE u.email=:email");
        myQuery.setParameter("email", sessionEmail);
        List<BusinessAccount> accounts=myQuery.getResultList();
        int sessionContractorId=accounts.get(0).getId();
        em.persist(projects);
        em.getTransaction().commit();
        em.close();

        return "success";
    }

Answer 1

There are two things I notice about the code.

First, the code should work with entities and ignore the id fields for the particular entities. So when you fetch the account, it should grab the entity instead of the id:

//Don't do this
List<BusinessAccount> accounts=myQuery.getResultList();
int sessionContractorId=accounts.get(0).getId();

//Instead do this
List<BusinessAccount> accounts=myQuery.getResultList();
BusinessAccount account =accounts.get(0); //hopefully an account exists

Second, in JPA you are responsible for managing both sides of an association. So you must add the Account to the Project and on the other side of the relationship set the Project for the Account. I never see this occur in the code, I only see the projects (not sure of its origin) being persisted. Assuming projects is a List<Project> it would look something like this:

public String addProject() {
        factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();   
        em.getTransaction().begin();

        String sessionEmail=Util.getEmail();
        Query myQuery = em.createQuery("SELECT u FROM BusinessAccount u WHERE u.email=:email");
        myQuery.setParameter("email", sessionEmail);
        List<BusinessAccount> accounts=myQuery.getResultList();
        BusinessAccount account =accounts.get(0);

        projects.setBusinessAccount(account); //managing both sides
        account.getProjects().add(projects); //managing both sides
        em.persist(projects);
        em.getTransaction().commit();
        em.close();

        return "success";
    }

On a side note, you may want to change the class name to Project and use the variable name project since it more accurately depicts the relationship. Also since you are creating a new Project you will need to instantiate the List<BusinessAccount>:

List<BusinessAccount> projects = new ArrayList<BusinessAccount>();

Hopefully this will solve your issues, I would recommend watching a this video tutorial I created on Bidirectional One To Many Relationships.

Answer 2

You should use @JoinColumn(name="id_user", insertable = false, updatable = false)

Please refer: Multiple writable mappings in JPA?

Hope this helps :)