记录通过mybatis处理大批量oracle数据

最新推荐文章于 2024-04-22 21:29:08 发布

sow2018

最新推荐文章于 2024-04-22 21:29:08 发布

阅读量766

点赞数

分类专栏： mybatis 文章标签： oracle mybatis 存储

本文链接：https://blog.csdn.net/lxlstronger/article/details/39371081

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

主要方式为通过存储过程来处理大批量数据；

效率不错，十万条数据用时60s左右，测试时间包括TypeHandler中处理数据、数据传输、数据处理，

示例记录如下。

参考资料

http://code.google.com/p/mybatis/source/browse/trunk/src/test/java/org/apache/ibatis/submitted/sptests/?r=3394

http://blog.csdn.net/kkdelta/article/details/7226331

1，oracle数据类型定义

<pre class="sql" name="code">CREATE OR REPLACE TYPE BASE_DATA AS OBJECT (  
      col1                VARCHAR2(40)
);

2，定义数据类型的数组集合

CREATE OR REPLACE TYPE TABLE_OF_BASE AS TABLE OF BASE_DATA;

3，创建存储过程

CREATE OR REPLACE procedure data_pro(base_datas IN TABLE_OF_BASE) as
  BASEDATA BASE_DATA;
begin
  FOR idx IN base_datas.first() .. base_datas.last() LOOP
    BASEDATA := base_datas(idx);
    INSERT INTO TESTTABLE
      (
       bstnk)
    VALUES
      (
       B2CDATA.bstnk);
  end loop;
exception
  when others then
    rollback;
    raise;
end;

4，定义TypeHandler

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.List;

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

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

import com.haier.openplatform.alm.radar.domain.RRSLesJDMSG;

public class ArrayTypeHandler implements TypeHandler<Object> {
 public RadarL0TypeHandler() {
        super();
    }
    public void setParameter(PreparedStatement ps, int i, Object parameter,
            JdbcType jdbcType) throws SQLException {
     List<RRSLesJDMSG> objects = (List<RRSLesJDMSG>) parameter;

        StructDescriptor structDescriptor = StructDescriptor.createDescriptor("BASE_DATA", ps.getConnection());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        STRUCT[] structs = new STRUCT[objects.size()];
        for (int index = 0; index < objects.size(); index++)
        {
         RRSLesJDMSG pack = objects.get(index);
            Object[] params = new Object[1];
            params[0] = pack.getBstnk();
            
            STRUCT struct = new STRUCT(structDescriptor, ps.getConnection(), params);
            structs[index] = struct;
        }

        ArrayDescriptor desc = ArrayDescriptor.createDescriptor("TABLE_OF_BASE", ps.getConnection());
        oracle.sql.ARRAY oracleArray = new oracle.sql.ARRAY(desc, ps.getConnection(), structs);
        ps.setArray(i, oracleArray);
    }
    public Object getResult(ResultSet rs, String columnName) throws SQLException {
        Array array = rs.getArray(columnName);
        return array.getArray();
    }
    @Override
    public Object getResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        Array array = cs.getArray(columnIndex);
        return array.getArray();
    }
 @Override
 public Object getResult(ResultSet rs, int columnIndex) throws SQLException {
  // TODO Auto-generated method stub
  return null;
 } 

}

5,设置mapper.xml文件

<update id="testDataList" parameterType="java.util.Map" statementType="CALLABLE">
  <![CDATA[
      {call batchInsertB2C(#{datalist,mode=IN,jdbcType=ARRAY,typeHandler=com.quartz.ArrayTypeHandler})}
     ]]>
 </update>