CODEWITHSUNDEEP
javasqloracle-databasemybatis
Gangnus
Gangnus

Reputation: 24464

Two delete in one MyBatis query for Oracle?

I want to delete two rows from two tables. A row from each one. In the XML Mapper.
There is MyBatis executing multiple sql statements in one go, is that possible? touching that problem, but the answers are not about XML mapper or -look below - don't work for Oracle.

According to mentioned, I need to write:

<delete id="removeReportRecord" >
    DELETE FROM S01_SESTAVA WHERE (id_s01_sestava = #{idReport});
    DELETE FROM S02_DATA WHERE (id_s01_sestava = #{idReport});
</delete >
...
int removeReportRecord(Long idReport);

But that won't work on Oracle. I have tried no ";" at the third line, or on both delete operations (MyBatis with Oracle doesn't like the ending ";") or using annotations for the parameter in the interface - I always have the same message:

org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

If I use two consequent queries instead:

<delete id="removeReportRecord" >
    DELETE FROM S01_SESTAVA WHERE (id_s01_sestava = #{idReport})
</delete >
<delete id="removeReportData" >
    DELETE FROM S02_DATA WHERE (id_s01_sestava = #{idReport})
</delete >
...
int removeReportRecord(Long idReport);
int removeReportData(Long idReport);

No problems, all works. I have already covered these two operations with @Transactional and it works and seems safe, but how can I use these MyBatis XML + Oracle if I really need several SQL operators in one query? Am I doing something wrongly?

Upvotes: 0

Views: 2381

Answers (1)

ave
ave

Reputation: 3594

Whether executing multiple statements is supported or not depends on the driver.
Oracle's JDBC driver supports it in the following manner (tested with ojdbc8 19.8.0.0).

<delete id="removeReportRecord">
  BEGIN
    DELETE FROM S01_SESTAVA WHERE (id_s01_sestava = #{idReport});
    DELETE FROM S02_DATA WHERE (id_s01_sestava = #{idReport});
  END;
</delete >

Note that, with this approach, the method returns -1 rather than the number of deleted rows because the driver returns -1 from PreparedStatement#getUpdateCount().

Upvotes: 1

Related Questions