MyBatis BULK INSERT Oracle Database

Take a look at the code that executes correctly:

	<insert id= "Insertbatchxyfalse" parametertype= "java.util.List" > INSERT into Jm_zyk_xy_false (ID, gcxh, HPHM, HPZL  , Hpys, CSYS, Clxh, Cllx, CLPP, Jdcsyr, Sjhm, KKBH, Fxlx, CDH, BDLJ, TP1, TP2, TP3, GCSJ, XZQH, CZR, xxly, RKSJ, ZT, 
     			BY1, BY2, BY3) <foreach close= ")" collection= "list" item= "item" index= "Index" open= "(" separator= "union > Select #{item.id,jdbctype=varchar}, #{item.gcxh,jdbctype=varchar}, #{item.hphm,jdbctype=varc HAR}, #{item.hpzl,jdbctype=varchar}, #{item.hpys,jdbctype=varchar}, #{item.csys,jdbctype=varchar}, #{i Tem.clxh,jdbctype=varchar}, #{item.cllx,jdbctype=varchar}, #{item.clpp,jdbctype=varchar}, #{item.jdcsyr,jdb Ctype=varchar}, #{item.sjhm,jdbctype=varchar}, #{item.kkbh,jdbctype=varchar}, #{item.fxlx,jdbctype=varchar} , #{item.cdh,jdbctype=integer}, #{item.bdlj,jdbctype=varchar}, #{item.tp1,jdbctype=varchar}, #{item.tp
				2,jdbctype=varchar},	#{item.tp3,jdbctype=varchar}, #{item.gcsj,javatype=string, Jdbctype=timestamp}, #{item.xzqh,jdbctype=varchar},
					#{item.czr,jdbctype=varchar}, #{item.xxly,jdbctype=varchar}, Sysdate, #{item.zt,jdbctype=integer},
    		#{item.by1,jdbctype=varchar}, #{item.by2,jdbctype=varchar}, #{item.by3,jdbctype=varchar} from dual </foreach> </insert>

Here you should pay attention to the handling of null values, which are configured in Mybatis-config.xml:

  <settings>
  	<setting name= "Jdbctypefornull" value= "NULL"/>
  </settings>

Here you should pay attention to the location of the settings, after the properties, typealiases before.

Then write a unit test:

	@Test public
	void Insertbatchxyfalsetest () {
		list<vehiclexyfalse> vflist = new arraylist< Vehiclexyfalse> ();
		for (int i = 0; i < i++) {
			Vehiclexyfalse VF = new Vehiclexyfalse ("1001" +i, "2001" +i, "Xiang AD1234", "02", "4301", "1") ;
			Vflist.add (VF);
		}
		Ixyzykservice Xyzykservice = (xyzykservice) context.getbean ("Xyzykservice");
		try {
			xyzykservice.insertbatchxyfalse (vflist);
		} catch (Serviceexception e) {
			e.printstacktrace ();
		}
	}

The test passed and the database successfully inserted 2000 data.

But here's a hole, if we increase the amount of data inserted at one time to 5000, the error "CAUSE:JAVA.SQL.SQLSYNTAXERROREXCEPTION:ORA-01745: Invalid host/bound variable name". It is a grammatical error on the Internet. But essentially, SQL is too long, and the SQL statement has a length limit. Oracle documents are 64KB and are actually related to many environments: Database configuration, disk space, how much memory, and so on.

Can only be written in batches, so performance will be higher.