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

来源：互联网发布：店铺淘宝客怎么做编辑：程序博客网时间：2024/05/16 16:17

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

效率不错，十万条数据用时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>

0 0