Recently, I made a batch import requirement, and batch inserting multiple records into the database.
Solution: encapsulate a List collection object in the program, and then insert the entities in the collection into the database. Because the project uses MyBatis, it is planned to use MyBatis' foreach function for batch insertion. During this period, I encountered the error "SQL command did not end correctly", and finally solved it and recorded it for future review and learning.
First, I referenced the information about Mybatis' foreach insert online, as follows:
Foreach is mainly used in building in conditions, it can iterate over a collection in SQL statements.
The attributes of the foreach element mainly include item, index, collection, open, separator, and close.
Item represents the alias when each element in the collection is iterated. Index specifies a name to represent the position to which each iteration is reached during the iteration process. Open represents what starts with the statement, separator represents what symbols are used as separators between each iteration, and close represents what ends with. The most critical and most error-prone thing when using foreach is the collection attribute. This attribute must be specified, but in different cases, the value of the attribute is different. There are three main situations:
1. If the passed in a single parameter and the parameter type is a List, the collection attribute value is list
2. If the passed in a single parameter and the parameter type is an array, the property value of the collection is array
3. If there are multiple parameters passed in, we need to encapsulate them into a map. Of course, a single parameter can also be encapsulated into a map.
Then, the following xml file was written according to the copy.
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 INTO 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>However, after running, it keeps reporting errors, and 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: The SQL command did not end correctly
Copying SQL out and running in PL/SQL also reports the same error. As can be seen above, SQL statements executed using batch insert are equivalent to: INSERT INTO T_OCL_SUPCITY (CITY_ID,CITY_CODE, CITY_NAME, AREA_DESC, SUP_ID, STAT) VALUES (?,?,?,?,?),(?,?,?,?,?), and in oracle insert into xxx values (xxx,xxx), (xxx,xxx) is unparalleled. Looking back at that article, I found that this is suitable for MySQL and not for Oracle, so I modified 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} STATFROM dual</foreach>)A</insert>Run through.
In Oracle version, there are a few things to note:
1. There is no VALUES in SQL;
2. (selece...... from dual);
3. The attribute of the separator in the <foreach> tag is "UNION ALL", and the query will be merged into the result set.
The above is a quick solution to the batch insert error of Oracle+Mybatis foreach insert function introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!