使用UCanAccess插入多行到Access时第二次insertRow抛游标无效异常

Great question—this is a common gotcha with JDBC result sets and UCanAccess's behavior around transactions. Let's break down exactly why you're seeing this error and why your two workarounds fix it:

Why the Exception Happens By Default #

When you don't explicitly disable auto-commit (con.setAutoCommit(false)), JDBC operates in auto-commit mode by default. Here's what happens step-by-step:

1. You execute your query to get a result set (rs = st.executeQuery(sql)). Assuming you created your statement with CONCUR_UPDATABLE (which you must have, since the first insertRow() works), this result set is open and ready for modifications.
2. You call insertRow()—since auto-commit is on, this operation immediately triggers a transaction commit under the hood.
3. After the commit, UCanAccess (like most JDBC drivers) invalidates the original result set. The transaction context that the result set was tied to is now closed, so the cursor is no longer open or usable for further modifications.
4. When you try to call insertRow() a second time, the result set is already in an invalid state, hence the ucanaccess SQL Exception: invalid cursor state: identified cursor is not open error.

Why setAutoCommit(false) + commit() Fixes It #

Disabling auto-commit keeps all your operations within a single, active transaction:

• When you turn off auto-commit, calling insertRow() doesn't immediately commit the transaction. The result set remains tied to an open transaction context, so it stays valid and open for subsequent insertRow() calls.
• Once you've inserted all your rows, calling con.commit() finalizes the transaction. This is the cleanest, most efficient approach because you're avoiding unnecessary transaction overhead and keeping your result set alive for the entire batch of inserts.

Why Re-running rs = st.executeQuery(sql) Fixes It #

Each time you execute st.executeQuery(sql), you're creating a brand new, fresh result set:

• After the first insertRow() and auto-commit, the original result set is invalidated. But re-running the query gives you a new, open result set that's ready for modification.
• This works, but it's less efficient than using transactions—you're essentially re-querying the database every time you want to insert a row, which adds unnecessary round-trips and overhead.

Recommended Best Practice #

Stick with the transaction approach (setAutoCommit(false) → multiple insertRow() calls → commit()). It's more performant, aligns with JDBC best practices, and avoids the need to re-execute your query repeatedly.

Just make sure you handle exceptions properly (rollback the transaction if something goes wrong!) to keep your data consistent.

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