Mybatis+oracle for bulk inserts and updates of data ____oracle

MyBatis is a very common framework of data persistence in Web engineering development, through which we can easily check and delete database. When the database connection commits the transaction, the resources that need to be spent are more, if need inserts the updated data more, and each transaction only submits one data, can cause very large database resource waste, causes the database performance, the system ability to drop greatly.

For bulk inserts on MyBatis, most examples on the web are mostly about MySQL databases, with fewer examples of Oracle databases. When an individual attempts to use MyBatis to perform a bulk insert of an Oracle database, it is found that MyBatis completes the process of assembling a batch-injected SQL statement. We only need to assemble the database support SQL statement in the MyBatis configuration file basically also completes the batch inserts the work.

MyBatis in combination with Oracle database for bulk data insertion, from the Oracle database point of view, is actually through a single SQL statement, inserting more than one data. As you can see from the query, the following statement inserts multiple data into a single statement:

INSERT all into 
        USERINFO (userid,username) VALUES (' 1001 ', ' Tom ') into
        USERINFO (userid,username) VALUES (' 1002 ' , "Black" into
        USERINFO (userid,username) VALUES (' 1003 ', ' Jetty ') into
        USERINFO (userid,username) VALUES (' 1004 ', ' Cat ')
      SELECT 1 from DUAL;

What we need to do now is to assemble the data into bulk by assembling the MyBatis configuration file in this format. MyBatis provides a <foreach> tag for circular control, at which point we can enter the list of user information as a parameter (ParameterType), as shown in the following example:

<insert id= "Batchinsertuser" parametertype= "java.util.ArrayList" >
Insert all 
        <foreach collection= "List" item= "userlist" index= "index" > Into
            USERINFO (userid,username) VALUES (#{userlist.userid},#{ Userlist.username})
        </foreach>
      SELECT 1 from DUAL
</insert>

About the use of <foreach> labels, the information is very much, no longer to repeat.

After the bulk insert of the data, the idea of the batch update is the same, but the update is simpler than the data insertion. Specifically, refer to the following example, the entry or the list of users who need to update the information:

<update id= "Batchupdateuser" parametertype= "java.util.ArrayList" >
	<foreach collection= "list" item= " UserList "index=" index "open=" Begin "close="; end; "Separator="; " >
		UPDATE USERINFO T  
		   <set>
			t.userid = #{userlist.userid,jdbctype=varchar},
			t.username = #{ Userlist.username,jdbctype=varchar},
		   </set>
		 WHERE
			t.userid = #{userlist.userid,jdbctype= VARCHAR}
	</foreach>
</update>

The problems encountered: I am in the bulk data insertion, encountered a problem, when the size of the parameter list () is larger, will lead to insert failure, after repeated attempts to find the size of the list () within 500, batch operation no problem, the specific reason has not been found, Please advise us on this point.