JDBC批量插入如何通过RETURNING INTO获取插入行ID列表？

Absolutely! You can retrieve all generated IDs from a bulk insert operation using Oracle's RETURNING INTO clause, but your current implementation won't capture all IDs because each PL/SQL block only handles a single insert and binds to a single output variable. Here are two solid approaches to solve this:

Approach 1: Use PL/SQL FORALL for Efficient Bulk Insert (Recommended) #

This method is far more efficient than looping single inserts, as it performs the bulk insert in one database round-trip and collects all IDs at once.

First, adjust your SQL to use a PL/SQL block with bulk operations:

DECLARE
  -- Define a collection type for the returned IDs
  TYPE id_collection IS TABLE OF x.a%TYPE;
  v_generated_ids id_collection;

  -- Define collection types for your input parameters (match column types)
  TYPE b_collection IS TABLE OF x.b%TYPE;
  TYPE c_collection IS TABLE OF x.c%TYPE;
  TYPE d_collection IS TABLE OF x.d%TYPE;
  -- ... Add similar types for columns e through m

  -- Bind input arrays from Java
  v_b_values b_collection := :b_list;
  v_c_values c_collection := :c_list;
  v_d_values d_collection := :d_list;
  -- ... Bind the rest of your input parameter arrays

BEGIN
  -- Bulk insert using FORALL
  FORALL i IN 1..v_b_values.COUNT
    INSERT INTO x (a, b, c, d, e, f, g, h, i, j, k, l, m)
    VALUES (sequence.nextVal, v_b_values(i), v_c_values(i), v_d_values(i), 
            :e_list(i), :f_list(i), :g_list(i), :h_list(i), :i_list(i), 
            :j_list(i), :k_list(i), :l_list(i), :m_list(i))
    RETURNING a BULK COLLECT INTO v_generated_ids;

  -- Pass the collected IDs back to Java
  :result_ids := v_generated_ids;
END;

Note: You'll need to create the corresponding SQL collection types in your Oracle database first, like:

CREATE TYPE id_collection AS TABLE OF NUMBER;
CREATE TYPE b_collection AS TABLE OF [your_b_column_type];
-- ... Create types for other input parameters

Then in your Java code, use CallableStatement to pass arrays and retrieve the generated IDs:

List<Long> generatedIds = new ArrayList<>();
Connection conn = ...; // Get your database connection

// Prepare the callable statement
String bulkQuery = "DECLARE ... "; // Use the PL/SQL block above
CallableStatement cs = conn.prepareCall(bulkQuery);

// Prepare input arrays (example for column b; repeat for other columns)
int count = 5; // Number of rows to insert
Long[] bValues = new Long[count];
for (int i = 0; i < count; i++) {
  bValues[i] = yourBValue; // Replace with actual value
}

// Register Oracle array descriptors (match the SQL types you created)
ArrayDescriptor bDesc = ArrayDescriptor.createDescriptor("B_COLLECTION", conn);
ArrayDescriptor idDesc = ArrayDescriptor.createDescriptor("ID_COLLECTION", conn);

// Set input array parameters
cs.setArray("b_list", new ARRAY(bDesc, conn, bValues));
// ... Set other input array parameters (c_list, d_list, etc.)

// Register the output array parameter
cs.registerOutParameter("result_ids", OracleTypes.ARRAY, "ID_COLLECTION");

// Execute the bulk operation
cs.execute();

// Retrieve the generated IDs
ARRAY idArray = (ARRAY) cs.getObject("result_ids");
Long[] ids = (Long[]) idArray.getArray();
generatedIds.addAll(Arrays.asList(ids));

// Cleanup resources
cs.close();
conn.close();

Approach 2: Collect OUT Parameters from Batched Single Inserts #

If you prefer to keep your existing single-insert PL/SQL block, you can adjust your Java code to collect the OUT parameter from each batch entry. This is less efficient but works with minimal changes to your SQL.

Keep your original PL/SQL block:

DECLARE 
  resultId NUMBER; 
BEGIN 
  INSERT INTO x (a, b, c, d, e, f, g, h, i, j, k, l, m) 
  VALUES (sequence.nextVal, :a, :b, :c, :d, :e, :f, :g, :h, :i, :j, :k, :l) 
  RETURNING a INTO :resultId;
END;

Modify your Java code to retrieve each OUT parameter after batch execution:

List<Long> generatedIds = new ArrayList<>();
Connection conn = ...; // Get your connection
CallableStatement cs = conn.prepareCall(QUERY_FOR_SAVE);

// Add batches
IntStream.range(0, count).forEach(index -> {
  try {
    // Set parameters for each insert
    cs.setString("a", yourAValue);
    cs.setLong("b", yourBValue);
    // ... Set other parameters (c through l)
    cs.addBatch();
  } catch (SQLException e) {
    e.printStackTrace();
  }
});

// Execute the batch
cs.executeBatch();

// Retrieve each generated ID
for (int i = 0; i < count; i++) {
  try {
    // Move to the next batch result
    cs.getMoreResults();
    // Get the OUT parameter value
    Long id = cs.getLong("resultId");
    generatedIds.add(id);
  } catch (SQLException e) {
    e.printStackTrace();
  }
}

// Cleanup
cs.close();
conn.close();

Important: This approach relies on Oracle's JDBC driver supporting retrieval of multiple OUT parameters from a batch. It's less efficient than the FORALL method because it performs multiple database round-trips.

内容的提问来源于stack exchange，提问作者snowflake