如何在带有bean的jdbc准备好的语句中使用INSERT INTO ALL语句

Question

请给我一些例子，说明如何在 jsf bean 内的 jdbc 准备好的语句中使用“INSERT INTO ALL STATEMENT”？

实际上，我想使用单个 jsf 页面并为每个员工 ID 使用一个文本框来获取当前员工的员工 ID。

我如何使用 INSERT INTO ALL 语句来实现此目的？

以下是我的代码片段。

出勤Bean.java：

public class AttendanceBean {
private int atteid;                    
    private String attdname; 
private int attday;
private int attmonth;
private int attyear;

    public static Connection getAttConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:globldb3";
    String username = "scott";
    String password = "tiger";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
public String addAttendance(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    boolean committed = false;
try {
    conn = getAttConnection();
    conn.setAutoCommit(false);
    String query = "INSERT ALL INTO attendance VALUES (?,?,?,?,?)";
    pstmt = conn.prepareStatement(query); 
    pstmt.setInt(1,this.atteid); 
    pstmt.setString(2,this.attdname);
    pstmt.setInt(3,this.attday);
    pstmt.setInt(4,this.attmonth);
    pstmt.setInt(5,this.attyear);
            pstmt.executeUpdate();
        conn.commit();
        conn.setAutoCommit(true);
        committed = true;
    return "home.xhtml";
    } catch (Exception e) {
        e.printStackTrace();
        return "CRM.xhtml";
    }   finally {
            try{
                if (!committed) conn.rollback();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
      }
    }    

Answer 1

多重插入的正确 SQL 语法是：

INSERT INTO 
    tbl (col1, col2, col3) 
VALUES
    (val1a, val2a, val3a),
    (val1b, val2b, val3b),
    (val1c, val2c, val3c),
    ...

但是，在 JDBC 中，您最好使用 PreparedStatement#addBatch() 在循环中，后跟 executeBatch() 进行多重插入。这是一个启动示例：

private static final String SQL_INSERT = "INSERT INTO tbl (col1, col2, col3) VALUES (?, ?, ?)";

public void save(List<Entity> entities) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement(SQL_INSERT);

        for (Entity entity : entities) {
            statement.setObject(1, entity.getCol1());
            statement.setObject(2, entity.getCol2());
            statement.setObject(3, entity.getCol3());
            statement.addBatch();
        }

        statement.executeBatch();
    } finally {
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }
}