JPA Many to Many Relationship Creates Two Join Tables

Issue

I am trying to create a many-to-many relationship between User and FileObject classes with the assumption user can access many file objects and file object can be accessed by many user and one-to-many relationship as one user can own many files but one file can only be owned by one user. Here is my code:

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    public int id;
    public String firstname;
    public String lastname;
    public String publicAttributes;
    public String privateAttributes;

    @ManyToOne
    private Department department;

    @OneToMany(mappedBy = "user")
    public List<Device> devices = new ArrayList<Device>();

    @OneToMany(mappedBy = "userCreator")
    public List <FileObject> fileOwned = new ArrayList <FileObject>();

    @ManyToMany
    @JoinTable(name="USER_FILE_ACCESS")
    public List<FileObject> fileHasAccess = new ArrayList<FileObject>();
}

@Entity
public class FileObject {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    public int id;
    String checksum;

    @OneToMany(mappedBy = "fileObject")
    public List<Policy> policies = new ArrayList<Policy>();

    byte[] encryptedFileKey;

    byte[] iv;

    @ManyToOne
    public User userCreator;

    @ManyToMany
    public List<User> listUserAccessor = new ArrayList<User>();
}

In theory it should only create one join table namely USER_FILE_ACCESS but when I ran the script then it creates two join tables and I am not sure why.

Here are the excerpt of the SQL commands:

show tables;
+------------------+
| Tables_in_cpabe  |
+------------------+
| DEPARTMENT       |
| DEVICE           |
| FILEOBJECT       |
| FILEOBJECT_USER  |
| POLICY           |
| SEQUENCE         |
| USER             |
| USER_FILE_ACCESS |
+------------------+

show columns from FILEOBJECT_USER;
+---------------------+---------+------+-----+---------+-------+
| Field               | Type    | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| FileObject_ID       | int(11) | NO   | PRI | NULL    |       |
| listUserAccessor_ID | int(11) | NO   | PRI | NULL    |       |
+---------------------+---------+------+-----+---------+-------+

show columns from USER_FILE_ACCESS;
+------------------+---------+------+-----+---------+-------+
| Field            | Type    | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| User_ID          | int(11) | NO   | PRI | NULL    |       |
| fileHasAccess_ID | int(11) | NO   | PRI | NULL    |       |
+------------------+---------+------+-----+---------+-------+

How do I make join table to be only one table?

Solution

If you want to have a bi-directional relationship then:

@Entity
public class User {

@ManyToMany
@JoinTable(name="USER_FILE_ACCESS"
     [email protected](name="user_id"),
        [email protected](name="fileHasAccess_ID "))
public Set<FileObject> fileHasAccess;


@Entity
public class FileObject {

@ManyToMany
@JoinTable(name="USER_FILE_ACCESS"
         [email protected](name="fileHasAccess_ID"),
            [email protected](name="user_id"))
    public Set<User> listUserAccessor;

Update

Try to use SEt instead of a List.

And you can also try another bi-directional option. So on the referencing or inverse side (non-owning side), you would map as follows:

@ManyToMany(mappedBy = "fileHasAccess")
public Set<User> listUserAccessor;

Also, double-check the column names .. I may have put something different then what you actually have in the DB.

Answered By – Maciej Kowalski

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published