So I have a fairly standard user -> user_permissions -> permissions setup. I'm trying to figure out the best way to handle updates to the user's permissions, where permissions might of been deleted, added, or left alone. I'm new to JOOQ so just trying to figure out the JOOQ way if you will.
Note: This is using mysql 5.7. First i'm grabbing the list of permissions ids from the db. Yes I plan to change this final Set<Long> permissions = DSL.using( transaction ).select().from( PERMISSION_TABLE ).where( PERMISSION_TABLE.PERMISSION.in( user. getPermissions().stream().map( Permission::getPermission ).collect( Collectors.toSet()) ) ).fetch().stream().map( p->p.get(PERMISSION_TABLE. PERMISSION_ID) ).collect(Collectors.toSet()); Then I delete any permission ids from the many to many map table that arn't on the current user. DSL.using( transaction ).delete(USER_PERMISSION_TABLE).where( USER_PERMISSION_TABLE.PERMISSION_ID.notIn( permissions ).and( USER_PERMISSION_TABLE.USER_ID.eq( user.getId() ) ) ); Here's where I'm stuck. I was hoping I could do: DSL.using( transaction ).insertInto( USER_PERMISSION_TABLE ).columns( USER_PERMISSION_TABLE.USER_ID, USER_PERMISSION_TABLE.PERMISSION_ID ).values( user.getId(), permissions ).execute() To create something like: INSERT INTO user_permissions(user_id, permission_id) values (user id, permission id from [0]) (user id, permission id from [1]) (user id, permission id from [2]) (user id, permission id from [N]) Is there a way to do this? I was trying to avoid creating updatable records in a loop and using batch.store(records). -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
