【mybatis调用存储过程】传list参数返回字段

以oracle存储过程为例

1.数据库表

create table ZD_UNIT_MENU
(
  unit_id VARCHAR2(32),
  menu_id VARCHAR2(32)
)

2.在Oracle中建立相应java对象的类型和数组

CREATE OR REPLACE TYPE unit_menu_obj AS OBJECT(
 unitId VARCHAR2(32),
 menuId VARCHAR2(32)
);
CREATE OR REPLACE TYPE unit_menu_list AS table OF unit_menu_obj;

3.存储过程

create or replace procedure save_unit_power(list0 in unit_menu_table,result0 out int) as
  sql_bind varchar(200);
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;  
  --返回结果，执行成功的话返回1
  result0 := 1;
  --捕捉异常，回滚操作
  EXCEPTION
      WHEN OTHERS THEN
          result0 := -1;--执行失败的话返回-1
      ROLLBACK;
end save_unit_power;

4.mybatis的sqlmapper配置

<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.dao接口定义方法

public void addUnitPower(Map<String, Object> paramsMap);

6.service方法

 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);

7.自定义typeHandler

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
	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-generated method stub
		return null;
	}
 
	@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");
				//这里必须得用大写，而且必须要引入一个包，如果不引入这个包的话字符串无法正常转换，包是：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;
	}
	
}