Spigot 1.20.2插件SQLite连接关闭/空值致封禁查询报错

你的核心问题是单例Connection被try-with-resources语法自动关闭：

• isPlayerBanned方法用try-with-resources包裹了从单例SQLiteConnector获取的Connection，该语法会在代码块执行完毕后强制关闭连接。
• 由于SQLiteConnector采用单例模式管理连接，后续调用getBanInfo时拿到的是已经被关闭的连接，因此抛出"数据库连接已关闭或为空"的异常。

方案1：修改单例连接的使用逻辑（快速修复） #

保留单例Connection，但只在Statement/ResultSet层面使用try-with-resources，绝不关闭Connection本身，仅在插件卸载时统一关闭连接。

示例代码：

// 修改后的isPlayerBanned方法
public boolean isPlayerBanned(UUID playerId) {
    String query = "SELECT EXISTS(SELECT 1 FROM bans WHERE player_uuid = ?)";
    try (PreparedStatement stmt = SQLiteConnector.getInstance().getConnection().prepareStatement(query)) {
        stmt.setString(1, playerId.toString());
        try (ResultSet rs = stmt.executeQuery()) {
            return rs.next() && rs.getBoolean(1);
        }
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

// 修改后的getBanInfo方法
public BanInfo getBanInfo(UUID playerId) {
    String query = "SELECT reason, expire_time FROM bans WHERE player_uuid = ?";
    try (PreparedStatement stmt = SQLiteConnector.getInstance().getConnection().prepareStatement(query)) {
        stmt.setString(1, playerId.toString());
        try (ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return new BanInfo(rs.getString("reason"), rs.getLong("expire_time"));
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

// 在插件onDisable方法中关闭单例连接
@Override
public void onDisable() {
    try {
        SQLiteConnector.getInstance().getConnection().close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

方案2：改用连接池（推荐长期方案） #

单例Connection本身存在线程安全问题（多线程并发操作会抛出异常），改用轻量连接池（如HikariCP）可以彻底解决连接管理问题：每次从池内获取连接，用完自动归还（不是真正关闭），同时支持高并发场景。

示例代码：

// 配置SQLite连接池
public class SQLiteConnector {
    private static HikariDataSource pool;

    public static void init() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:sqlite:plugins/YourPlugin/bans.db");
        config.setMaximumPoolSize(5); // 根据服务器负载调整
        pool = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return pool.getConnection();
    }

    public static void shutdown() {
        if (pool != null) pool.close();
    }
}

// 安全使用连接池的isPlayerBanned方法
public boolean isPlayerBanned(UUID playerId) {
    String query = "SELECT EXISTS(SELECT 1 FROM bans WHERE player_uuid = ?)";
    try (Connection conn = SQLiteConnector.getConnection();
         PreparedStatement stmt = conn.prepareStatement(query)) {
        stmt.setString(1, playerId.toString());
        try (ResultSet rs = stmt.executeQuery()) {
            return rs.next() && rs.getBoolean(1);
        }
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

// 安全使用连接池的getBanInfo方法
public BanInfo getBanInfo(UUID playerId) {
    String query = "SELECT reason, expire_time FROM bans WHERE player_uuid = ?";
    try (Connection conn = SQLiteConnector.getConnection();
         PreparedStatement stmt = conn.prepareStatement(query)) {
        stmt.setString(1, playerId.toString());
        try (ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return new BanInfo(rs.getString("reason"), rs.getLong("expire_time"));
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

方案3：合并数据库操作（优化调用逻辑） #

在一次连接中同时完成"判断封禁状态"和"获取封禁信息"的查询，避免两次连接调用，减少出错概率：

示例代码：

public BanInfo checkBanStatus(UUID playerId) {
    String query = "SELECT reason, expire_time FROM bans WHERE player_uuid = ?";
    try (Connection conn = SQLiteConnector.getConnection();
         PreparedStatement stmt = conn.prepareStatement(query)) {
        stmt.setString(1, playerId.toString());
        try (ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return new BanInfo(rs.getString("reason"), rs.getLong("expire_time"));
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

// 在JoinEvent中调用
BanInfo banInfo = checkBanStatus(player.getUniqueId());
if (banInfo != null) {
    player.kickPlayer("你已被封禁：" + banInfo.getReason());
}

• Spigot插件中，数据库操作必须放在异步线程执行，避免阻塞主线程导致服务器卡顿：

  Bukkit.getScheduler().runTaskAsynchronously(yourPlugin, () -> {
      BanInfo banInfo = checkBanStatus(player.getUniqueId());
      if (banInfo != null) {
          Bukkit.getScheduler().runTask(yourPlugin, () -> 
              player.kickPlayer("你已被封禁：" + banInfo.getReason())
          );
      }
  });
  

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