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