从Eclipse向Oracle数据库批量插入多行数据的代码问题求助

Let’s walk through the problems in your code and how to fix them:

1. Invalid Multi-Row Insert Syntax for Oracle #

Your SQL uses the VALUES (?), (?) syntax that works for MySQL, but Oracle doesn’t support this format. Trying to run this will throw a syntax error right away.

2. Column Name with Special Character Needs Quoting #

Your column P-CODE contains a hyphen. In Oracle, identifiers with special characters must be wrapped in double quotes—otherwise the database will interpret it as P - CODE (a subtraction operation), leading to an error. So you need to write it as "P-CODE".

3. Incomplete & Incorrect Batch Insert Implementation #

You’re trying to insert multiple rows in one go, but your current approach isn’t the right way to handle batch inserts with JDBC for Oracle. Instead, you should use the addBatch() and executeBatch() methods of PreparedStatement for cleaner, supported batch operations.

Fixed Code Example #

Here’s how to adjust your code to work properly:

// Corrected SQL with quoted column name for Oracle's special character handling
String SQL = "INSERT INTO INFO(ORDER_NO, ITEM_NAME, SERIAL_NO, \"P-CODE\", QTY, RATE, TOTAL) VALUES(?, ?, ?, ?, ?, ?, ?)";

try {
    PreparedStatement pst = con.prepareStatement(SQL);
    
    // Add first row to batch
    pst.setString(1, order.getText());
    pst.setString(2, item1.getSelectedItem().toString());
    pst.setString(3, serial1.getSelectedItem().toString());
    pst.setString(4, code1.getSelectedItem().toString());
    pst.setInt(5, qty1Value); // Replace with your actual QTY value
    pst.setBigDecimal(6, rate1Value); // Replace with your actual RATE value
    pst.setBigDecimal(7, total1Value); // Replace with your actual TOTAL value
    pst.addBatch();
    
    // Add second row to batch
    pst.setString(1, order.getText()); // Adjust if ORDER_NO differs for the second row
    pst.setString(2, item2.getSelectedItem().toString());
    pst.setString(3, serial2.getSelectedItem().toString());
    pst.setString(4, code2.getSelectedItem().toString());
    pst.setInt(5, qty2Value);
    pst.setBigDecimal(6, rate2Value);
    pst.setBigDecimal(7, total2Value);
    pst.addBatch();
    
    // Execute all batch inserts
    int[] affectedRows = pst.executeBatch();
    con.commit(); // Don't forget to commit if auto-commit is disabled
    
} catch (SQLException e) {
    e.printStackTrace();
    con.rollback(); // Rollback on error to maintain data integrity
} finally {
    // Clean up resources properly
    if (pst != null) pst.close();
    if (con != null) con.close();
}

Alternative: Using Oracle's INSERT ALL Syntax #

If you prefer a single SQL statement for multiple rows, you can use Oracle's native INSERT ALL syntax:

String SQL = "INSERT ALL " +
             "INTO INFO(ORDER_NO, ITEM_NAME, SERIAL_NO, \"P-CODE\", QTY, RATE, TOTAL) VALUES(?, ?, ?, ?, ?, ?, ?) " +
             "INTO INFO(ORDER_NO, ITEM_NAME, SERIAL_NO, \"P-CODE\", QTY, RATE, TOTAL) VALUES(?, ?, ?, ?, ?, ?, ?) " +
             "SELECT 1 FROM DUAL";

try {
    PreparedStatement pst = con.prepareStatement(SQL);
    // Set parameters for first row (positions 1-7)
    pst.setString(1, order.getText());
    pst.setString(2, item1.getSelectedItem().toString());
    // ... fill in remaining first-row parameters
    // Set parameters for second row (positions 8-14)
    pst.setString(8, order.getText());
    pst.setString(9, item2.getSelectedItem().toString());
    // ... fill in remaining second-row parameters
    
    int affectedRows = pst.executeUpdate();
    con.commit();
} catch (SQLException e) {
    e.printStackTrace();
    con.rollback();
}

Note that INSERT ALL has limitations (like not supporting RETURNING clauses), so the addBatch() approach is generally more flexible for JDBC batch operations.

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