JPA Many to Many Relationship Creates Two Join Tables

Question

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?

your 2nd @ManyToMany has no join table/mapped by. maybe have a look at it — XtremeBaumer, Feb 06 '17 at 14:12
@XtremeBaumer I saw in some examples available for the other side of joined table I don't need that annotation CMIIW — Ihsan Haikal, Feb 06 '17 at 14:14

score 6 · Accepted Answer · edited May 09 '22 at 03:08

6

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

@Entity
public class User {

@ManyToMany
@JoinTable(name="USER_FILE_ACCESS"
     joinColumns=@JoinColumn(name="user_id"),
        inverseJoinColumns=@JoinColumn(name="fileHasAccess_ID "))
public Set<FileObject> fileHasAccess;


@Entity
public class FileObject {

@ManyToMany
@JoinTable(name="USER_FILE_ACCESS"
         joinColumns=@JoinColumn(name="fileHasAccess_ID"),
            inverseJoinColumns=@JoinColumn(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.

edited May 09 '22 at 03:08

Yassine Addi

343
2
10

answered Feb 06 '17 at 14:16

Maciej Kowalski

25,605
12
54
63

it still doesn't work and instead gave some error of unknown tables and also join table does not exist – Ihsan Haikal Feb 06 '17 at 14:25
1

specifying mappedBy makes it bidirectional, not unidirectional. If you want it to use the mapping specified on the 'other' side rather than duplicate it, you add mappedBy – Chris Feb 06 '17 at 15:06
Correct its an example of another way of declaring it as bi-directional.. i think i need a break today.. – Maciej Kowalski Feb 06 '17 at 15:13
Using Set instead a List is for performance reason. "mappedBy" is key to generate one technical table between. Both sides of bidirectional are not totally symmetric, one is master – Jacek Cz Feb 07 '17 at 08:51
@MaciejKowalski why use set instead of list? anyway thx for the answer – Ihsan Haikal Feb 07 '17 at 13:59
All the entities should be unique (usually by id). If you use Set and override equals() and hashCode() for each of you entities,.. you are safeguarding your application from some possible duplicates out of the box. – Maciej Kowalski Feb 07 '17 at 14:10

JPA Many to Many Relationship Creates Two Join Tables

1 Answers1

Linked