JDBC Batch Insert with Returning Clause

Question

Is there any way to get the values of affected rows using returning clause in JAVA while using JDBC Batch Insert statement? I am able to get the required values of a single row affected.But not for all Batch Inserts?

Code :

try {
    String query = "INSERT INTO temp ( "
                 + "org_node_id, org_node_category_id,  org_node_name, "
                 + "customer_id, created_by, created_date_time, "
                 + "updated_date_time, activation_Status )"
                 + " VALUES (seq_org_node_id.nextval,  11527,  'Abcd',  9756,  1,  sysdate,   sysdate,   'AC')"
    +" returning org_node_id, org_node_name INTO ?, ?";

    con = DBUtils.getOASConnection();

    OraclePreparedStatement ps = (OraclePreparedStatement) con.prepareStatement(query);
    ps.registerReturnParameter(1, Types.INTEGER);
    ps.registerReturnParameter(2, Types.VARCHAR);
    ps.execute();

    ResultSet rs = ps.getReturnResultSet();
    rs.next();

    System.out.println("Org ID : "+ rs.getInt(1));
    System.out.println("Org Name : "+ rs.getString(2));

} catch (SQLException e) {
  e.printStackTrace();
}

Menfolk

please show the code u implemented – Dworman

Yes this is single insert, but how to get the same result for bulk inserts? I am facing problem in this area. – Menfolk

@Menfolk Have a look at this, perhaps of some help. – Sculptor

rs.next(); must be while(rs.next()) to show all resultset – Nabal

If you check chapter 4.6.4 Limitations of DML Returning in the JDBC Developer's Guide docs.oracle.com/en/database/oracle/oracle-database/18/jjdbc/… you see that "DML returning cannot be combined with batch update.". – Evoy

Answer 1

Batching INSERT .. RETURNING statements isn't supported by ojdbc, but bulk insertion can work using PL/SQL's FORALL command.

Given a table...

CREATE TABLE x (
  i INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
  j VARCHAR2(50), 
  k DATE DEFAULT SYSDATE
);

...and types...

CREATE TYPE t_i AS TABLE OF NUMBER(38);
/
CREATE TYPE t_j AS TABLE OF VARCHAR2(50);
/
CREATE TYPE t_k AS TABLE OF DATE;
/

...you can work around this limitation by running a bulk insert, and bulk collecting the results (as I've shown also in this blog post) like this:

try (Connection con = DriverManager.getConnection(url, props);
    CallableStatement c = con.prepareCall(
        "DECLARE "
      + "  v_j t_j := ?; "
      + "BEGIN "
      + "  FORALL j IN 1 .. v_j.COUNT "
      + "    INSERT INTO x (j) VALUES (v_j(j)) "
      + "    RETURNING i, j, k "
      + "    BULK COLLECT INTO ?, ?, ?; "
      + "END;")) {

    // Bind input and output arrays
    c.setArray(1, ((OracleConnection) con).createARRAY(
        "T_J", new String[] { "a", "b", "c" })
    );
    c.registerOutParameter(2, Types.ARRAY, "T_I");
    c.registerOutParameter(3, Types.ARRAY, "T_J");
    c.registerOutParameter(4, Types.ARRAY, "T_K");

    // Execute, fetch, and display output arrays
    c.execute();
    Object[] i = (Object[]) c.getArray(2).getArray();
    Object[] j = (Object[]) c.getArray(3).getArray();
    Object[] k = (Object[]) c.getArray(4).getArray();

    System.out.println(Arrays.asList(i));
    System.out.println(Arrays.asList(j));
    System.out.println(Arrays.asList(k));
}

The results are:

[1, 2, 3]
[a, b, c]
[2018-05-02 10:40:34.0, 2018-05-02 10:40:34.0, 2018-05-02 10:40:34.0]

Endive