Change where clause for batch delete

user_5743485 0
Viewed 54

I want to like to create a batch delete something like: DELETE t WHERE t.my_attribute = ?

First try was:

   private void deleteRecord( ) {
      //loop
      final MyRecord myRecord = new MyRecord();
      myRecord.setMyAttribute(1234);
      getDslContext().batchDelete(myRecord) .execute();
   }

But here the SQL contains always the pk instead of my attribute.

Second try was to create a delete statement with a bind value, but here i found no solution how i can create a where clause with ?

  //loop
  getDslContext().delete( MY_RECORD ).where( ???)
        .bind( 12234 );

Can anybody help me further?

jooq sql java
1 Answers

The DELETE statement itself

Just add your comparison predicate as you would in SQL:

getDslContext()
    .delete(T)
    .where(T.MY_ATTRIBUTE.eq(12234))
    .execute();

This is assuming you are using the code generator, so you can static import your com.example.generated.Tables.T table reference.

Batching that

You have two options of batching such statements in jOOQ:

1. Using the explicit batch API

As explained here, create a query with a dummy bind value as I've shown above, but don't execute it directly, use the Batch API instead:

// Assuming these are your input attributes
List<Integer> attributes = ...

Query query = getDslContext().delete(T).where(T.MY_ATTRIBUTE.eq(0));
getDSLContext()
    .batch(query)
    .bind(attributes
        .stream().map(a -> new Object[] { a }).toArray(Object[][]::new)
    ).execute();

2. Collect individual executions in a batched connection

You can always use the convenient batched collection in jOOQ to transparently collect executed SQL and delay it into a batch:

getDslContext().batched(c -> {
    for (Integer attribute : attributes)
        c.dsl().getDslContext()
               .delete(T)
               .where(T.MY_ATTRIBUTE.eq(attribute)
               .execute(); // Doesn't execute the query yet
}); // Now the entire batch is executed

In the latter case, the SQL string might be re-generated for every single execution, so the former is probably better for simple batches.

Bulk execution

However, why batch when you can run a single query? Just do this, perhaps?

getDslContext()
    .delete(T)
    .where(T.MY_ATTRIBUTE.in(attributes))
    .execute();
user_5217990
Related