JPA Bidirectionnal Uknown column

Question

I have entities and they were unidirectional, so I decided to make them bi-directional, when they were unidirectional everything was good, but when I changed them to bi-directional I got some problems !!!!

I have a lots of entities but I will just refer, I have a problem in two entities one with OneToMany and the other with ManyToOne.

The first entity is called Agent (OneToMany with Project) :

@Entity
public class Agent implements Serializable {
    private static final long serialVersionUID = 1047335572433032863L;
    @GeneratedValue
    @Id
    private int id;
    @Basic
    private String fistname;
    @Basic
    private String lastname;
    @ManyToOne
    private Department department;
    @OneToMany(mappedBy = "agent")
    private List<Project> project;

    //Setters and getters ...
}

the second entity is called Project (ManyToOne with Agent) :

@Entity
public class Project implements Serializable {
    private static final long serialVersionUID = -6643569041998000573L;
    @GeneratedValue
    @Id
    private int id;
    @Basic
    private String crmCode;
    @Basic
    private String circuit;
    @Basic
    private String type;
    @Basic
    private String scope;
    @Basic
    @Temporal(TemporalType.DATE)
    private Date beginDate;
    @Basic
    @Temporal(TemporalType.DATE)
    private Date endDate;
    @ManyToOne
    private Agent agent;
    @ManyToOne
    private Service service;

    //Setters and getters
}

but when I launch some fetch queries I got this exception :

exception

javax.servlet.ServletException: javax.ejb.EJBException

root cause

javax.ejb.EJBException

root cause

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'AGENT_ID' in 'field list'
Error Code: 1054
Call: SELECT ID, BEGINDATE, CIRCUIT, CRMCODE, ENDDATE, SCOPE, TYPE, AGENT_ID, SERVICE_ID FROM PROJECT WHERE (CRMCODE = ?)
    bind => [1 parameter bound]
Query: ReadAllQuery(referenceClass=Project sql="SELECT ID, BEGINDATE, CIRCUIT, CRMCODE, ENDDATE, SCOPE, TYPE, AGENT_ID, SERVICE_ID FROM PROJECT WHERE (CRMCODE = ?)")

root cause

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'AGENT_ID' in 'field list'
Error Code: 1054
Call: SELECT ID, BEGINDATE, CIRCUIT, CRMCODE, ENDDATE, SCOPE, TYPE, AGENT_ID, SERVICE_ID FROM PROJECT WHERE (CRMCODE = ?)
    bind => [1 parameter bound]
Query: ReadAllQuery(referenceClass=Project sql="SELECT ID, BEGINDATE, CIRCUIT, CRMCODE, ENDDATE, SCOPE, TYPE, AGENT_ID, SERVICE_ID FROM PROJECT WHERE (CRMCODE = ?)")

root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'AGENT_ID' in 'field list'

Here I can understand the problem, JPA is referencing to AGENT_ID column which doesn't exist in the database, the same with the field service in Project.

The problem is why this weird error?, shouldn't it reference to Agent.ID instead of AGENT_ID when it creates the request to the database, I tried to change the name of the id in the Agent entity to AGENT_ID using @Column but I got the same problem just with AGENT_AGENT_ID !!!!

Anyone knows how to solve this problem, I'm on glassfish, thanks all :)

Answer 1

i've annotated the id of the Agent entity with @Column(name = "AGENT_ID") but i got the same problem just JPA report that AGENT_AGENT_ID doesn't exist

What you did will change the ID column in the Agent table, but it is not going to affect the join column in Project table.

You should do something like:

class Project {
    //....

    @ManyToOne
    @JoinColumn(name="YOUR_AGENT_ID_COL_NAME");
    private Agent agent;
    //......
}