MyBatis Mapper with several SQLs in one method using DB2

Question

I need to execute several SQL statements in one MyBatis Mapper method, because the SQLs are dependend on each other. With H2, this is no problem:

    @Delete("DELETE FROM DETAIL_TABLE " +
        "      WHERE MASTER_ID IN " +
        "              (SELECT ID FROM MASTER WHERE BUSINESS_KEY = #{businessKey});" +
        "DELETE FROM MASTER " +
        "      WHERE BUSINESS_KEY = #{businessKey}; ")
void delete(@Param("businessKey") Integer businessKey);

When switching to DB2 the statement does not work anymore, because the JDBC driver throws the following exception:

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=?;DELETE FROM MASTER       WHERE;TAIL WHERE BUSINESS_KEY =;<space>, DRIVER=4.26.14

This is just an easy example to demonstrate the issue. I am aware that you can send a cascading delete statement in the shown situation.

I read a StackOverflow post, that multiple SQL statements in one call are not supported by some JDBC drivers and are not recommended, but sometimes you need to execute different SQLs in a certain order, so defining several MyBatis mapper methods does not solve the issue.

Does anybody has any idea how this can be achieved?

It's a common requirement and there is a mechanism called 'transaction' to group multiple separate statement calls. The doc is [here](https://mybatis.org/mybatis-3/java-api.html#sqlSessions). If you use mybatis-spring or mybatis-spring-boot, see [here](https://mybatis.org/spring/transactions.html). For mybatis-guice, [here](http://mybatis.org/guice/transactional.html). — ave, Feb 18 '20 at 14:36
The problem were not the transaction handling, but the DB2 syntax for encapsulating multiple commands. — Thorsten Kitz, Feb 20 '20 at 07:53

score 1 · Accepted Answer · edited Mar 04 '20 at 19:55

1

On the DB2 side, you can run a compound statement

  "BEGIN ATOMIC" +
  "  DELETE FROM DETAIL_TABLE" +
  "        WHERE MASTER_ID IN" +
  "                (SELECT ID FROM MASTER WHERE BUSINESS_KEY = #{businessKey});" +
  "  DELETE FROM MASTER" +
  "        WHERE BUSINESS_KEY = #{businessKey};" +
  "END"

or create and call a stored procedure

edited Mar 04 '20 at 19:55

marc_s

732,580
175
1,330
1,459

answered Feb 18 '20 at 08:41

nfgl

2,812
6
16

Thanks, that was the statement, I was looking for. Coming from live long Oracle programming, DB2 is kind of different. – Thorsten Kitz Feb 20 '20 at 07:52
@ThorstenKitz Maybe your DBA will accept to activate Oracle compatibility features https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html – nfgl Feb 20 '20 at 11:06

MyBatis Mapper with several SQLs in one method using DB2

1 Answers1