Resolve Oracle+mybatis BULK INSERT error: SQL command does not end correctly

MyBatis BULK INSERT requires a foreach element. The foreach element has the following main properties:

(1) Item: the alias at which each element in the collection iterates.

(2) Index: Specifies a name that represents the position of each iteration during the iteration.

(3) Collection: Determined according to the parameter value passed in.

(4) Open: Indicates what the statement starts with.

(5) Separator: Indicates what symbol is used as the delimiter between each iteration.

(6) Close: Indicates what ends with.

First, the wrong XML configuration file is as follows:

<insert id= "Save" databaseid= "Oracle" >
INSERT INTO "Sys_user_role"
(
"User_ID",
"ROLE_ID"
) values
<foreach collection= "roleidlist" item= "item" index= "index" separator= "," >
(
#{userid},
#{item}
)
</foreach>
</insert>

If you write like this, you will get an error: The SQL command did not end correctly.

The correct XML configuration file has been modified as follows:

<insert id= "Save" databaseid= "Oracle" >
INSERT INTO "Sys_user_role"
(
"User_ID",
"ROLE_ID"
)
<foreach collection= "roleidlist" item= "item" index= "index" separator= "UNION All" >
SELECT
#{userid},
#{item}
From dual
</foreach>
</insert>

Depending on the profile, there are three places to be aware of:

(1) Need to remove values

(2) The separator attribute value is changed to union all. Because using INSERT into XXX values (XXX,XXX) in Oracle, this syntax is xxx,xxx

(3) You need to remove the parentheses in the foreach tag to add select. From dual.

Resolve Oracle+mybatis BULK INSERT error: SQL command does not end correctly