jOOQ MySQL how to determine if complex DELETE affected row(s)

Question

I'm trying to determine the result of the following method, and ideally return the # of rows affected as an Int:

/**
 * Delete the Account ONLY if there are no `account_foo` or `account_bar` belonging to it.
 *
 * @param db        context.
 * @param accountId specific Account to delete.
 */
private void deleteAccount(DSLContext db, ULong accountId) throws DatabaseException {
  db.deleteFrom(ACCOUNT)
    .where(ACCOUNT.ID.eq(accountId))
    .andNotExists(
      db.selectFrom(ACCOUNT_FOO)
        .where(ACCOUNT_FOO.ACCOUNT_ID.eq(accountId))
    )
    .andNotExists(
      db.selectFrom(ACCOUNT_BAR)
        .where(ACCOUNT_BAR.ACCOUNT_ID.eq(accountId))
    )
    .execute();
}

The following query "works" when executed exactly like this via direct MySQL client:

mysql> DELETE FROM `account` \
         WHERE `account`.`id`=12 \
           AND NOT EXISTS (SELECT `id` FROM `account_foo` WHERE `account_foo`.`account_id`=12) \
           AND NOT EXISTS (SELECT `id` FROM `account_bar` WHERE `account_bar`.`account_id`=12); \
       SELECT ROW_COUNT();
Query OK, 1 row affected (0.01 sec)

+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM `account` \
         WHERE `account`.`id`=14 \
           AND NOT EXISTS (SELECT `id` FROM `account_foo` WHERE `account_foo`.`account_id`=14) \
           AND NOT EXISTS (SELECT `id` FROM `account_bar` WHERE `account_bar`.`account_id`=14); \
       SELECT ROW_COUNT();
Query OK, 0 rows affected (0.00 sec)

+-------------+
| ROW_COUNT() |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

BUT the following version implementing jOOQ fails:

/**
 * Delete the Account ONLY if there are no `library` or `account_user_role` belonging to it.
 *
 * @param db        context.
 * @param accountId specific Account to delete.
 */
private Integer deleteAccount(DSLContext db, ULong accountId) throws DatabaseException {
  Integer rows;
  String accountIdString = accountId.toString();
  ResultSet rs = db.resultQuery(
    "DELETE FROM `account` "+
        "WHERE `account`.`id`=" + accountId.toString() +
        "AND NOT EXISTS (SELECT `id` FROM `account_foo` WHERE `account_foo`.`account_id`=" + accountId.toString() +") "+
        "AND NOT EXISTS (SELECT `id` FROM `account_bar` WHERE `account_bar`.`account_id`=" + accountId.toString() +"); "+
        "SELECT ROW_COUNT();"
  ).fetchResultSet();

  try {
    rs.next();
    rows = rs.getInt(1);
  } catch (SQLException e) {
    throw new DatabaseException("SQLException: " + e.getMessage());
  }

  return rows;
}

Here's the stack trace of the failure:

org.jooq.exception.DataAccessException: SQL [DELETE FROM `account` WHERE `account`.`id`=14 AND NOT EXISTS (SELECT `id` FROM `account_foo` WHERE `account_foo`.`account_id`=14) AND NOT EXISTS (SELECT `id` FROM `account_bar` WHERE `account_bar`.`account_id`=14); SELECT ROW_COUNT();]; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ROW_COUNT()' at line 1
    at org.jooq.impl.Tools.translate(Tools.java:1941)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:659)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:362)
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:365)
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:352)
    at org.jooq.impl.AbstractResultQuery.fetchResultSet(AbstractResultQuery.java:318)
    at io.outright.xj.hub.controller.account.AccountControllerImpl.deleteAccount(AccountControllerImpl.java:173)
    at io.outright.xj.hub.controller.account.AccountControllerImpl.deleteAccount(AccountControllerImpl.java:84)
    at io.outright.xj.hub.resource.accounts.AccountRecordResource.deleteAccount(AccountRecordResource.java:109)
    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 org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:164)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:181)
    at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:158)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:101)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
    at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:305)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
    at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
    at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:288)
    at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1110)
    at org.glassfish.jersey.grizzly2.httpserver.GrizzlyHttpContainer.service(GrizzlyHttpContainer.java:381)
    at org.glassfish.grizzly.http.server.HttpHandler$1.run(HttpHandler.java:219)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:565)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:545)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ROW_COUNT()' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
    at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
    at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826)
    at com.mysql.cj.jdbc.PreparedStatement.execute(PreparedStatement.java:1153)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
    at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:269)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348)
    ... 32 more

See also: MySQL doc for ROW_COUNT()

Answer 1

You're running into two issues:

1. Your JDBC driver must be set to allow multiple queries per JDBC statement

You need to add this flag to your JDBC connection URL: allowMultiQueries=true

See for instance this stack overflow question.

2. jOOQ returns only the first result of a batch

When sending a batch through jOOQ's plain SQL API, you'll be getting only the first result from the batch. In this case, that's the INSERT statement's update count, not the result of your SELECT statement (note there's also bug #5818 in jOOQ 3.9.1).

What you probably want to do is to use ResultQuery.fetchMany():

db.resultQuery(
    "DELETE FROM `account` "+
    "WHERE `account`.`id`={0} "+
    "AND NOT EXISTS (SELECT `id` FROM `account_foo` WHERE `account_foo`.`account_id`={0}) "+
    "AND NOT EXISTS (SELECT `id` FROM `account_bar` WHERE `account_bar`.`account_id`={0}); "+
    "SELECT ROW_COUNT();", val(accountId)
  ).fetchMany();

This will return both the update count and the result set.

Note, to prevent SQL injection risks, I've also replaced your string concatenation of accountId into your SQL statement by a bind variable.