通过While循环将表复制到内存H2数据库失败求助

Hey there! Let's figure out why your while loop is skipping and get that DB2 data imported into your in-memory H2 database properly. From what you've described, your connections are working, but the loop just isn't running—let's break down the common issues and fix this step by step.

1. Did you accidentally consume your ResultSet before the loop? #

If you're testing the ResultSet by calling rs.next() or printing a row before your while loop, you've already moved the result set pointer past the first (or only) row. For example:

// This line moves the pointer to the first row
if (rs.next()) {
    System.out.println("Test print: " + rs.getString(1));
}
// Now rs.next() will return false here because we're already at the end
while (rs.next()) {
    // Your import logic never runs
}

Fix: Either move your test print inside the while loop, or call rs.beforeFirst() after testing to reset the pointer to the start of the result set.

2. Double-check your ResultSet loop syntax #

Make sure your loop is using the correct condition to iterate through rows. The only reliable way to traverse a ResultSet is with while (rs.next())—anything else (like while (rs != null)) won't work as expected. This is a super common gotcha!

3. Verify your H2 insertion logic (and handle transactions) #

Since you can write to H2 outside the loop, the issue might be in how you're executing inserts inside the loop. Maybe you're not committing transactions, or your PreparedStatement isn't set up correctly. Here's a robust example that handles batch inserts (way faster for large datasets too):

// Assume you have valid connections for DB2 (db2Conn) and H2 (h2Conn)
try {
    // Disable auto-commit for batch operations (better performance + atomicity)
    h2Conn.setAutoCommit(false);

    String db2Query = "SELECT * FROM MLB.SC_EP";
    // Replace with your actual H2 table columns—match DB2's schema!
    String h2Insert = "INSERT INTO SC_EP (column1, column2, column3) VALUES (?, ?, ?)";

    try (Statement db2Stmt = db2Conn.createStatement();
         ResultSet rs = db2Stmt.executeQuery(db2Query);
         PreparedStatement h2Stmt = h2Conn.prepareStatement(h2Insert)) {

        int batchSize = 1000; // Adjust based on your data volume
        int rowCount = 0;

        // Correct loop to traverse all rows
        while (rs.next()) {
            // Map DB2 columns to H2 parameters—match data types exactly!
            h2Stmt.setString(1, rs.getString("COLUMN1"));
            h2Stmt.setInt(2, rs.getInt("COLUMN2"));
            h2Stmt.setTimestamp(3, rs.getTimestamp("COLUMN3"));

            h2Stmt.addBatch();
            rowCount++;

            // Execute batch when we hit our size limit
            if (rowCount % batchSize == 0) {
                h2Stmt.executeBatch();
                h2Conn.commit();
                System.out.println("Processed " + rowCount + " rows so far...");
            }
        }

        // Process any remaining rows
        if (rowCount % batchSize != 0) {
            h2Stmt.executeBatch();
            h2Conn.commit();
        }

        System.out.println("completed!");
    } catch (SQLException e) {
        // Rollback on failure to avoid partial inserts
        h2Conn.rollback();
        System.err.println("Error during import: " + e.getMessage());
        e.printStackTrace();
    } finally {
        // Restore auto-commit for future operations
        h2Conn.setAutoCommit(true);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Quick Troubleshooting Checks #

• Confirm your ResultSet has data: Add these lines right before the loop to check:

  rs.last();
  System.out.println("Total rows in DB2 result set: " + rs.getRow());
  rs.beforeFirst(); // Reset pointer to start
  

  This will tell you if there actually are rows to process.
• Catch all exceptions inside the loop: If you have try/catch blocks inside the while loop, make sure you're printing the error—otherwise, an exception could silently exit the loop and make it look like it was skipped.
• Match H2 table schema to DB2: If your H2 table has different column names or data types, inserts will fail (and if you're not catching the error, the loop will stop abruptly).

内容的提问来源于stack exchange，提问作者Jamie Craig Glazier