MyBatis executing multiple sql statements in one go, is that possible?

Question

i was wondering if it is possible to execute multiple sql statements in 1 go. For example the scenario that i want to delete rows from multiple tables, is there a way i can do things like..

<delete id="delete" parameterType="String">
    DELETE FROM DUMMYTABLE_A where X=${value}
    DELETE FROM DUMMYTABLE_B where X=${value}
</delete>

score 25 · Answer 1 · edited Dec 01 '11 at 09:20

25

I'm using myBatis with Oracle. I guess there is something similar in other DB. Actually you always can create procedures in DB, which usually is better for the future, when you have to support the project.

<delete id="deleteUnfinishedData" parameterType="map">
    {call
        declare
        begin
            delete from TABLE1 where id = #{valueFromMap1};
            delete from TABLE2 where id = #{valueFromMap2};
        end
    }
</delete>

edited Dec 01 '11 at 09:20

Prateek

2,377
17
29

answered Dec 01 '11 at 09:01

StrekoZ

618
6
12

1

Oracle is a special beast at this topic. Thanks for showing that example, just what I needed today. – Marc Aug 13 '12 at 12:47
How could this be modified for SQL Server? – SMKS May 25 '16 at 19:40

Professor Falken · Accepted Answer · 2013-05-04T20:24:44.133

18

Yes, most databases allow this. Usually you have to delimit your SQL statements with something. In PostGRES and MySQL it's a semicolon (;). In Microsoft SQL server you should use the keyword GO. [ May 2013 Update: As of SQL Server 2012, you can and should use semicolons to delimit your statements. After SQL Server 2012 (ie. the next version and beyond) these will be mandatory. Using GO is now the deprecated way to do things in SQL2012 and beyond). ]

MySQL / PostGRES example:

 DELETE FROM DUMMYTABLE_A where X=${value};
 DELETE FROM DUMMYTABLE_B where X=${value};
 DELETE FROM DUMMYTABLE_C where X=${value};

MS-SQL example:

 DELETE FROM DUMMYTABLE_A where X=${value}
 GO
 DELETE FROM DUMMYTABLE_B where X=${value}
 GO
 DELETE FROM DUMMYTABLE_C where X=${value}

Better databases (ie. not MySQL) will also support transactions with BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN. Using transactions you can actually batch all the statements into one atomic operation, where if part of it failed, all three would be rolled back. See http://www.sqlteam.com/article/introduction-to-transactions for some more information about those.

Most likely all you need is the semicolons between your SQL statements though!

edited May 04 '13 at 20:24

answered Oct 18 '11 at 05:52

Professor Falken

1,017
11
13

1

I don't know but this doesn't work for me I am attaching the next gist https://gist.github.com/AdelinGhanaem/b565ca3a447e89087ab7 you can see that it does not separate the two statements with 'query' as with rollback ... – Adelin Oct 19 '15 at 12:25
3

I tried this way and myBatis threw an exception. The problem was that I didn't set the flag allowMultiQueries=true in the JDBC URL. Thanks. – Ye Kyaw Kyaw Htoo Nov 17 '15 at 04:56
This doesn't work for me as well, I'm trying to delete two batch of data via `in` condition, however I got sql grammar syntax error. – Saorikido Oct 25 '16 at 02:47
1

The problem was that I hadn't set the flag allowMultiQueries=true in the JDBC URL. http://stackoverflow.com/questions/23184619/multiple-statements-in-single-mapper-method-in-mybatis – Med-Salah OKA Mar 22 '17 at 12:44

score 1 · Answer 3 · answered Mar 15 '19 at 05:00

if anyone got an error like

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE mytable

You can fix this by allowing multi queries in your driver. For mariadb it would be the same as MySQL

allowMultiQuery=true

described in following mybatis issue https://github.com/mybatis/mybatis-3/issues/1497

score 0 · Answer 4 · answered Jul 03 '22 at 11:07

This code works for multiple Select in one go in MSSQL:

<select id="selectMultipleQueries" resultSets="movies,genres" resultMap="multipleQueriesResult">
        BEGIN
            select M.ID_         as mId,
                   M.NAME_       as mName,
            from TestMyBatis.dbo.Movie as M
            where M.ID_ = #{id,jdbcType=INTEGER,mode=IN};
            select G.ID_ as gId, 
                   G.NAME_ as gName
            from TestMyBatis.dbo.Genre as G
            where G.ID_ = #{id,jdbcType=INTEGER,mode=IN};
        END
</select>

MyBatis executing multiple sql statements in one go, is that possible?

4 Answers4

Linked