我使用executeBatch插入数据但是有些数据丢失了,在数据库中找不到,没有发现任何异常 .
DB:Oracle .
驱动程序版本:11.2.0.4.0
池:UCP:Oracle通用连接池
请帮忙 .
这是我的代码:
protected static void insertExtraData(String serviceCode, JsonObject extras, PaymentRequest request) {
PreparedStatement ps = null;
long tid = request.getTransId();
long coreId = request.getCoreTransId();
Connection con = null;
try {
con = ConnectionFactory.getConnection();
con.setAutoCommit(false);
ps = con.prepareStatement("INSERT INTO REPORT_ADMIN.SHOPPING_TRANS_DATA VALUES(?,?,?)");
MultiLog.write(serviceCode, tid, coreId, "INSERT INTO REPORT_ADMIN.SHOPPING_TRANS_DATA VALUES(?,?,?)");
Set<String> fieldNames = extras.getFieldNames();
for (String fieldName : fieldNames) {
MultiLog.write(serviceCode, tid, coreId, "para 01: " + tid);
MultiLog.write(serviceCode, tid, coreId, "para 02: " + fieldName);
MultiLog.write(serviceCode, tid, coreId, "para 03: " + extras.getString(fieldName));
ps.setString(1, String.valueOf(tid));
ps.setString(2, Utils.nullToEmpty(fieldName));
ps.setString(3, Utils.nullToEmpty(extras.getString(fieldName)));
MultiLog.write(serviceCode, tid, coreId, "add Batch");
ps.addBatch();
}
ps.executeBatch();
con.commit();
}
catch (Exception e) {
MultiLog.error(serviceCode, tid, "ERROR", e);
}
finally {
Utils.close(ps);
Utils.close(con);
}
}
这是我的日志:
[[1460430555111][698905167]]---INSERT INTO REPORT_ADMIN.SHOPPING_TRANS_DATA VALUES(?,?,?)
[[1460430555111][698905167]]---para 01: 1460430555111
[[1460430555111][698905167]]---para 02: TransId_Ref
[[1460430555111][698905167]]---para 03: 1460430539304
[[1460430555111][698905167]]---add Batch
[[1460430555111][698905167]]---para 01: 1460430555111
[[1460430555111][698905167]]---para 02: ContractNo
[[1460430555111][698905167]]---para 03: 207-0003
[[1460430555111][698905167]]---add Batch
[[1460430555111][698905167]]---para 01: 1460430555111
[[1460430555111][698905167]]---para 02: IdCardNumber
[[1460430555111][698905167]]---para 03: ***9272
[[1460430555111][698905167]]---add Batch
[[1460430555111][698905167]]---para 01: 1460430555111
[[1460430555111][698905167]]---para 02: CustomerName
[[1460430555111][698905167]]---para 03: pa
[[1460430555111][698905167]]---add Batch
[[1460430555111][698905167]]---para 01: 1460430555111
[[1460430555111][698905167]]---para 02: CompanyName
[[1460430555111][698905167]]---para 03: FC
[[1460430555111][698905167]]---add Batch
[[1460430555111][698905167]]---para 01: 1460430555111
[[1460430555111][698905167]]---para 02: RefNo
[[1460430555111][698905167]]---para 03: MS1460430555111
[[1460430555111][698905167]]---add Batch
[[1460430555111][698905167]]---para 01: 1460430555111
[[1460430555111][698905167]]---para 02: IdentifyInfo
[[1460430555111][698905167]]---para 03: TE_at
[[1460430555111][698905167]]---add Batch
表格说明:
CREATE TABLE "REPORT_ADMIN"."SHOPPING_TRANS_DATA"
(
"TID" NUMBER NOT NULL ENABLE,
"KEY" VARCHAR2(50 BYTE),
"VALUE" VARCHAR2(500 BYTE),
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1 回答
还有一件事你可能要检查:检查executeBatch返回的
int[]
. 虽然我现在无法验证这一点,但我认为我在过去(~10y)遇到过executeBatch
的旧版Oracle JDBC驱动程序(你使用的是哪个版本?)的意外行为 . 虽然有些陈述失败,但它可能不会抛出任何异常 . 扫描数组以查找EXECUTE_FAILED
/SUCCESS_NO_INFO
条目(请参阅链接的JavaDoc) .(如果我没记错的话,如果只有一个批处理语句失败,你甚至可能得到所有
EXECUTE_FAILED
的数组,即你甚至可能没有得到哪个批处理语句失败以及哪个有效的提示 . 在这种情况下你将不得不回滚并拆分批处理以排除单个失败的批处理(并以非批处理方式执行它们以便获得正确的异常和错误消息) . 不可否认,对于中等数量的数据集来说,这是过于复杂的 . )