batchdelete on a junction table

[anuj...@gmail.com]

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.

[Lukas Eder]

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

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

[anuj...@gmail.com]

Some what related to batchUpdate() and optimistic locking. When I try to do a batch update on a stale record it fails and does not throw any kind of exception. For example:

RoleRecord roleRecord = new RoleRecord();
roleRecord.setName("role"); // name is the PK for this table

roleRecord.attach(configuration);
roleRecord.insert();

assertThat(roleRecord.getVersion()).isEqualTo(1);
assertThat(roleRecord.getDescription()).isNull();

roleRecord.setDescription("desc1");
DSL.using(configuration).batchUpdate(roleRecord).execute(); // this works fine as we have the most current roleRecord 

RoleRecord fromDB = DSL.using(configuration).selectFrom(ROLE).where(ROLE.NAME.eq("role")).fetchOne();

assertThat(fromDB.getDescription()).isEqualTo("desc1");
assertThat(fromDB.getVersion()).isEqualTo(2);

roleRecord.setDescription("desc2");
DSL.using(configuration).batchUpdate(roleRecord).execute(); // does not throw DataChangedException, we have a stale roleRecord here
//roleRecord.update(); // throws a DataChangedException which is the correct behaviour

fromDB = DSL.using(configuration).selectFrom(ROLE).where(ROLE.NAME.eq("role")).fetchOne();

assertThat(fromDB.getVersion()).isEqualTo(3); // fails
assertThat(fromDB.getDescription()).isEqualTo("desc2"); // fails

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[anuj...@gmail.com]

any insight on this would be greatly appreciated

[Lukas Eder]

I'm sorry for the delay here.

Yes indeed, as I've mentioned in a previous E-Mail, jOOQ currently has a limitation where the combination of the optimistic locking feature and batching is not possible:

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

Hope this helps,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.