This discussion is archived
14 Replies Latest reply: Mar 28, 2013 9:00 PM by rp0428 RSS

Update one to many Relation

999729 Newbie
Currently Being Moderated
Hi,

I have 2 classes java User and Role, the User class contains a

list of Role:

class User{
private long id;
private List<Role> roles;
...
}

class Role{
private long id;
private String roleName;
..
}
i have created 3 tables user, role and user_role with id_user and id_role column

now i want to update a user object by removing some roles, should i delete all rows in role_user an role table and insert the new

data or there are something notify tables that java data has changed
  • 1. Re: Update one to many Relation
    gimbal2 Guru
    Currently Being Moderated
    996726 wrote:
    now i want to update a user object by removing some roles, should i delete all rows in role_user an role table and insert the new
    I don't know if you should do that, but I certainly do that. Its a nice and dumb way of getting what you want.
  • 2. Re: Update one to many Relation
    999729 Newbie
    Currently Being Moderated
    Thank for your replay,

    but i find that is not a suitable method to delete all rows from role_user and role tables

    and insert the new update User object, i wonder if there is a method that simply delete

    only the row that i have removed from Role's list
  • 3. Re: Update one to many Relation
    gimbal2 Guru
    Currently Being Moderated
    Its very, very basic SQL. There is no "method" to call. Learn SQL and learn the basic JDBC API and you'll know what to do.

    Unless you're actually using something else than JDBC?
  • 4. Re: Update one to many Relation
    Tolls Journeyer
    Currently Being Moderated
    I'm a bit confused.
    Are you removing a Role from a user (ie removing an entry in the user_role table) or are you removing a Role (ie an entry in the Role table) and therefore want that reflected in the user_role table?
  • 5. Re: Update one to many Relation
    999729 Newbie
    Currently Being Moderated
    Tolls:
    ie : i have a user entry in user table with 3 roles , so 3 entries in role table and 3 entries in role_user table

    now i map these entries into one java object User with List of role and i want to remove one role from the list

    and update the database, i wonder if i should delete all entries in role and role_user table and insert the new

    2 entries or there is another method by which i can remove only one entry
  • 6. Re: Update one to many Relation
    999729 Newbie
    Currently Being Moderated
    Thank you gimbal2, i'll try to read about jdbc api and sql
  • 7. Re: Update one to many Relation
    rp0428 Guru
    Currently Being Moderated
    >
    I have 2 classes java User and Role, the User class contains a
    . . .
    i have created 3 tables user, role and user_role with id_user and id_role column

    now i want to update a user object by removing some roles, should i delete all rows in role_user an role table and insert the new

    data or there are something notify tables that java data has changed
    >
    No - the database does not know, and does not care, if you Java data has changed. If you are removing a role from the database tables you have no new data to insert and there is no need to remove ALL of the data; just remove the data for the role you are removing.

    To completely remove a ROLE (i.e. the role no longer exists):

    1. delete ALL rows from the USER_ROLE table for that role and then delete that role from the ROLE table. Don't name a table 'ROLE'; that will likely be a reserved word in some databases.
    DELETE FROM MY_USER_ROLE_TABLE WHERE id_role = ?; -- replace the question mark with the id of the role
    
    DELETE FROM MY_ROLE_TABLE WHERE id_role = ?;
  • 8. Re: Update one to many Relation
    gimbal2 Guru
    Currently Being Moderated
    996726 wrote:
    Thank you gimbal2, i'll try to read about jdbc api and sql
    You're welcome, but after rp2048's response I do realize I misread your question a little bit. I would only do the 'remove all' trick in the user_role table. I was assuming you were talking about easily updating which roles a specific user has. You can't just delete roles themselves because they will likely still be in use by other users; referential integrity comes into play there.
  • 9. Re: Update one to many Relation
    Tolls Journeyer
    Currently Being Moderated
    996726 wrote:
    Tolls:
    ie : i have a user entry in user table with 3 roles , so 3 entries in role table and 3 entries in role_user table

    now i map these entries into one java object User with List of role and i want to remove one role from the list

    and update the database, i wonder if i should delete all entries in role and role_user table and insert the new

    2 entries or there is another method by which i can remove only one entry
    OK.
    So you're removing a Role in its entirety, not just a Role from a User.

    See above then...though in this case I would go for a ON DELETE CASCADE, but that's just me.
  • 10. Re: Update one to many Relation
    gimbal2 Guru
    Currently Being Moderated
    Tolls wrote:
    See above then...though in this case I would go for a ON DELETE CASCADE, but that's just me.
    Ouch, cascaded deletes are like grenades dude ;)
  • 11. Re: Update one to many Relation
    Tolls Journeyer
    Currently Being Moderated
    gimbal2 wrote:
    Tolls wrote:
    See above then...though in this case I would go for a ON DELETE CASCADE, but that's just me.
    Ouch, cascaded deletes are like grenades dude ;)
    Nah.
    People are simply too afraid to use them. It's a bit like the abuse of outer joins "in case I miss something".
    Honestly.
    It's a simple relationship, there should not be any user_role for roles that don't exist, so why not use the in built mechanism for dealing with that?

    Now, if there's more to this than the problem as given then all bets are off...:)
  • 12. Re: Update one to many Relation
    939520 Explorer
    Currently Being Moderated
    Before you write any Java code, perhaps you could post your database schema so we can see what your database tables look like. It looks like you only need a simple parent/child relationship between user and role. Looks like you may be unnecessarily setting up a many to many relationship instead ("I have created 3 tables user, role and user_role" ).
  • 13. Re: Update one to many Relation
    gimbal2 Guru
    Currently Being Moderated
    Tolls wrote:
    People are simply too afraid to use them. It's a bit like the abuse of outer joins "in case I miss something".
    Agreed, I use them when they make sense (more in a JPA/ORM environment though). But you'd have to take a gander in JPA forums to see people posting their models with cascades on EVERYTHING... copy/paste, copy/paste, copy/paste, I don't know what it implies but copy/paste...
  • 14. Re: Update one to many Relation
    rp0428 Guru
    Currently Being Moderated
    >
    It looks like you only need a simple parent/child relationship between user and role. Looks like you may be unnecessarily setting up a many to many relationship instead ("I have created 3 tables user, role and user_role" ).
    >
    Except that OP HAS a many-to-many model.

    One user can be associated with MANY roles. And one role can be associated with MANY users. That's the classic definition of many-to-many.

    You can't use parent/chile for user/role since a role can be associated with many users.

    The user_role table is often called an INTERSECT table since it contains the intersection of two attributes.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points