主要方式为通过存储过程来处理大批量数据;
效率不错,十万条数据用时60s左右,测试时间包括TypeHandler中处理数据、数据传输、数据处理,
示例记录如下。
参考资料
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>