存储数据量大 优化-- 存储过程

当业务需要多次访问数据库，进行数据存储时，可以考虑使用存储过程进行数据存储，减少数据库访问次数，既而进行性能优化，提高效率。

例如：

// 处理结果集

List<BOMItemModelSlim> bomItemModels = (List<BOMItemModelSlim>) bomMultExp
.getResultObj();
if (bomItemModels!=null&&bomItemModels.size() > 0) {
// 获取路线
MMRPlantMtl mmr = getMMRPlantMtByBomComponent(material);
String madeLine = "";// 制造路线
String assemLine = "";// 装配路线
if (mmr != null) {
madeLine = mmr.getPagefromat();
assemLine = mmr.getIndustrystnddes();
}

StructDescriptor recDesc = StructDescriptor
.createDescriptor("L_BOM_CHILD", connection);


// 遍历结果集
for (BOMItemModelSlim bomItemModelSlim : bomItemModels) {
Object[] record = new Object[9];
String bomComponent = bomItemModelSlim
.getBOMComponent();// 组件
BigDecimal quantity = bomItemModelSlim.getQuantity();// 组件数量
String isEmpty = bomItemModelSlim.getIsEmpty();// 虚拟件标识
String meterialName = getMeterialNameByMeterial(bomComponent);
record[0] = assemLine;// 工艺路线
record[1] = new BigDecimal("1");// 层级
record[2] = quantity;// 数量
record[3] = isEmpty;// 虚拟件标识
record[4] = madeLine;// 制造路线
record[5] = meterialName;// 物料名称
record[6] = "";// 工位
if (bomParentById == null) {
record[7] = bomParent.getId();
} else {
record[7] = bomParentById.getId();
}
record[8] = bomComponent;// 组件即物料号
STRUCT item = new STRUCT(recDesc, connection, record);
pstruct.add(item);
//1000条执行一次存储过程
if (pstruct.size() > 999) {
ArrayDescriptor tabDesc = ArrayDescriptor
.createDescriptor("T_BOM_CHILD", connection);
ARRAY vArray = new ARRAY(tabDesc, connection,
pstruct.toArray());
stmt = (CallableStatement) connection
.prepareCall("{call PRC_LM_BOMCHILD(?)}");
stmt.setArray(1, vArray);
stmt.execute();
connection.commit();
pstruct.clear();
}
}
ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor(
"T_BOM_CHILD", connection);
ARRAY vArray = new ARRAY(tabDesc, connection,
pstruct.toArray());
stmt = (CallableStatement) connection
.prepareCall("{call PRC_LM_BOMCHILD(?)}");
stmt.setArray(1, vArray);
stmt.execute();
connection.commit();
// operateType=4 表示最终结果 isSuccess=1 为BOM展开成功
// 该日志为结果类型时 参数存入 车型码
saveMessageToLog(material, arg, "4", bom_id, "1", null,
isAuto,planDate);
result = 1;
// System.out.println("result:"+result);
}