Java中add方法可添加至列表但无法写入SQL Server数据库

我需要将Player对象同时添加至allPlayers列表与SQL Server数据库的PlayerMAP表中，目前列表添加功能正常，但数据库表无任何数据变更。使用SSMS查看PlayerMAP表时，未发现新增的Player数据。

相关代码 #

public class PlayerRepositoryJDBC {

    private Connection connection;
    private ArrayList<Player> allPlayers = new ArrayList<>();


    public ArrayList<Player> getAllPlayers() {
        return allPlayers;
    }

    public void setAllPlayers(ArrayList<Player> allPlayers) {
        this.allPlayers = allPlayers;
    }

    public PlayerRepositoryJDBC() throws SQLException {
        String connectionURL = "jdbc:sqlserver://localhost:52448;databaseName=MAP;user=user1;password=1234;encrypt=true;trustServerCertificate=true";
        try {
            System.out.print("Connecting to the server......");
            try (Connection connection = DriverManager.getConnection(connectionURL)) {
                System.out.println("Connected to the Server.");

                Statement select = connection.createStatement();
                ResultSet resultSet = select.executeQuery("SELECT * FROM PlayerMAP");
                int count = 0;
                while (resultSet.next()) {
                    count++;
                }
                if (count > 0) {
                    ResultSet result = select.executeQuery(" SELECT * FROM PlayerMAP");
                    while (result.next()) {
                        Player player = new Player
                                (result.getString("id"), result.getString("firstName"), result.getString("lastName"), result.getInt("age"), result.getString("nationality"), result.getString("position"), result.getInt("marketValue"));
                        allPlayers.add(player);
                    }
                } else {
                    String insert_string = "INSERT INTO PlayerMAP(id, firstName, lastName, age, nationality, position, marketValue) VALUES ('P1','Dan','Mic',18,'Romania','Forward',1250)";
                    Statement insert = connection.createStatement();
                    insert.executeUpdate(insert_string);
                    Player player=new Player("P1","Dan","Mic",18,"Romania","Forward",1250);
                    allPlayers.add(player);
                }
                //System.out.println(allPlayers.size());

            } catch (Exception e) {
                System.out.println("I am not connected to the Server");
                e.printStackTrace();
            }

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void add(Player entity) throws SQLException {
        allPlayers.add(entity);
        String id=entity.getId();
        String firstName=entity.getFirstName();
        String lastName=entity.getLastName();
        int age = entity.getAge();
        String nationality= entity.getNationality();
        String position=entity.getPosition();
        int marketValue=entity.getMarketValue();
        String insert_string = "INSERT INTO PlayerMAP(id, firstName, lastName, age, nationality, position, marketValue) VALUES ('"+id+"', '"+firstName+"', '"+lastName+"', '"+age+"', '"+nationality+"', '"+position+"', '"+marketValue+"')";

        Statement insert = this.connection.createStatement();
        insert.executeUpdate(insert_string);

    }
}

问题原因分析 #

1. 成员变量connection未初始化：构造方法中使用try-with-resources创建的Connection是局部变量，并未赋值给类的成员变量this.connection。导致add方法调用this.connection.createStatement()时，connection为null，若未捕获异常则会静默失败。
2. SQL拼接错误与注入风险：直接拼接字符串生成SQL，不仅存在SQL注入风险，还可能因特殊字符、类型不匹配导致SQL执行失败。
3. 连接资源管理不当：try-with-resources会在代码块结束后自动关闭连接，导致后续add方法无法复用有效连接。

解决方案 #

1. 正确初始化成员变量connection #

修改构造方法，将创建的连接赋值给成员变量，同时调整资源管理逻辑：

public PlayerRepositoryJDBC() throws SQLException {
    String connectionURL = "jdbc:sqlserver://localhost:52448;databaseName=MAP;user=user1;password=1234;encrypt=true;trustServerCertificate=true";
    try {
        System.out.print("Connecting to the server......");
        // 赋值给成员变量，避免try-with-resources自动关闭连接
        this.connection = DriverManager.getConnection(connectionURL);
        System.out.println("Connected to the Server.");

        Statement select = this.connection.createStatement();
        ResultSet resultSet = select.executeQuery("SELECT * FROM PlayerMAP");
        int count = 0;
        while (resultSet.next()) {
            count++;
        }
        if (count > 0) {
            ResultSet result = select.executeQuery(" SELECT * FROM PlayerMAP");
            while (result.next()) {
                Player player = new Player
                        (result.getString("id"), result.getString("firstName"), result.getString("lastName"), result.getInt("age"), result.getString("nationality"), result.getString("position"), result.getInt("marketValue"));
                allPlayers.add(player);
            }
        } else {
            String insert_string = "INSERT INTO PlayerMAP(id, firstName, lastName, age, nationality, position, marketValue) VALUES ('P1','Dan','Mic',18,'Romania','Forward',1250)";
            Statement insert = this.connection.createStatement();
            insert.executeUpdate(insert_string);
            Player player=new Player("P1","Dan","Mic",18,"Romania","Forward",1250);
            allPlayers.add(player);
        }
    } catch (Exception e) {
        System.out.println("I am not connected to the Server");
        e.printStackTrace();
        // 连接创建失败时关闭已打开的连接
        if (this.connection != null) {
            try {
                this.connection.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        throw new RuntimeException(e);
    }
}

2. 使用PreparedStatement优化SQL操作 #

替换字符串拼接为预编译语句，避免注入风险与类型错误：

public void add(Player entity) throws SQLException {
    allPlayers.add(entity);
    String sql = "INSERT INTO PlayerMAP(id, firstName, lastName, age, nationality, position, marketValue) VALUES (?, ?, ?, ?, ?, ?, ?)";
    
    try (PreparedStatement pstmt = this.connection.prepareStatement(sql)) {
        pstmt.setString(1, entity.getId());
        pstmt.setString(2, entity.getFirstName());
        pstmt.setString(3, entity.getLastName());
        pstmt.setInt(4, entity.getAge());
        pstmt.setString(5, entity.getNationality());
        pstmt.setString(6, entity.getPosition());
        pstmt.setInt(7, entity.getMarketValue());
        
        int affectedRows = pstmt.executeUpdate();
        // 打印受影响行数验证执行结果
        System.out.println("插入成功，受影响行数：" + affectedRows);
    } catch (SQLException e) {
        e.printStackTrace();
        throw e;
    }
}

3. 添加连接关闭方法 #

避免连接泄漏，添加资源清理方法：

public void close() throws SQLException {
    if (this.connection != null && !this.connection.isClosed()) {
        this.connection.close();
    }
}

4. 规范调用逻辑 #

确保调用时正确处理异常，及时发现问题：

// 调用示例
try (PlayerRepositoryJDBC repo = new PlayerRepositoryJDBC()) {
    Player newPlayer = new Player("P2", "John", "Doe", 22, "USA", "Midfielder", 2500);
    repo.add(newPlayer);
} catch (SQLException e) {
    e.printStackTrace();
}

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