如何在Java中使用PreparedStatement向多表插入多条记录？

问题描述 #

需要向两个不同数据库表各插入一行数据，如何实现？了解批量执行但理解不深，若可用批量执行，具体该怎么操作？

尝试了如下代码后，收到语法错误提示：

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?, ?, ?, ?, ?, ?)' at line 1

原代码：

/**
 * save data from multiple tables
 */
private void customSave() {
    // create connection
    conn = new DB_Connection().makeConn();

    try {

        // for invoice table
        String product_name = comboProductName.getSelectedItem().toString();
        String date_of_sale = textDateOfSale.getText();
        String paid_on = textPaidOn.getText();
        int quantity = Integer.valueOf(spinnerQuantity.getValue().toString());
        int discount = Integer.valueOf(textDiscount.getText());
        double total_price = Double.valueOf(lblTotal.getText());

        // for customer details table
        String name = textFullName.getText();
        String email = textEmail.getText();
        String mobile = textMobile.getText();
        String address = textAddress.getText();

        stmnt = conn.createStatement();

        // save to invoice
        String sql_invoice = "INSERT INTO `invoice`(`product_name`, `date_of_sale`, `paid_on`, `discount`, `total_price`, `quantity`) "
                + "VALUES (?, ?, ?, ?, ?, ?)";
        ps = conn.prepareStatement(sql_invoice);
        ps.setString(1, product_name);
        ps.setString(2, date_of_sale);
        ps.setString(3, paid_on);
        ps.setInt(4, discount);
        ps.setDouble(5, total_price);
        ps.setInt(6, quantity);

        // save to customer_details
        String sql_customer = "insert into customer_details(customer_name, email, mobile, address )"
                + "values (?, ?, ?, ?)";
        ps = conn.prepareStatement(sql_customer);
        ps.setString(1, name);
        ps.setString(2, email);
        ps.setString(3, mobile);
        ps.setString(4, address);

        if ((!textDateOfSale.getText().equals("")) && (!textPaidOn.getText().equals(""))
                && (!textFullName.getText().equals(""))) {

            int count = ps.executeUpdate(sql_invoice);
            int count1 = ps.executeUpdate(sql_customer);

            if (count > 0 && count1 > 0) {

                JOptionPane.showMessageDialog(null, "Data saved successfully.", "Success!!!",
                        JOptionPane.INFORMATION_MESSAGE);
                textDateOfSale.setText("");
                textPaidOn.setText("");
                textFullName.setText("");
                textEmail.setText("");
                textMobile.setText("");
                textAddress.setText("");
            } else {
                JOptionPane.showMessageDialog(null, "Unable to save data.", "Warning!!!",
                        JOptionPane.WARNING_MESSAGE);
            }
        } else {
            JOptionPane.showMessageDialog(null, "Please fillup the required fields.", "Warning!",
                    JOptionPane.WARNING_MESSAGE);
        }

        stmnt.close();
        conn.close();
    } catch (Exception ex) {
        // JOptionPane.showMessageDialog(null, ex, "Error!", JOptionPane.ERROR_MESSAGE);
        System.out.println(ex.getMessage());
        ex.getStackTrace();
    }
}

错误原因分析 #

1. PreparedStatement被覆盖：创建的两个预编译语句对象中，第二个ps = conn.prepareStatement(sql_customer);直接覆盖了第一个，导致后续执行时用客户表的PreparedStatement处理发票表的SQL，完全不匹配。
2. executeUpdate使用错误：PreparedStatement.executeUpdate()不需要传入SQL参数，传入SQL后方法会退化为Statement的执行逻辑，占位符?不会被预编译替换，直接被当成SQL语法的一部分，引发语法错误。

基础修正版本（非批量） #

分开两个PreparedStatement对象避免覆盖，同时正确调用无参executeUpdate()，并加入事务控制保证数据一致性：

private void customSave() {
    conn = new DB_Connection().makeConn();
    PreparedStatement psInvoice = null;
    PreparedStatement psCustomer = null;

    try {
        // 1. 获取表单数据
        String product_name = comboProductName.getSelectedItem().toString();
        String date_of_sale = textDateOfSale.getText();
        String paid_on = textPaidOn.getText();
        int quantity = Integer.valueOf(spinnerQuantity.getValue().toString());
        int discount = Integer.valueOf(textDiscount.getText());
        double total_price = Double.valueOf(lblTotal.getText());

        String name = textFullName.getText();
        String email = textEmail.getText();
        String mobile = textMobile.getText();
        String address = textAddress.getText();

        // 2. 数据校验
        if (date_of_sale.isEmpty() || paid_on.isEmpty() || name.isEmpty()) {
            JOptionPane.showMessageDialog(null, "请填写必填字段。", "警告!", JOptionPane.WARNING_MESSAGE);
            return;
        }

        // 3. 开启事务（确保两个插入要么都成功，要么都失败）
        conn.setAutoCommit(false);

        // 4. 插入发票表
        String sql_invoice = "INSERT INTO `invoice`(`product_name`, `date_of_sale`, `paid_on`, `discount`, `total_price`, `quantity`) VALUES (?, ?, ?, ?, ?, ?)";
        psInvoice = conn.prepareStatement(sql_invoice);
        psInvoice.setString(1, product_name);
        psInvoice.setString(2, date_of_sale);
        psInvoice.setString(3, paid_on);
        psInvoice.setInt(4, discount);
        psInvoice.setDouble(5, total_price);
        psInvoice.setInt(6, quantity);
        int countInvoice = psInvoice.executeUpdate();

        // 5. 插入客户表
        String sql_customer = "INSERT INTO customer_details(customer_name, email, mobile, address) VALUES (?, ?, ?, ?)";
        psCustomer = conn.prepareStatement(sql_customer);
        psCustomer.setString(1, name);
        psCustomer.setString(2, email);
        psCustomer.setString(3, mobile);
        psCustomer.setString(4, address);
        int countCustomer = psCustomer.executeUpdate();

        // 6. 提交事务
        conn.commit();

        if (countInvoice > 0 && countCustomer > 0) {
            JOptionPane.showMessageDialog(null, "数据保存成功。", "成功!!!", JOptionPane.INFORMATION_MESSAGE);
            // 清空表单
            textDateOfSale.setText("");
            textPaidOn.setText("");
            textFullName.setText("");
            textEmail.setText("");
            textMobile.setText("");
            textAddress.setText("");
        } else {
            JOptionPane.showMessageDialog(null, "无法保存数据。", "警告!!!", JOptionPane.WARNING_MESSAGE);
        }

    } catch (Exception ex) {
        // 回滚事务
        try {
            if (conn != null) conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(ex.getMessage());
        ex.getStackTrace();
        JOptionPane.showMessageDialog(null, "保存失败：" + ex.getMessage(), "错误!", JOptionPane.ERROR_MESSAGE);
    } finally {
        // 关闭资源
        try {
            if (psInvoice != null) psInvoice.close();
            if (psCustomer != null) psCustomer.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

批量执行的实现方式 #

批量执行适合合并执行多条SQL语句，对于不同表的单条插入也可以用该机制，核心是addBatch()添加语句、executeBatch()批量执行，同时配合事务保证一致性：

private void batchSave() {
    conn = new DB_Connection().makeConn();
    Statement stmt = null;

    try {
        // 数据校验
        String date_of_sale = textDateOfSale.getText();
        String paid_on = textPaidOn.getText();
        String name = textFullName.getText();
        if (date_of_sale.isEmpty() || paid_on.isEmpty() || name.isEmpty()) {
            JOptionPane.showMessageDialog(null, "请填写必填字段。", "警告!", JOptionPane.WARNING_MESSAGE);
            return;
        }

        conn.setAutoCommit(false);
        stmt = conn.createStatement();

        // 构造发票表插入SQL
        String product_name = comboProductName.getSelectedItem().toString();
        int quantity = Integer.valueOf(spinnerQuantity.getValue().toString());
        int discount = Integer.valueOf(textDiscount.getText());
        double total_price = Double.valueOf(lblTotal.getText());
        String sqlInvoice = String.format(
            "INSERT INTO `invoice`(`product_name`, `date_of_sale`, `paid_on`, `discount`, `total_price`, `quantity`) VALUES ('%s', '%s', '%s', %d, %.2f, %d)",
            product_name, date_of_sale, paid_on, discount, total_price, quantity
        );
        stmt.addBatch(sqlInvoice);

        // 构造客户表插入SQL
        String email = textEmail.getText();
        String mobile = textMobile.getText();
        String address = textAddress.getText();
        String sqlCustomer = String.format(
            "INSERT INTO customer_details(customer_name, email, mobile, address) VALUES ('%s', '%s', '%s', '%s')",
            name, email, mobile, address
        );
        stmt.addBatch(sqlCustomer);

        // 执行批量操作
        int[] results = stmt.executeBatch();
        conn.commit();

        // 检查执行结果
        boolean allSuccess = true;
        for (int res : results) {
            if (res <= 0) {
                allSuccess = false;
                break;
            }
        }

        if (allSuccess) {
            JOptionPane.showMessageDialog(null, "数据批量保存成功。", "成功!!!", JOptionPane.INFORMATION_MESSAGE);
            // 清空表单
            textDateOfSale.setText("");
            textPaidOn.setText("");
            textFullName.setText("");
            textEmail.setText("");
            textMobile.setText("");
            textAddress.setText("");
        } else {
            JOptionPane.showMessageDialog(null, "部分数据保存失败。", "警告!!!", JOptionPane.WARNING_MESSAGE);
        }

    } catch (Exception ex) {
        try {
            if (conn != null) conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(ex.getMessage());
        ex.getStackTrace();
        JOptionPane.showMessageDialog(null, "批量保存失败：" + ex.getMessage(), "错误!", JOptionPane.ERROR_MESSAGE);
    } finally {
        try {
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

注意事项 #

• SQL注入风险：上述批量示例用String.format拼接SQL存在注入风险，若要安全批量操作，可使用PreparedStatement.addBatch()循环添加参数：

  // 示例：同表批量插入
  String sql = "INSERT INTO `invoice`(`product_name`, `date_of_sale`) VALUES (?, ?)";
  PreparedStatement ps = conn.prepareStatement(sql);
  ps.setString(1, "产品A");
  ps.setString(2, "2024-05-20");
  ps.addBatch();
  
  ps.setString(1, "产品B");
  ps.setString(2, "2024-05-21");
  ps.addBatch();
  
  ps.executeBatch();
  

• 事务控制：无论哪种方式，都要开启事务（关闭autoCommit），插入完成后提交，异常时回滚，避免出现单表插入成功的不一致情况。

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