Insert/merge multiple rows using an unchanging id and a list of ids

[Beldrew]

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).

[friso.v...@gmail.com]

How about 

INSERT INTO user_permissions(...)

SELECT ...

FROM PERMISSION_TABLE

WHERE ....

?

[Lukas Eder]

I'd definitely also go Friso's way of moving the loop back into the database by writing a single SQL statement. However, from your description, that might not be so easy, as there are also deletions involved, and MySQL doesn't support the MERGE statement, which seems to be the perfect fit for this.

Currently, jOOQ doesn't have any convenience API to run dynamic multi-row inserts easily the way you intended. The relevant feature request is here:

https://github.com/jOOQ/jOOQ/issues/6604

You can, however, easily map-reduce your collection into an insert statement as follows:

permissions

  .stream()

  .collect(Collector.of(

    () -> DSL.using(transaction)
             .insertInto(USER_PERMISSION_TABLE)
             .columns(USER_PERMISSION_TABLE.USER_ID, USER_PERMISSION_TABLE.PERMISSION_ID),

    (q, p) -> q.values(user.getId(), p),

    (q1, q2) -> null, // No parallel streams supported

  ))

  .execute();

Hope this helps,

Lukas

--
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 jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.