Lost data when using addBatch java

Question

I using executeBatch to insert data but some data have lost, can't not found in database, no found any exception.
DB: Oracle.
Driver version: 11.2.0.4.0
Pool: UCP: Oracle Universal Connection Pool
Pls help.

Here my code:

    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);
    }
}

Here is my log:

    [[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

table description:

  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)

so does your multilog contain all the write statements for items? — eis
that's not what I asked. Does the log contain all your batch entries? — eis
But does that log contain all the messages you expect - how many records are you expected to be created, how many 'add Batch' messages do you get, and how many rows are actually inserted? We don't know anything about your data or expected or actual results. Have you got duplicate fieldName values, perhaps, and each is only being inserted once when you expect several of each? — Alex Poole
Does the log contain all your batch entries? --> Yes, I have 7 records on logs, but I found only 3 on db. — Vũ Nguyễn

halfbit halfbit · Accepted Answer · 2016-04-13T21:03:30

Just one more thing you might want to check: Examine the int[] returned by executeBatch. Although I can not verify this now, I think I experienced unexpected behavior of some older Oracle JDBC driver (which version do you use?) with executeBatch in the past (~10y). It might not throw any exception although some statements failed. Scan the array for EXECUTE_FAILED/SUCCESS_NO_INFO entries (see linked JavaDoc).

(If I remember right, then you might even get an array of all EXECUTE_FAILED if just one batch statement failed, i.e. you might not even get a hint at which of the batch statements failed and which worked. In that case you would have to rollback and split down the batches to sort out the single failing ones (and execute them non-batch so you get a proper exception and error message). Admittedly, this is over-complicated for a moderate number of data-sets.)

Lost data when using addBatch java

1 Answers