Oracle+mybatis a quick fix for a batch insert error for a foreach insert _java

Recently make a batch import requirement, insert multiple records into the database in bulk.

Solution: Encapsulate a list object in a program, and then insert the entities in the collection into the database because the project uses MyBatis, so you intend to use the MyBatis foreach feature for bulk inserts. The "SQL command did not end correctly" error was encountered during the period, which was eventually resolved and recorded for later reference and study.

First, the online reference to the MyBatis of a foreach insert information, as follows:

The main use of foreach is in the build in condition, which can iterate over a collection in an SQL statement.

The properties of a foreach element are mainly item,index,collection,open,separator,close.

Item represents the alias for each element of the collection when it is iterated, and index specifies a name that represents where each iteration is in the iteration, open indicates what the statement starts with, and separator represents what symbol to use as a separator between iterations, Close indicates what ends with foreach, and the most critical and error-prone is the collection property, which must be specified, but in different cases the value of the property is not the same, mainly for 3 things:

1. If a single parameter is passed in and the parameter type is a list, the collection property value is List

2. If a single parameter is passed in and the parameter type is an array, the collection property value is array

3. If the incoming parameters are multiple, we need to encapsulate them into a map, and of course single parameters can be encapsulated into a map

Then, divert wrote the following XML file,

Xxxmapper.xml file:

<insert id= "addsupcity" parametertype= "java.util.List" >
<selectkey keyproperty= "Cityid" order= "before" "Resulttype=" String >
<![ Cdata[select seq_ocl_supcity. Nextval from dual]]>
</selectKey>
INSERT to T_ocl_supcity
(City_id,city_code, City_name, area _desc, sup_id, STAT)
VALUES 
<foreach collection= "list" item= "item" index= "index" separator= "," > 
(
#{item.cityid,jdbctype=varchar},
#{item.citycode,jdbctype=varchar},
#{item.cityname,jdbctype= VARCHAR},
#{item.areadesc,jdbctype=varchar},
#{item.supid,jdbctype=varchar},
#{item.stat, Jdbctype=varchar}
)
</foreach>
</insert>

But after the operation has been the error, the error message is as follows:

### Sql:insert into T_ocl_supcity
(City_id,city_code, City_name, Area_desc, sup_id, STAT) VALUES (?,?,?,?,?), (?,?, ?,?,?)
### cause:java.sql.sqlsyntaxerrorexception:ora-00933:sql command does not end correctly

Copying SQL out of the Pl/sql is also the same mistake, as you can see, the SQL statements executed using bulk inserts are equivalent to: INSERT INTO t_ocl_supcity (City_id,city_code, City_name, Area_ DESC, sup_id, STAT) VALUES (?,?,?,?,?), (?,?,?,?,?), and insert into XXX values (XXX,XXX) in Oracle, (XXX,XXX) This syntax is not a pass. Go back to that article and find out that it applies to MySQL and does not apply to Oracle, so modify the XML file:

<insert id= "addsupcity" parametertype= "java.util.List" >
insert INTO t_ocl_supcity
(city_id,city_code , City_name, Area_desc, sup_id, STAT)
SELECT seq_ocl_supcity. Nextval city_id, a.* from
(
<foreach collection= "list" item= "item" index= "index" separator= "UNION All" >
SELECT 
#{item.citycode,jdbctype=varchar} city_code,
#{item.cityname,jdbctype=varchar} city_name,
#{item.areadesc,jdbctype=varchar} area_desc,
#{item.supid,jdbctype=varchar} sup_id,
#{item.stat, Jdbctype=varchar} STAT from
dual
</foreach>
A
</insert>

Run through.

In Oracle's version, there are several points to note:

No values in 1.SQL;

2.<foreach> (Selece ... from dual) in the label;

The separator attribute in the 3.<foreach> tag is "UNION all", and the query merges the result set.

The above is a small set to introduce the Oracle+mybatis of the foreach insert to insert the error of the quick solution, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!