So let's say your database has a simple parent-child relationship such as the relationship between a user and their addresses. A single user can have many addresses so it is a one-to-many relationship from the point of view of the user; a many-to-one relationship from the point of view of the address. You model this with JPA entities as follows:
Listing 1: User entity with collection of Address
@Entity
@Table(name = "app_user")
public class User implements Serializable {
@EmbeddedId
UserKey key;
@OneToMany(mappedBy="user", fetch=FetchType.EAGER, cascade=CascadeType.ALL)
Collection<Address> addresses;
}
In listing 1, you can see the @OneToMany annotation is used in the User class (#2) because from the point of view of a user, a user can have multiple addresses . Recall this annotation is used on the owner of the relationship.
Listing 2: Address entity with parent User
@Entity
@Table(name = "app_address")
public class Address implements Serializable {
@EmbeddedId
AddressKey key;
@ManyToOne
private User user;
}
In listing 2, the Address entity uses the @ManyToOne annotation (#2) because from the address point of view, an address can only have one user. That's it, real simple JPA config.
So after coding this up, you start your integration tests. A test attempts to select data from the database and build a User/Address object tree. Immediately you run into a problem. The SQL generated is not valid and you get a SQLGrammarException complaining about some column which does not exist. listing 3 shows some of what the stack trace may say:
Listing 3: SQLGrammarException stack trace
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not load an entity: .
.
Caused by: org.hibernate.exception.SQLGrammarException: could not
load an entity: [org.ferris.resolving.sqlgrammarexception.User#
.
.
Caused by: java.sql.SQLSyntaxErrorException: Column
'ADDRESSES1_.USER_BIRTHDAY' is either not in any table in the
FROM list or appears within a join specification and is outside
the scope of the join specification or appears in a HAVING clause
and is not in the GROUP BY list.
You look at this stack trace, you see Column 'ADDRESSES1_.USER_BIRTHDAY', and the $10,000 question is where is JPA getting this column? It doesn't exist in your database and none of your entities reference a column with this name. You wired up your JPA entities with annotations in the exact way the book tells you to, so everything should work. Isn't that right? The purpose of this article is to explore why you get this SQLGrammarException and how to resolve it.
System Requirements
The code was developed and run using the following system setup. If yours is different, it may work but no guarantees.
- JDK 1.7.0_17
- NetBeans 7.3.1
- Maven 3.0.5 (bundled with NetBeans)
Download Project
If you just want to look at the project demonstrating the solution, download the code example from GitHub (https://github.com/mjremijan/JPATest).
Run Project
The project is a standard Maven setup. Simply execute mvn. The project contains Maven integration tests which are NOT run during the unit testing phase of the Maven lifecycle. So executing mvn test won't run the example.
The code is configure so the integration tests will pass and you will not get the SQLGrammerException. You can mess around with the JPA annotations on your own an explore all the wonderful exceptions you can get.
Why the SQLGrammerException ?
To understand why you may be getting an SQLGrammarException, you really need to understand the difference between artificial and natural primary and foreign keys for database tables. It's in the keys that the problem lies. So let's take a look at these keys.
When studying JPA, you learn the @ManyToOne and @OneToMany annotations can be used to establish a bi-directional relationship between entities. Typically, examples demonstrating how to code this relationship with JPA use very simple artificial primary and foreign keys. As a reminder, an artificial primary key is a column added to a database table which only exists to make rows in the table unique and as such is completely independent of the data stored in the table. Artificial keys are typically Java long primitive values. Each table in the relationship typically has its own artificial key. An artificial key relationship is shown in figure 1.
Figure 1: An artificial key database relationship
Figure 2: A natural key database relationship
Now that you got a quick review of artificial and natural keys, let's take a look at how this is contributing to our problem. First look at the error message again.
Column 'ADDRESSES1_.USER_BIRTHDAY' is either not in any table. . .
ADDRESSES1 indicates the problem is with the Address entity. So that makes sense. Now referring back to listing 2, the Address entity has a relationship to its user by embedding the User entity like so:
@ManyToOne
private User user;
So the USER part of the error now kinda makes sense, but now what about BIRTHDAY? Well refer back to listing 1 and take a look at the properties of the User entity. It has an embedded UserKey as its primary key. Listing 4 shows UserKey.
Listing 4: UserKey is the primary key of the User entity
@Embeddable
public class UserKey implements Serializable
{
private static final long serialVersionUID = 131374817387370831L;
@Column(name = "last_name")
private String lastName;
@Column(name = "zip_code")
private Long zipCode;
@Column(name = "birthday")
private Timestamp birthday;
// getters and setters omitted for brevity
There is is! Inside UserKey is the missing BIRTHDAY. So now that it looks like we've identified all of the parts of the exception, let's walk though what's going on.
Address is an entity managed by JPA. As such, JPA scans through all the properties of Address looking for non-@Transient properties to manage. It finds the private User user property. Since User it is an object and not a simple primative, JPA uses make an intelligent guess about the column name for this property and assumes it starts with "user". Then JPA scans the User entity. In it, JPA finds @EmbeddedId UserKey userKey. Because UserKey is an embedded object, JPA now scans it for fields to manage and finds birthday. Putting this all together in hibernate-generated SQL you get ADDRESSES1_.USER_BIRTHDAY. Not very pretty, so what do we do about it? We the solution is actually very simple.
Solution
To solve this problem, you need to configure JPA with the relationship between the entities. Recall the relationship between the user and address data is a complicated natural foreign key relationship. Figure 2 shows the primary key of App_User is a combination of LAST_NAME, ZIP, and BIRTHDAY. For App_Address, these three columns are repeated with an addition of a fourth column to make the combination of LAST_NAME, ZIP, BIRTHDAY, and ADDRESS the primary key for App_Address. App_Address also uses its LAST_NAME, ZIP, and BIRTHDAY columns as a foreign key back to App_User. Though this may sound complicated, JPA annotations make it very easy. Listing 5 shows how to define the relationship in the Address entity.
Listing 5: Address relationship with its user
@ManyToOne(optional=false, fetch=FetchType.EAGER, cascade=CascadeType.ALL)
@JoinColumns({
@JoinColumn(name="last_name", referencedColumnName="last_name", insertable=false, updatable=false),
@JoinColumn(name="zip_code", referencedColumnName="zip_code", insertable=false, updatable=false),
@JoinColumn(name="birthday", referencedColumnName="birthday", insertable=false, updatable=false)
})
private User user;
In listing 5 @JoinColumn has been introduced (#3). The name property refers to the name of the column in the App_Address table. The referencedColumnName propery refers to the name of the column in the App_User table. The insertable and updatable properties are self explanatory but critically important so JPA does not duplicate the columns when generating the INSERT and UPDATE statements. And that's it, very simple.
