pratik pratik - 18 days ago 5
MySQL Question

JPA with EclipseLink ManyToMany relationship not working

I'm trying to set up an application for like a Phone directory where each user can have multiple phones and each phone can be assigned to multiple users. I am using JPA with EclipseLink to make this work. And while retrieving one of the tables, JPA is throwing an exception.
Details are as follows -


Exception Trace -

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't0.users_ID' in 'where clause'
Error Code: 1054
Call: SELECT t1.ID, t1.FIRSTNAME, t1.LASTNAME, t1.TITLE, t1.CITY, t1.STATE, t1.STREET, t1.ZIP FROM Phone_User t0, User t1 WHERE ((t0.Phone_ID = ?) AND (t1.ID = t0.users_ID))
bind => [1 parameter bound]
Query: ReadAllQuery(name="users" referenceClass=User sql="SELECT t1.ID, t1.FIRSTNAME, t1.LASTNAME, t1.TITLE, t1.CITY, t1.STATE, t1.STREET, t1.ZIP FROM Phone_User t0, User t1 WHERE ((t0.Phone_ID = ?) AND (t1.ID = t0.users_ID))") (through reference chain: edu.lab2.beans.Phone["users"]);


User.java

@Entity
@Table(name = "User")
public class User {

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;

private String firstname;
private String lastname;
private String title;

@Embedded
private Address address;

@ManyToMany
@JoinTable(name = "Phone_User", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "phone_id", referencedColumnName = "id"))
private List<Phone> phones;


Phone.java

@Entity
@Table(name = "Phone")
public class Phone {

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
private String number; // Note, phone numbers must be unique
private String description;

@Embedded
private Address address;

private List<User> users;

@ManyToMany(mappedBy="phones")
public List<User> getUsers() {
return users;
}


Tables -

mysql> DESC User;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| firstname | varchar(40) | YES | | NULL | |
| lastname | varchar(40) | YES | | NULL | |
| title | varchar(10) | YES | | NULL | |
| street | varchar(40) | YES | | NULL | |
| city | varchar(40) | YES | | NULL | |
| state | varchar(40) | YES | | NULL | |
| zip | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
| state | varchar(40) | YES | | NULL | |
8 rows in set (0.03 sec)

mysql> DESC Phone;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| number | varchar(20) | YES | UNI | NULL | |
| description | varchar(20) | YES | | NULL | |
| street | varchar(40) | YES | | NULL | |
| city | varchar(40) | YES | | NULL | |
| state | varchar(40) | YES | | NULL | |
| zip | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> DESC Phone_User;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| user_id | int(11) | NO | PRI | NULL | |
| phone_id | int(11) | NO | PRI | NULL | |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)


I am confused why EclipseLink or JPA is searching for
users_id
in the Join Table?

Answer

The Phone entity is mixing field and property access types (see JPA 2.1 Specs, ch. 2.3). Try placing the @ManyToMany(mappedBy="phones") annotation on the private List<User> users field.

If you really need to split annotations between fields and properties (read: getters), you can annotate the specific property with @Access(AccessType.PROPERTY), i.e. in this case:

@Access(AccessType.PROPERTY)
@ManyToMany(mappedBy="phones")
public List<User> getUsers() {
    return users;
}