A 50,000-Row JDBC Import That Leaks Connections and Commits Partially

The partial load is a transaction-boundary problem, not a parsing problem. With autoCommit=true each executeUpdate is its own transaction, so 12,000 successes are durable and the failure only aborts row 12,001.

Baseline fix for all-or-nothing: conn.setAutoCommit(false), run the whole file, commit() once and rollback() in the catch block. That contradicts the skip-bad-rows requirement, so use savepoints per row (or per small chunk):

try (Connection c = ds.getConnection()) {
  c.setAutoCommit(false);
  Savepoint sp = c.setSavepoint();
  try { insertOrder(c, row); }
  catch (SQLException e) {
    c.rollback(sp);
    failures.add(row.lineNumber() + " -> " + e.getMessage());
  }
  c.commit();
}

Caveats to verify on the actual database: PostgreSQL aborts the entire transaction on any statement error, so the rollback(sp) must happen before continuing; some drivers implicitly commit before DDL; and savepoints cost a round trip each, so benchmark 200,000 rows. If a parent row fails, its ORDER_LINE children must be skipped together - group by order id and use one savepoint per order, not per line. Finally, never toggle autoCommit on a pooled connection without restoring it, because the proxy returns the same physical connection to the next caller.

The time goes into one network round trip per row, a parse/plan per statement, and a commit (with fsync) per row. Three independent fixes:

1. Batch: reuse a single PreparedStatement, addBatch() per row, call executeBatch() every 500–1,000 rows, then clearBatch(). This cuts round trips by orders of magnitude. Avoid one giant batch: the driver buffers parameters in memory and a mid-batch failure is harder to attribute.
2. Reuse the statement and connection: prepare once per batch rather than per row; the server-side statement cache can then help.
3. Commit per batch, not per row: 1,000 rows per commit is a common compromise between throughput and recovery cost.

Batch failure semantics matter. executeBatch can partially succeed. Catch BatchUpdateException, inspect getUpdateCounts(): a driver that returns EXECUTE_FAILED for the offending element lets you map failure to the exact row, but many drivers stop at the first failure and return counts only up to that point. In that case you cannot use a large batch for error recovery - fall back to per-row executeUpdate with a savepoint (slower but attributable), or pre-validate rows and quarantine suspects. Use executeLargeBatch only if a single batch can exceed Integer.MAX_VALUE rows, which is unrealistic here; the more common pitfall is forgetting clearBatch() and re-sending the previous batch.

The timeout is a leak, not a sizing problem: a connection borrowed on a path that throws before close() never returns to the pool. After ten such failures the pool is empty and every caller blocks until the 30-second timeout. Enlarging the pool only delays the failure and increases pressure on the database.

Pattern:

1. try-with-resources on every borrow, declaring resources in creation order so they close in reverse: try (Connection c = ds.getConnection(); PreparedStatement ps = c.prepareStatement(SQL)) { ... }. Closing a Statement closes its ResultSet, so that nesting is sufficient and the connection returns to the pool even when a SQLException propagates. Add a ResultSet to the declaration list only when it is used across method boundaries or you need to inspect it after the statement closes.
2. Suppressed exceptions. If closing a resource throws while a SQLException is in flight, the close exception is attached and retrievable via getSuppressed(). Log both: the primary exception is the business failure, and the suppressed one frequently reveals the real cause, such as a broken connection or a commit failure during rollback.
3. Leak detection. Set HikariConfig.setLeakDetectionThreshold(20000); it logs a stack trace of the borrower that never returned, which is how you find the remaining offending path. Watch pool metrics during the run.
4. Never cache a Connection in a field or a static, and never close the DataSource in the same try-with-resources block as a borrowed connection. For generated keys, request Statement.RETURN_GENERATED_KEYS on the prepared statement and read them immediately after executeUpdate, before the next batch iteration overwrites the result.