前言:

        方向: java - Spring MVC -MyBatis - SQL存储过程

        实战目标:MyBatis调用存储过程传入List集合类型的数据进行添加操作;

        案例必备小知识:

                Spring MVC , MyBatis , MySQL存储过程;

实战在线:

        1.List集合类型接收页面的数据:

         2.集合类型预处理(将List集合类型转换为字符串类型):

         3.MySQL中创建存储过程:

-- 存储过程中进行批量添加

create PROCEDURE addUserList(in arrayStr varchar(255), in delimiter varchar(255))
BEGIN
	declare i int default 0;
	
	set @arrayLength = 1 + (length(arrayStr) - length(REPLACE(arrayStr,delimiter,'')));
	
	while i < @arrayLength

	DO

	set i = i+1;
	
	set @result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(arrayStr,delimiter,i)),delimiter,1));

-- 	添加语句
-- 	位置截取:SUBSTRING_INDEX(SUBSTRING_INDEX(@result,',',1),',',-1)
  insert into d_user(uname,pwd,`status`) values (SUBSTRING_INDEX(SUBSTRING_INDEX(@result,',',1),',',-1),SUBSTRING_INDEX(SUBSTRING_INDEX(@result,',',2),',',-1),0);

  end while;

END

drop PROCEDURE addUserList

-- 存储过程测验
call addUserList("小小英,123-小mei美,222","-");

         4.MyBatis调用存储过程进行添加:

<insert id="addUserList" statementType="CALLABLE">
        {
            call addUserList(#{arrayStr},#{delim})
        }
</insert>

 小结:

        1.MySQL存储过程中传入List集合类型时需要预先转化成字符串,以字符串形式传参;

        2.存储过程中可以先循环数组再进行sql操作即可(注意截取);

        3.存储过程中传集合类型并不常用,在做批量数据处理是可以调用处理;

        4存储过程中若需要进行复杂操作是可以创建临时表,在数据表基础上操作会更方便.

声明:

        此文内容已进过测试可用,若有问题请及时联系小编;

        此文仅支持交流学习之用,不支持用作它途,欢迎大家交流指正;

Dao

List<Map<String,Object>> minuteSum(@Param("tableName") String tableName,@Param("dayTime") Date dayTime);

Mapper

<resultMap type="java.util.HashMap" id="resultMapMinute">
    <result column="value" property="value" javaType="java.lang.Double" jdbcType="DOUBLE"/>
    <result column="time" property="time" javaType="java.util.Date" jdbcType="TIMESTAMP"/>
</resultMap>

<select id="minuteSum" resultMap="resultMapMinute" statementType="CALLABLE">
    {CALL etl_minute_sum(#{tableName,mode=IN},#{dayTime,mode=IN})}
</select>

效果

（一）jdbc连接：

--第一步，创建数据库对象  
CREATE OR REPLACE TYPE param_object is object(  
  configId nvarchar2(64),  
  evaluateScore number(18,2),  
  evaluateLevel nvarchar2(64)  
);   
  
  
--第二步，创建数据库list并且与数据库对象挂关系  
create type param_array as table of param_object;  
  
  
--第三步，创建存储过程将list<object>  
create or replace procedure PRO_UPDATE_EVALUATE_SCORE(paramList in param_array,  
                                                      result_id out varchar2) is  
begin  
  for i in 1 .. paramList.count loop  
    update SCM_TEST  
       set CONFIGID      = paramList(i).configId,  
           EVALUATESCORE = paramList(i).evaluateScore where  
           EVALUATELEVEL = paramList(i).evaluateLevel;  
           result_id := i;  
  end loop;  
  commit;  
end PRO_UPDATE_EVALUATE_SCORE;  




[java] view plain copy
package com.atguigu.springdata.test;  
  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.SQLException;  
import java.sql.Types;  
import java.util.ArrayList;  
import java.util.Date;  
import com.sun.org.apache.xerces.internal.impl.dtd.models.DFAContentModel;  
  
import oracle.jdbc.OracleCallableStatement;  
import oracle.sql.ARRAY;  
import oracle.sql.ArrayDescriptor;  
import oracle.sql.STRUCT;  
import oracle.sql.StructDescriptor;  
  
public class Tesst {  
    // 数据库连接  
    private static final String connectionURL = "jdbc:oracle:thin:@127.0.0.1:1521/oracle11g";  
    private static final String userID = "oracle11g";  
    private static final String userPassword = "oracle11g";  
    private static final String driver_class = "oracle.jdbc.driver.OracleDriver";  
  
    public void runTest() {  
        //组装需要的参数List<String[]> String[] length 要和数据库的自定对象PARAM_OBJECT个数一样  
        String chars = "abcdefghijklmnopqrstuvwxyz";  
        ArrayList list = new ArrayList<>();  
        for(int i=0;i<2000;i++){  
            String c = chars.charAt((int)(Math.random() * 26))+"";  
            String[] values = { i + 1 + "", i + 1 + "",c.toUpperCase()};  
            list.add(values);  
        }  
             
             
        Connection con = null;  
        OracleCallableStatement stmt = null;  
        try {  
            Class.forName(driver_class).newInstance();  
            con = DriverManager.getConnection(connectionURL, userID,  
                    userPassword);  
              
            ARRAY aArray = getArray(con, "PARAM_OBJECT","PARAM_ARRAY", list,11);  
              
            System.out.println("开始时间："+  new Date());  
            stmt = (OracleCallableStatement) con.prepareCall("{call PRO_UPDATE_EVALUATE_SCORE(?,?)}");  
              
            stmt.setARRAY(1, aArray);  
//          stmt.setArray(1, aArray);  
            stmt.registerOutParameter(2, Types.VARCHAR);  
              
            stmt.execute();  
            String string = stmt.getString(2);  
            System.out.println("结束时间："+  new Date());  
            System.out.println("输出参数："+string);  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            if (stmt != null) {  
                try {  
                    stmt.close();  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
            if (con != null) {  
                try {  
                    con.close();  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
        }  
    }  
  
    /** 
     * 将java数组转换成数据库数组 
     * @param con           原生jdbc链接，链接池的链接不行 
     * @param OracleObj     数据库自定义对象 
     * @param Oraclelist    数据库自定义数组 
     * @param objlist       需要的参数 
     * @param paramNum      自定义对象的个数 
     * @return   
     * @throws Exception 
     */  
    private ARRAY getArray(Connection con, String OracleObj,  
  
    String Oraclelist, ArrayList objlist,int paramNum) throws Exception {  
  
        ARRAY list = null;  
  
  
        if (objlist != null && objlist.size() > 0) {  
  
            StructDescriptor structdesc = StructDescriptor.createDescriptor(OracleObj,con);  
              
            STRUCT[] structs = new STRUCT[objlist.size()];  
  
            Object[] result = new Object[0];  
  
            for (int i = 0; i < objlist.size(); i++) {  
                Object[] object = (Object[]) objlist.get(i);  
                String[] str = (String[]) objlist.get(i);  
                  
                result = new Object[paramNum];  
  
                // 数组大小应和你定义的数据库对象(AOBJECT)的属性的个数  
                for(int j=0;j<paramNum;j++){  
                    result[j] = str[j];  
                }  
  
                // 将list中元素的数据传入result数组 result[1] = new Integer(..); //  
                structs[i] = new STRUCT(structdesc, con, result);  
            }  
  
            ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,  
                    con);  
  
            list = new ARRAY(desc, con, structs);  
        }  
  
  
        return list;  
  
    }  
  
    public static void main(String[] args) {  
        Tesst testListToProcedure = new Tesst();  
        testListToProcedure.runTest();  
    }  
  
}  

注：自定义对象里面的字符必须要使用nvarchar2，不然java代码将字符串的格式封装不进去。链接要使用jdbc原生链接（Connection）才能对自定义对象和数组的转换，使用链接池转换不过来。如果谁使用了连接池成功了麻烦您评论下如何操作的，谢谢！

（2）oracle  mybatils调用

1.Controller层：

// 读取解析Excel,并判断是否解析成功
List<String[]> list = ReadExcel.batchimport(file);
List list1 = new ArrayList<Budgetinfo>();
int size = list.size();
if(size>1){
for(int i=1;i<size;i++){
String [] obj = list.get(i);
Budgetinfo budgetinfo=new Budgetinfo();
budgetinfo.setCreaty(obj[0]); // 年份
if(!StringUtil.isEmpty(obj[1])){
budgetinfo.setCreatm(Integer.parseInt(obj[1])); // 月份
}
budgetinfo.setOneOrgname(obj[2]); // 所属组织
budgetinfo.setOneSubjectname(obj[3]); // 一级科目
budgetinfo.setTwoSubjectname(obj[4]); // 二级科目
budgetinfo.setThreeSubjectname(obj[5]); // 三级科目
budgetinfo.setFourSubjectname(obj[6]); // 四级科目
if(!"0.0".equals(obj[7])){
budgetinfo.setBudget(Double.parseDouble(obj[7])); // 预算金额
}
budgetinfo.setCreatetor(user.getStaffname()); // 创建人

list1.add(budgetinfo);
budgetinfo = null;
}
//调用存储过程
HashMap<String,Object> map = new HashMap<String,Object>();
map.put("arr",list1);

res = service.importData(map);

2.dao层：

public BaseArgument importData(HashMap<String, Object> map) {
BaseArgument res=new BaseArgument();
getSqlSession().selectOne("BudgetinfoMapper.importExcelData", map);
return res;

}

3.XML代码：

<parameterMap type="java.util.Map" id="inputMap">  
        <parameter property="arr" jdbcType="ARRAY"  
            javaType="java.util.List" mode="IN" typeHandler="com.ydtx.comm.ListHandler"/>  
        <parameter property="v_out" jdbcType="VARCHAR"  
            javaType="java.lang.String" mode="OUT" />  

  </parameterMap> 

<!-- 调用存储过程导入excel数据 -->
<select id="importExcelData" statementType="CALLABLE" parameterMap="inputMap">
{call sp_loaddata(?,?)} 

</select>

3.对传入的List集合做处理的类

package com.ydtx.comm;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;


import com.ydtx.bean.pojo.payBudget.Budgetinfo;
import com.ydtx.utils.StringUtil;


import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;


public class ListHandler extends BaseTypeHandler{
  
    @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<Budgetinfo> list = (ArrayList<Budgetinfo>) o;  
                conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.16:1521:oracle", "CWSYS", "CWSYS");  
                //这里必须得用大写，而且必须要引入一个包，如果不引入这个包的话字符串无法正常转换，包是：orai18n.jar  

                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<Budgetinfo> 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).getCreaty(), // 年份
                listData.get(i).getCreatm(), // 月份
                listData.get(i).getOneOrgname(), // 所属组织
                listData.get(i).getOneSubjectname(), // 一级科目
                listData.get(i).getTwoSubjectname(), // 二级科目
                listData.get(i).getThreeSubjectname(), // 三级科目
                listData.get(i).getFourSubjectname(), // 四级科目
                listData.get(i).getBudget(), // 预算金额
                listData.get(i).getCreatetor()
                };  
                structs[i] = new STRUCT(structdesc, con, result);  
            }  
            array = new ARRAY(desc, con, structs);  
        }else{  
            array = new ARRAY(desc, con, structs);  
        }  
        return array;  
    }


@Override
public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
// TODO Auto-generated method stub
return null;
}


@Override
public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
// TODO Auto-generated method stub
return null;
}


@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
// TODO Auto-generated method stub
return null;
}

}