0

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?

Gangnus
  • 24,044
  • 16
  • 90
  • 149
  • Have you seen this: https://stackoverflow.com/a/8338725/910736 – JamesB Nov 02 '20 at 00:15
  • As per the answer @JamesB linked to, Oracle does not support multiple statements in one command but you can send a single PL/SQL anonymous block that contains multiple SQL statements. `{CALL BEGIN DELETE FROM table1 WHERE ...; DELETE FROM table2 WHERE ...; END }` – MT0 Nov 02 '20 at 00:29
  • I would steer clear of this *feature*. Just run each SQL separately and sleep well at night. I personally don't like to be on call when the application crashes at 2 am, because DevOps changed the datasource URL -- migration, upgrade, deployment, new version, etc. -- and removed the flag. – The Impaler Nov 02 '20 at 02:19
  • @JamesB I gave a reference to that question in mine, it is it the first line, haven't you noticed? Of course, I read all that I could find. Proposing others technological solution has no value for me - I do not want to widen the spectre of used ways without need. – Gangnus Nov 02 '20 at 08:44
  • @MT0 In that answer is NOT said that Oracle does not support that in XML mapper. Only another way to work is proposed. Thank you for the information, that could be the answer. – Gangnus Nov 02 '20 at 08:47
  • @TheImpaler It is an unsupported feature, to put several SQLs into one XML MyBatis query? That could be an answer, too, but do you have any references about that? – Gangnus Nov 02 '20 at 08:50

1 Answers1

1

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

ave
  • 3,244
  • 2
  • 14
  • 20
  • Thank you. I see even one more plus here in your solution. If I don't use these begin/end brackets, I have to remove ";" from the Oracle SQL queries for MyBatis to accept them. With these brackets, as I see, the standard Oracle syntax is supported. So, it is better to use them even for single queries - thus we can use the queries in an SQL client for testing **unchanged**. – Gangnus Nov 03 '20 at 11:39
  • Glad to know it helped! :D – ave Nov 04 '20 at 17:03