EclipseLink / JPA – com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column

This post explains the common exception thrown in your eclipselink or JPA program when you miss the @JoinColumn annotation to refer the foreign key value.

@JoinColumn is very important, cause it is used to indicate that there is a Joining at the annotated property, where the persistence implementation will take care of and consider the relationship annotation. The annotation @JoinColumn indicates that this entity is the owner of the relationship (that is: the corresponding table has a column with a foreign key to the referenced table).

Whatever the annotation that are used for achieving the association, you won’t getting a proper result if you aren’t use the @JoinColumn. See the following example (OneToOne Example) that shown you an Employee entity whose address property establish an OneToOne association and let’s assume that we are missed the @JoinColumn. Surely you’ve got such that exception:

Exception in thread "main" javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'ADDRESS_ADDRESSID' in 'field list'
Error Code: 1054
Call: INSERT INTO EMPLOYEE (EMPLOYEEID, EMPLOYEENAME, ADDRESS_ADDRESSID) VALUES (?, ?, ?)
 bind => [1, John Smith, 1]
Query: InsertObjectQuery(net.javabeat.eclipselink.data.Employee@5470be88)
 at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:157)
 at net.javabeat.eclipselink.JPAImpl.main(JPAImpl.java:22)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'ADDRESS_ADDRESSID' in 'field list'
Error Code: 1054
Call: INSERT INTO EMPLOYEE (EMPLOYEEID, EMPLOYEENAME, ADDRESS_ADDRESSID) VALUES (?, ?, ?)
 bind => [1, John Smith, 1]
<span style="font-size: 12px; line-height: 18px;"> at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:180)</span>
 at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:489)
 at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
 at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
 at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:301)
 at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
 at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
 at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:798)
 at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
<span style="font-size: 12px; line-height: 18px;"> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)</span>
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
 at java.lang.reflect.Constructor.newInstance(Unknown Source)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
 at com.mysql.jdbc.Util.getInstance(Util.java:386)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)

As you have noticed by omitting the @JoinColumn the JPA implementation assumes that there is an additional attribute called ADDRESS_ADDRESSID, although it’s not intended at all. The @JoinColumn help the JPA implementation to notice that it has an association that should be considered.