解决Java JDBC数据插入SQL Server无响应问题：深度教程

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PlayerRepositoryJDBC {

    private Connection connection; // 类成员Connection，用于持久化连接
    private ArrayList<Player> allPlayers = new ArrayList<>();

    // 假设Player类已定义，并有相应的getter方法

    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......");
            // 初始化类成员connection
            this.connection = DriverManager.getConnection(connectionURL);
            System.out.println("Connected to the Server.");

            // 禁用自动提交，手动管理事务
            this.connection.setAutoCommit(false); 

            // 初始加载数据
            loadPlayersFromDatabase();

            this.connection.commit(); // 提交初始加载的事务
        } catch (SQLException e) {
            System.err.println("Failed to connect to the Server or load initial data.");
            e.printStackTrace();
            if (this.connection != null) {
                try {
                    this.connection.rollback(); // 发生异常时回滚
                    this.connection.close(); // 关闭连接
                } catch (SQLException ex) {
                    System.err.println("Error during rollback or close: " + ex.getMessage());
                }
            }
            throw e; // 重新抛出异常，让调用者知道初始化失败
        }
    }

    private void loadPlayersFromDatabase() throws SQLException {
        String selectSql = "SELECT id, firstName, lastName, age, nationality, position, marketValue FROM PlayerMAP";
        try (Statement statement = this.connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSql)) {

            while (resultSet.next()) {
                Player player = new Player(
                        resultSet.getString("id"),
                        resultSet.getString("firstName"),
                        resultSet.getString("lastName"),
                        resultSet.getInt("age"),
                        resultSet.getString("nationality"),
                        resultSet.getString("position"),
                        resultSet.getInt("marketValue")
                );
                allPlayers.add(player);
            }
            System.out.println("Loaded " + allPlayers.size() + " players from database.");

            // 如果数据库为空，插入一个默认玩家
            if (allPlayers.isEmpty()) {
                Player defaultPlayer = new Player("P1", "Dan", "Mic", 18, "Romania", "Forward", 1250);
                add(defaultPlayer); // 调用add方法插入，add方法会处理数据库和列表
                System.out.println("Inserted default player 'P1'.");
            }
        }
    }

    public void add(Player entity) throws SQLException {
        // 优先将玩家添加到内存列表
        allPlayers.add(entity); 
        System.out.println("Player added to in-memory list: " + entity.getId());

        String insertSql = "INSERT INTO PlayerMAP(id, firstName, lastName, age, nationality, position, marketValue) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement preparedStatement = this.connection.prepareStatement(insertSql)) {
            preparedStatement.setString(1, entity.getId());
            preparedStatement.setString(2, entity.getFirstName());
            preparedStatement.setString(3, entity.getLastName());
            preparedStatement.setInt(4, entity.getAge());
            preparedStatement.setString(5, entity.getNationality());
            preparedStatement.setString(6, entity.getPosition());
            preparedStatement.setInt(7, entity.getMarketValue());

            int affectedRows = preparedStatement.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("Player '" + entity.getId() + "' successfully added to database.");
                this.connection.commit(); // 提交更改
            } else {
                System.out.println("Player '" + entity.getId() + "' not added to database (0 rows affected).");
                this.connection.rollback(); // 如果没有行受影响，回滚
                allPlayers.remove(entity); // 如果数据库插入失败，从内存列表中移除
            }
        } catch (SQLIntegrityConstraintViolationException e) {
            System.err.println("Error: Player with ID '" + entity.getId() + "' already exists or other constraint violation.");
            this.connection.rollback(); // 发生主键冲突时回滚
            allPlayers.remove(entity); // 如果数据库插入失败，从内存列表中移除
            throw e; // 重新抛出，让上层处理
        } catch (SQLException e) {
            System.err.println("Error adding player '" + entity.getId() + "' to database: " + e.getMessage());
            this.connection.rollback(); // 发生其他SQL异常时回滚
            allPlayers.remove(entity); // 如果数据库插入失败，从内存列表中移除
            throw e; // 重新抛出
        }
    }

    public List<Player> getAllPlayers() {
        return new ArrayList<>(allPlayers); // 返回副本，防止外部修改内部列表
    }

    // 提供一个关闭连接的方法
    public void close() {
        if (this.connection != null) {
            try {
                this.connection.close();
                System.out.println("Database connection closed.");
            } catch (SQLException e) {
                System.err.println("Error closing database connection: " + e.getMessage());
            }
        }
    }
}

// 假设Player类定义如下
class Player {
    private String id;
    private String firstName;
    private String lastName;
    private int age;
    private String nationality;
    private String position;
    private int marketValue;

    public Player(String id, String firstName, String lastName, int age, String nationality, String position, int marketValue) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
        this.nationality = nationality;
        this.position = position;
        this.marketValue = marketValue;
    }

    // Getters
    public String getId() { return id; }
    public String getFirstName() { return firstName; }
    public String getLastName() { return lastName; }
    public int getAge() { return age; }
    public String getNationality() { return nationality; }
    public String getPosition() { return position; }
    public int getMarketValue() { return marketValue; }

    // Setters (如果需要)
    // ...

    @Override
    public String toString() {
        return "Player{" +
               "id='" + id + '\'' +
               ", firstName='" + firstName + '\'' +
               ", lastName='" + lastName + '\'' +
               '}';
    }
}