A 40-Minute Import and 1,900 Duplicate Rows

try (Connection c = ds.getConnection();
     PreparedStatement ps = c.prepareStatement(
         "INSERT INTO orders(id, sku, qty) VALUES (?,?,?)")) {
    c.setAutoCommit(false);
    try {
        int n = 0;
        for (Order o : orders) {
            ps.setLong(1, o.id());
            ps.setString(2, o.sku());        // O'Brien is data, not SQL
            ps.setInt(3, o.qty());
            ps.addBatch();
            if (++n % 500 == 0) ps.executeBatch();
        }
        ps.executeBatch();
        c.commit();
    } catch (SQLException e) {
        c.rollback();
        throw e;
    }
}

• PreparedStatement - parameters are bound, never parsed as SQL, so quoting bugs disappear; the driver can also reuse a server-side plan.
• addBatch/executeBatch - one round trip per 500 rows instead of 5,000 (this is the latency win).
• setAutoCommit(false) - each INSERT no longer forces its own commit/fsync; that is what turns 40 minutes into seconds, and it makes the run atomic.

Reset autoCommit before returning the connection if the pool does not (Hikari does; verify yours). BatchUpdateException.getUpdateCounts() tells you which rows of a failed batch applied.

With autoCommit=true, each successful executeUpdate was its own committed transaction. The apostrophe aborted the run at row 1,901, but the first 1,900 were already durable; the retry replayed the whole file and inserted them again.

Two independent fixes, and you want both:

1. One transaction per run (above) - a failure rolls back to zero, so a retry starts clean.
2. An idempotent write, in case a run dies between commit and checkpoint: an upsert (MERGE, or INSERT ... ON CONFLICT DO UPDATE on databases that support it), a DELETE by import batch id followed by the inserts, or INSERT ... SELECT ... WHERE NOT EXISTS. A unique constraint on the natural key (sku, order_date) converts silent duplication into a detectable error.

For very large files, commit in batches of a few thousand with a recorded checkpoint, so a retry resumes rather than replays. Logging the failure and continuing without rollback is not a fix: it leaves partial state and hides the defect.

A borrowed pooled connection is owned by the caller, not the DAO. Closing it returns the physical connection to the pool (or closes it), and closing a Connection closes every Statement and ResultSet derived from it - so the still-open ResultSet the caller is iterating becomes invalid, which is exactly the Connection is closed failure seen at random points. Worse, the returned connection may be leased to another thread mid-request.

Correct shape:

• The DAO opens Statement/ResultSet in try-with-resources and never closes the Connection it was handed; the caller (or a try (Connection c = ds.getConnection())) closes it.
• try-with-resources closes in reverse declaration order, so declaring Connection, then PreparedStatement, then ResultSet gives the right teardown. Closing a Statement already closes its ResultSet.
• Never return a ResultSet (or a lazy stream over one) from a method that has already released the connection.

Also keep the connection out of the CSV read loop: acquire, batch, commit, release - do not hold a pooled connection open for the 40 minutes of file I/O.