MyBatis list parameter calls Oracle stored procedures

How to use the MyBatis pass list type parameter to the database stored procedure to implement the bulk insert data.

MyBatis What to do when the parameter is a list type. As you all know, it's hard to get a lot of data out of MyBatis batch processing (transactions are managed by spring), all of which write logic in storage and the headaches (parameter lengths are limited), so what I want to do is encapsulate the parameters in the background as a single or multiple list collection. This parameter is passed directly through the MyBatis to the database stored procedure to get rid of many limitations of the MyBatis BULK INSERT data (for example: cannot return the primary key in real time, the foreach tag cycle set length is limited), second is the flexible control transaction in the storage, but this will involve two problems. MyBatis XML file in how to encapsulate, using Basetypehandler?
for this problem, after a day of research finally run through, say the solution:

1. Set up a database table, everyone should have their own library table, so this step can basically skip. I am here to post the library table is to let everyone correspond to the parameters inside. Database tables:

CREATE TABLE Zd_unit_menu
(
  unit_id VARCHAR2 (),
  menu_id VARCHAR2
)

2. Establish corresponding Java objects in the database (types in Oracle) and arrays:

CREATE OR REPLACE TYPE unit_menu_obj as OBJECT (
 unitid VARCHAR2 (),
 menuid VARCHAR2
);
CREATE OR REPLACE TYPE unit_menu_table as table of Unit_menu_obj;

3. Stored procedures:

Create or Replace procedure Save_unit_power (list0 in unit_menu_table,result0 out int) as
  sql_bind varchar (MB);
Begin for
 i in 1. List0.count LOOP  
    sql_bind: = ' insert into Zd_unit_menu (unit_id,menu_id) values (' | | List0 (i) unitid| | ', ' List0 (i). menuid| | ') ';
   Execute immediate sql_bind;
 End Loop;  
  
  commit;  
  Returns the result, the execution succeeds the word returns 1
  result0: = 1;
  --catch exception, rollback operation
  EXCEPTION when
      others THEN
          result0: = -1;--Execution Failure return-1
      ROLLBACK;
End Save_unit_power;

4. Take a look at the MyBatis configuration:

<parametermap type= "Java.util.Map" id= "_map" > <parameter property= "list0"
		jdbctype= "ARRAY"
			Javatype= "Java.util.List" mode= "in" typehandler= "Com.zd.util.ListHandler"/> <parameter property=
	    " Result0 "jdbctype=" DECIMAL "
			javatype=" Java.lang.Integer "mode=" Out "/>
  </parameterMap>
  < Select Id= "Addunitpower" statementtype= "callable" parametermap= "_map" >
		<![ cdata[call
		Save_unit_power (?,?)]
		] >
  </select>

5. See how I called it? I posted my server code directly, the DAO layer is not necessary:

list<unitmenu> list = new arraylist<unitmenu> ();
		map<string, object> _map = new hashmap<string, object> ();
		string[] Menuids = Menutreeids.split (",");
		Jsonobject job = new Jsonobject ();
		int result =-1;
		for (int i = 0; i < menuids.length i++) {
			Unitmenu um = new Unitmenu ();
			Um.setmenuid (Menuids[i]);
			Um.setunitid (Unitid);
			List.add (UM);
		}
		_map.put ("list0", list);
		_map.put ("Result0", "");
		Unitmenumapper.addunitpower (_MAP);
		System.out.println ("================================================_map =" +_map.tostring ());
		result = (Integer) _map.get ("Result0");
		System.out.println ("================================================result =" +result);

6. A very critical point, the attentive person should notice that In the Mapper.xml configuration, there are Typehandler configuration, which is actually a class I wrote, the role of this class is to talk about the list in Java into the database we set up objects and arrays, look at the code:

Package com.zd.util;
Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.ArrayList;

Import java.util.List;
Import Oracle.sql.ARRAY;
Import Oracle.sql.ArrayDescriptor;
Import Oracle.sql.STRUCT;

Import Oracle.sql.StructDescriptor;
Import Org.apache.ibatis.type.BaseTypeHandler;

Import Org.apache.ibatis.type.JdbcType;

Import Com.zd.model.system.UnitMenu; public class Listhandler extends basetypehandler{@Override the public Object getnullableresult (ResultSet arg0, String arg1
	Throws SQLException {//TODO auto-generated method stub return null; @Override public Object Getnullableresult (callablestatement arg0, int arg1) throws SQLException {//TODO auto-g
	enerated method stub return null; The @SuppressWarnings ("unchecked") @Override public void Setnonnullparameter (java.sql.PreparedStatement Parametersetter, int i, Object o, Jdbctype jdbctype) throWS SQLException {Connection conn = null;
				try {if (null!= o) {list<unitmenu> List = (arraylist<unitmenu>) o;
				conn = Drivermanager.getconnection ("Jdbc:oracle:thin:@192.168.1.88:1521:orcl", "TCTSCM", "TCTSCM"); This must be capitalized, and a package must be introduced, and if this package is not introduced, the string cannot be converted normally, the package is: Orai18n.jar array array = GetArray (conn, "Unit_menu_obj", "unit_menu_
				TABLE ", list);
			Parametersetter.setarray (i, array);
		} catch (Exception e) {e.printstacktrace ();
			} finally{if (NULL!= conn) {conn.close (); @SuppressWarnings ("Rawtypes") Private ARRAY GetArray (Connection con,string oracleobj, String oraclelist,
		List<unitmenu> listdata) throws Exception {array array = NULL;
		Arraydescriptor desc = arraydescriptor.createdescriptor (oraclelist, con);
		Struct[] structs = new struct[listdata.size ()]; if (listdata!= null && listdata.size () > 0) {structdescriptor Structdesc = new Structdescriptor (Oracleobj,
			Con); for (int i = 0; i < Listdata.size ();
				i++) {object[] result = {Listdata.get (i) Getunitid (), Listdata.get (i). Getmenuid ()};
			Structs[i] = new STRUCT (Structdesc, con, result);
		Array = new Array (desc, con, structs);
		}else{array = new Array (desc, con, structs);
	} return array; }
	
}

The steps are so much, pay attention to the details. Have questions can give me a message, hope to help everyone, do not like to spray.