使用JDBC PreparedStatement安全高效地更新数据库记录

String sql = "UPDATE player SET" +
             " first_name= '" + first_name + "'," +
             " last_name= '" + last_name + "'," +
             " address= '" + address + "'," +
             " postal_code= '" + postal_code + "'," +
             " province= '" + province + "'," +
             " phone_number= '" + phone_number + "'" +
             " WHERE player_id =" + searchP_id + ";";
statement.executeUpdate(sql);

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.swing.JOptionPane; // 用于模拟用户输入

public class PlayerRecordUpdater {

    // 假设这是一个获取数据库连接的方法
    // 在实际应用中，通常会使用连接池管理连接
    private static Connection getConnection() throws SQLException {
        // 请替换为你的数据库连接信息
        String url = "jdbc:oracle:thin:@localhost:1521:XE"; // 示例：Oracle数据库URL
        String user = "your_username";
        String password = "your_password";

        // 加载JDBC驱动 (对于新版本JDBC驱动通常不需要显式加载)
        // Class.forName("oracle.jdbc.driver.OracleDriver"); // 如果需要

        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 更新玩家记录的方法
     * @param playerId 要更新的玩家ID
     * @param firstName 玩家名
     * @param lastName 玩家姓
     * @param address 地址
     * @param postalCode 邮政编码
     * @param province 省份
     * @param phoneNumber 电话号码
     * @throws SQLException 如果数据库操作失败
     */
    public void updatePlayerRecord(int playerId, String firstName, String lastName,
                                   String address, String postalCode, String province,
                                   String phoneNumber) throws SQLException {

        // 定义带有占位符的SQL UPDATE语句
        String sql = "UPDATE player SET " +
                     "first_name = ?, " +
                     "last_name = ?, " +
                     "address = ?, " +
                     "postal_code = ?, " +
                     "province = ?, " +
                     "phone_number = ? " +
                     "WHERE player_id = ?";

        // 使用try-with-resources自动关闭PreparedStatement和Connection
        try (Connection connection = getConnection(); // 获取连接
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            // 设置参数，参数索引从1开始
            preparedStatement.setString(1, firstName);
            preparedStatement.setString(2, lastName);
            preparedStatement.setString(3, address);
            preparedStatement.setString(4, postalCode);
            preparedStatement.setString(5, province);
            preparedStatement.setString(6, phoneNumber);
            preparedStatement.setInt(7, playerId); // 注意：这里使用setInt()，匹配数据库中的整数类型

            // 执行更新操作
            int rowsAffected = preparedStatement.executeUpdate();

            System.out.println("更新完成。受影响的行数: " + rowsAffected);
            if (rowsAffected > 0) {
                JOptionPane.showMessageDialog(null, "玩家记录更新成功！");
            } else {
                JOptionPane.showMessageDialog(null, "未找到匹配的玩家ID，或数据无变化。");
            }

        } catch (SQLException e) {
            System.err.println("数据库更新错误: " + e.getMessage());
            e.printStackTrace();
            throw e; // 重新抛出异常，以便调用者处理
        }
    }

    public static void main(String[] args) {
        PlayerRecordUpdater updater = new PlayerRecordUpdater();

        try {
            // 模拟从用户界面获取输入
            String inputId = JOptionPane.showInputDialog(null, "请输入要更新的玩家ID:");
            if (inputId == null || inputId.trim().isEmpty()) {
                JOptionPane.showMessageDialog(null, "玩家ID不能为空！");
                return;
            }
            int searchP_id = Integer.parseInt(inputId);

            String newFirstName = JOptionPane.showInputDialog(null, "请输入新的玩家名:");
            String newLastName = JOptionPane.showInputDialog(null, "请输入新的玩家姓:");
            String newAddress = JOptionPane.showInputDialog(null, "请输入新的地址:");
            String newPostalCode = JOptionPane.showInputDialog(null, "请输入新的邮政编码:");
            String newProvince = JOptionPane.showInputDialog(null, "请输入新的省份:");
            String newPhoneNumber = JOptionPane.showInputDialog(null, "请输入新的电话号码:");

            // 调用更新方法
            updater.updatePlayerRecord(searchP_id, newFirstName, newLastName,
                                       newAddress, newPostalCode, newProvince,
                                       newPhoneNumber);

        } catch (NumberFormatException e) {
            JOptionPane.showMessageDialog(null, "玩家ID必须是有效的数字！");
            System.err.println("输入ID格式错误: " + e.getMessage());
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "数据库操作失败，请查看控制台日志。");
            // 异常已在updatePlayerRecord方法中打印
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "发生未知错误: " + e.getMessage());
            e.printStackTrace();
        }
    }
}