6 Replies Latest reply on Sep 1, 2005 12:21 PM by epbernard

    Many-toMany, Mysql trying to remove record

      I have a table with a couple of many to many relationship set up:

      @ManyToMany(targetEntity = Role.class)
      @JoinTable(table = @Table(name = "GROUP_ROLE"), joinColumns = @JoinColumn(name = "GROUP_ID", referencedColumnName = "ID"), inverseJoinColumns = @JoinColumn(name = "ROLE_ID", referencedColumnName = "ID"))
      public List getRoles() {
      return roles;
      }
      @ManyToMany(targetEntity = User.class, mappedBy = "groups", cascade = {CascadeType.PERSIST, CascadeType.REFRESH, CascadeType.MERGE})
      public List getUsers() {
      return users;
      }


      when i try to remove the record, I am expecting it to remove the link record as well, which is enforced on the database. but instead i get an error.

      15:35:55,187 WARN [JDBCExceptionReporter] SQL Error: 1217, SQLState: 23000
      15:35:55,187 ERROR [JDBCExceptionReporter] Cannot delete or update a parent row: a foreign key constraint fails
      15:35:55,187 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
      org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update


      Can anyone please tell me what i am doing wrong. I would prefer not to take the constraints off the database as there is then the potential for data integrity issues.

        • 1. Re: Many-toMany, Mysql trying to remove record

          I just removed the constriants from the database, to find that the link tables records still exist after the deletion.

          • 2. Re: Many-toMany, Mysql trying to remove record

            I forgot to put in that all the remove code is doing is this:

            Group group = em.find(Group.class, id);
            em.remove(group);


            I even tried:


            Group group = em.find(Group.class, id);
            List users = group.getUsers();
            List roles = group.getRoles();
            for (User u:users) {
            u.getRoles().remove(group);
            }
            group.setUsers(new ArrayList());
            for (Role r:roles) {
            r.getUsers().remove(group);
            }
            group.setRoles(new ArrayList());
            em.flush();
            em.remove(group);


            but it came up with an error about trying to persist a record that has been deleted.

            • 3. Re: Many-toMany, Mysql trying to remove record

              Try that again
              I forgot to put in that all the remove code is doing is this:

              Group group = em.find(Group.class, id);
              em.remove(group);

              I even tried:
              Group group = em.find(Group.class, id);
              List users = group.getUsers();
              List roles = group.getRoles();
              for (User u:users) {
               u.getRoles().remove(group);
              }
              group.setUsers(new ArrayList());
              for (Role r:roles) {
               r.getUsers().remove(group);
              }
              group.setRoles(new ArrayList());
              em.flush();
              em.remove(group);


              but it came up with an error about trying to persist a record that has been deleted.

              • 4. Re: Many-toMany, Mysql trying to remove record
                epbernard

                the mappedBy side is not responsible for managing the fk updates. you have to remove the groups from the suer side, or move the mapped by to the other side

                • 5. Re: Many-toMany, Mysql trying to remove record

                  ok, but i have the problem that i want to be able to call the same functionality from either side.

                  I have got around the problem by putting in 2 calls to the session bean, 1 that removes all the relation ships, then a second that deletes the entry. This works ok, but is not the best way to do it

                  The reason for doing it in 2 calls is because when doing it in a single call hibernate attempts to remove the record before the relationships are removed.

                  • 6. Re: Many-toMany, Mysql trying to remove record
                    epbernard

                    add a method on your entities (addEleemntToCollection() and removeElementToCollection() that will keep both side in sync.