Hibernate Spring: @ManyToMany DataIntegrityViolationException ConstraintViolationException

I am building a sample for ManyToMany relationship between: User(1) - ()AccessLevel() - (1)Role

I have implemented 3 classes in Java with hibernate implementation as follow:

Class User

@Entity
@Table(name="user")
public class User {
    @Id
    @GeneratedValue
    @Column(name="USER_ID")

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(name = "access_level", joinColumns = { 
            @JoinColumn(name = "user_id", nullable = false, updatable = false) }, 
            inverseJoinColumns = { @JoinColumn(name = "role_id", nullable = false, updatable = false) })
    private Set<Role> roles = new HashSet<Role>(0);

Class Role

@Entity
@Table(name="role")
public class Role {

    @Id
    @GeneratedValue
    @Column(name="role_id")
    private Integer id;

    @Column(name="role_name")
    private String roleName;

Class AccessLevel

@Entity
@Table(name="access_level")
public class AccessLevel {

    @Id
    @GeneratedValue
    private Integer id;
    @Column(name="role_id")
    private Integer  roleId;
    @Column(name="user_id")
    private Integer  userId;

Problem:

When I am persisting the User bean using merge method then an exception arise:

@Service
public class UserServiceImpl implements UserService {

    @PersistenceContext
    private EntityManager em;

    @Override
    @Transactional
    public void save(User user) {
        em.merge(user);
    }

Exception

org.springframework.web.util.NestedServletException: Request process

ing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; SQL [insert into access_level (user_id, role_id) values (?, ?)]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

As you can see hibernate is trying to run this query:

insert into access_level (user_id, role_id) values (?, ?)

From my point of view it seems like hibernate is not generating the primary key for AccessLevel even though I have added the @GeneratedValue to the id attribute.

Note: I am working on production environment with Postgresql and evelopment environment with HSQL database that creates all schemas from the begining based on the entities description. Both environments generate same issue.

Regards, Cristian Colorado

Reason:

It seems for ManyToMany relationships you do not need to define a class for the "Joining Table". Therefore if I eliminate AccessLevel entity the logic would work perfectly fine. I explain further:

Explanation:

When I defined the User class I also described the relationship with Role:

@ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(name = "access_level", joinColumns = { 
            @JoinColumn(name = "user_id", nullable = false, updatable = false) }, 
            inverseJoinColumns = { @JoinColumn(name = "role_id", nullable = false, updatable = false) })
    private Set<Role> roles = new HashSet<Role>(0);

Important thing here is I have told hibernate that User entity will relate to Role entity through a table known as "access_level" and such table will have user_id and role_id columns in order to join previous entities.

So far this is all hibernate needs in order to work the many to many relationship, therefore when mergin it uses that information to create and tun this script:

insert into access_level (user_id, role_id) values (?, ?)

Now, the problem cames when I defined a new entity for AccessLevel:

@Entity
    @Table(name="access_level")
    public class AccessLevel {

        @Id
        @GeneratedValue
        private Integer id;
        @Column(name="role_id")
        private Integer  roleId;
        @Column(name="user_id")
        private Integer  userId;

Now I am telling hibernate that there is a table "access_level" related to AccessLevel entity and it has 3 columns, the most important would be Id which is primary key.

So I defined "access_level" twice!

Solution:

• I eliminated the Entity for access_level table.
• I re-write my production script in order to have "access_level" with user_id/role_id columns only.

Note: It would be good to know how to add a primary key to the joining table without generating issues. An alternative would be adding a composed primary key in database(user_id/role_id) which would be independient from hibernate.