Thank you very much for reporting. Yes indeed, currently, optimistic locking doesn't work well with batchStore(), batchUpdate(), and batchDelete(). There's an issue for this limitation: https://github.com/jOOQ/jOOQ/issues/1637
Probably, however, you meant to do bulk deletion, not batch deletion. That would be done much more simply by running an ordinary DELETE statement: DSL.using(configuration) .deleteFrom(BOOK_AUTHOR) .where(BOOK_ID.eq(1)) .and(AUTHOR_ID.eq(1)) .execute(); Another option is to turn off Settings.executeWithOptimisticLocking for this particular batch call, or Settings.executeWithOptimisticLockingExcludeUnversioned for all of your configurations (this turns off optimistic locking for tables that don't have a version column), but again, I don't think you actually want to batch this call. Hope this helps, Lukas 2017-03-09 8:09 GMT+01:00 <[email protected]>: > I have a many-to-many relationship with book, book_author and author > tables. The book and author table have version column and I have configured > the optimistic locking option to true. The book_author table just has the > book_id and author_id fields. On executing the below statement > > BookAuthorRecord bookAuthorRecord = new BookAuthorRecord(1,1); > DSL.using(configuration).batchDelete(bookAuthorRecord).execute(); > > > I get the below error: > > Exception in thread "main" org.jooq.exception.DataAccessException: SQL > [select `book_author`.`book_id`, `book_author`.`author_id` from `book_author` > where (`book_author`.`book_id` = ? and `book_author`.`author_id` = ?) for > update]; Can not issue executeUpdate() or executeLargeUpdate() for SELECTs > at org.jooq.impl.Tools.translate(Tools.java:1941) > at > org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:659) > at org.jooq.impl.BatchSingle.executePrepared(BatchSingle.java:259) > at org.jooq.impl.BatchSingle.execute(BatchSingle.java:182) > at org.jooq.impl.BatchCRUD.executePrepared(BatchCRUD.java:159) > at org.jooq.impl.BatchCRUD.execute(BatchCRUD.java:100) > at com.jooq.JooqSampleTest.main(JooqSampleTest.java:37) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) > Caused by: java.sql.BatchUpdateException: Can not issue executeUpdate() or > executeLargeUpdate() for SELECTs > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.newInstance(Constructor.java:423) > at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) > at com.mysql.jdbc.Util.getInstance(Util.java:387) > at > com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1161) > at > com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773) > at > com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257) > at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:958) > at > org.jooq.tools.jdbc.DefaultStatement.executeBatch(DefaultStatement.java:93) > at org.jooq.impl.BatchSingle.executePrepared(BatchSingle.java:239) > ... 9 more > Caused by: java.sql.SQLException: Can not issue executeUpdate() or > executeLargeUpdate() for SELECTs > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) > at > com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2045) > at > com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751) > ... 13 more > > > But if I try to delete on the record, it works fine, so the below works: > > BookAuthorRecord bookAuthorRecord = new BookAuthorRecord(1,1); > bookAuthorRecord.attach(configuration); > bookAuthorRecord.refresh(); > bookAuthorRecord.delete(); > > > I would like to know how I can do bulk deletes for junction table which does > not have the version column and I have configured optimistic > > locking to true. > > > -- > 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. > -- 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.
