oracle通过存储过程上传list保存功能

create TYPE "AL01TYPE"                                                                                                                                                                                                                                   as object

(

-- 描述 : 档案批量转出

-- 作者  : dt

-- 时间 : 2021-05-10

-- 版本 ：dev-1.0.1

   aac003       NVARCHAR2(100),

   aac002       NVARCHAR2(50),

   aat001       NVARCHAR2(50),

   aat002       NVARCHAR2(50),

   aat013       NVARCHAR2(20),

   aae011       NVARCHAR2(20),

   aae036       NVARCHAR2(20),

   aah002       NVARCHAR2(100)

);

create type AL01TYPELIST as table of AL01TYPE;

-- auto-generated definition

create PROCEDURE SP_HFSZHDA_DOUPLOADAL01(LIST   IN    AL01TYPELIST,

                                        po_message OUT VARCHAR ) IS

                                        --描述：档案转出excel上传

                                        --作者：dt

                                        --时间：2021-05-10

                                        --版本：dev-1.0.1

   v_object    AL01TYPE;

   le_error EXCEPTION;

   P_renum number(20);

   v_aah002  VARCHAR (100);

   ls_count number;

   ls_aaf025 VARCHAR (50);

BEGIN

   P_renum  := 0; --初始化

   FOR I IN 1 ..  LIST. count LOOP

     P_renum := 1 + P_renum;

       v_object := LIST(I);

        select    replace (utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_object.aah002)),unistr( '\0000' ))  into   v_aah002 from    dual;

       begin

         Select count (0) into ls_count from az03 where aat001 = v_object.aat001 AND aat012 = '1' ;

         if ls_count=0 then

            po_message := '号：' ||v_object.aat001|| ' 状态异常请核对后再上传！' ;

            RAISE le_error;

            end if;

          Select count (0) into ls_count From AL01 where aaj022= '1' and aat001=v_object.aat001 and aah002=v_aah002;

          if ls_count =0 then

         -- 开始插入信息

       select   SQ_AAF025.nextval into ls_aaf025 from dual;

       insert into AL01(

       aaf025,

       aat012,

       aat001,

       aac003,

       aac002,

       aat002,

       aat013,

       aaj022,

       aaj026,

       aae011,

       aae036,

       aah002) values (

                  ls_aaf025,

                  '1' ,

                  v_object.aat001,

                  v_object.aac003 ,

                  v_object.aac002 ,

                  v_object.aat002 ,

                  v_object.aat013,

                  '1' ,

                  'excel上传数据' ,

                  v_object.aae011,

                  v_object.aae036,

                  v_aah002

             );

             end if;

     IF P_renum >1000 THEN

             commit ;

             P_renum:=0;

         END IF;

       po_message := 'ok' ;

EXCEPTION

                    WHEN le_error THEN

                    NULL ;

                    WHEN OTHERS THEN

                    ROLLBACK ;

                    po_message := '上传失败' || SQLCODE || SQLERRM;

         end ;

   END LOOP;

   COMMIT ;

END SP_HFSZHDA_DOUPLOADAL01;

package com.cominfo.elecfile.utils;

import oracle.jdbc.OracleConnection;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

import oracle.sql.STRUCT;

import oracle.sql.StructDescriptor;

import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor;

import java.sql. Connection ;

import java.util.List;

/**

  * 描述

  *

  * @Auther: dt

  * @ Date : 2021/5/10 0027 09:00

  */

public class OracleUtil {

     /**

      * 根据数据库中你的type将List组装成Array

      * @param con

      * @param OracleObj

      * @param Oraclelist

      * @param objlist

      * @ return

      * @throws Exception

      */

     public static ARRAY getArray( Connection con, String OracleObj, String Oraclelist, List<Object[]> objlist) throws Exception {

         ARRAY array= null ;

         C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();

         OracleConnection connection = (OracleConnection) cp30NativeJdbcExtractor.getNativeConnection(con);

         if (objlist != null && objlist. size () > 0) {

             StructDescriptor structdesc = new StructDescriptor(OracleObj, connection );

             STRUCT[] structs = new STRUCT[objlist. size ()];

             for ( int i = 0; i < objlist. size (); i++) {

                 Object[] result= (Object[]) objlist.get(i);

                 structs[i] = new STRUCT(structdesc, connection , result);

             }

             ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist, connection );

             array = new ARRAY( desc , connection , structs);

         }

         return array;

     }

}

List<Object[]> arrList = new ArrayList<>();

         //解析数据datamap

         for (Map<String, String> dataMap : dataMaps) {

             //创建保存对象

             Object[] objects =new Object[]{

                     dataMap.get( "aac003" ),

                     dataMap.get( "aac002" ),

                     dataMap.get( "aat001" ).trim(),

                     dataMap.get( "aat002" ),

                     dataMap.get( "aat013" ),

                     'admin' ,

                     DateUtil.getCurrentTimeStr(),

                     'ec-20210510-wcdedgk2091' ,

             };

             arrList. add (objects);

         }

//开始调用过程

         long startTime=System.currentTimeMillis();

         Connection connection = null ;

         CallableStatement sqlres = null ;

         String sql = "call SP_HFSZHDA_DOUPLOADAL01(?,?)" ;

         String msg = "" ;

         try {

             connection = dataSource.getConnection();

             ARRAY paramArr = OracleUtil.getArray( connection , "AL01TYPE" , "AL01TYPELIST" ,arrList);

             sqlres = connection .prepareCall(sql);

             sqlres.setArray(1, paramArr);

             sqlres.registerOutParameter(2, Types. VARCHAR );

             sqlres. execute ();

             msg = sqlres.getString(2);

             long endTime=System.currentTimeMillis()-startTime;

             System. out .println( "上传后获取的返回参数为：" +msg+ "||耗时：" +endTime/1000+ "秒" );

         } catch (SQLException e) {

             e.printStackTrace();

         } catch (Exception e) {

             e.printStackTrace();

         }finally {

             try {

                 if (sqlres != null ) {

                     sqlres. close ();

                 }

                 if ( connection != null ) {

                     connection . close ();

                 }

             } catch (SQLException e) {

                 e.printStackTrace();

             }

             if (! "ok" .equals(msg)){

                 throw new BusinessException( "上传失败！" +msg);

             }

         }