如何在Spigot插件可执行文件中集成数据库（优先支持SQLite、MySQL）

Hey there! I’ve built tons of Spigot plugins with database integration over the years, so let’s break down exactly how to set up both SQLite and MySQL for your plugin—no dead ends, just working code and best practices.

一、集成SQLite（适合单服务器、轻量场景） #

SQLite is perfect if you don’t want to run a separate database server—it stores data in a local file right in your plugin’s data folder. Here’s how to set it up:

1. 准备工作 #

If you’re using Maven/Gradle, add the SQLite JDBC dependency to your build file:

<!-- Maven -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.45.2.0</version>
    <scope>compile</scope>
</dependency>

If you’re not using a build tool, just download the SQLite JDBC jar and add it to your project’s classpath.

2. 核心代码实现 #

This example connects to SQLite on plugin enable, creates a player data table, and cleans up connections on disable:

import org.bukkit.plugin.java.JavaPlugin;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class MyDatabasePlugin extends JavaPlugin {
    private Connection dbConnection;

    @Override
    public void onEnable() {
        // 创建插件数据文件夹（如果不存在）
        if (!getDataFolder().exists()) getDataFolder().mkdir();
        
        // 初始化SQLite连接
        setupSQLite();
        // 创建必要的数据表
        createPlayerTable();
        
        getLogger().info("SQLite integration ready!");
    }

    private void setupSQLite() {
        try {
            // 数据库文件路径：插件数据文件夹下的player_data.db
            String dbPath = "jdbc:sqlite:" + getDataFolder().getPath() + "/player_data.db";
            dbConnection = DriverManager.getConnection(dbPath);
        } catch (SQLException e) {
            getLogger().severe("Failed to connect to SQLite: " + e.getMessage());
            // 连接失败就禁用插件，避免后续报错
            getServer().getPluginManager().disablePlugin(this);
        }
    }

    private void createPlayerTable() {
        String createTableSQL = """
            CREATE TABLE IF NOT EXISTS player_stats (
                uuid VARCHAR(36) PRIMARY KEY,
                player_name VARCHAR(16) NOT NULL,
                kills INT DEFAULT 0,
                deaths INT DEFAULT 0
            )
        """;
        
        try (PreparedStatement stmt = dbConnection.prepareStatement(createTableSQL)) {
            stmt.executeUpdate();
        } catch (SQLException e) {
            getLogger().severe("Failed to create player table: " + e.getMessage());
        }
    }

    @Override
    public void onDisable() {
        // 关闭数据库连接
        if (dbConnection != null) {
            try {
                dbConnection.close();
                getLogger().info("SQLite connection closed.");
            } catch (SQLException e) {
                getLogger().severe("Failed to close SQLite connection: " + e.getMessage());
            }
        }
    }
}

二、集成MySQL（适合多服务器共享、大数据量场景） #

MySQL requires a running database server, but it’s great for cross-server data sync or larger datasets. Here’s the step-by-step:

1. 配置数据库信息 #

First, add a config.yml to your plugin’s resources folder to store MySQL credentials (so you don’t hardcode them):

mysql:
  host: "localhost"
  port: 3306
  database: "spigot_plugin_db"
  username: "your_db_user"
  password: "your_db_password"

2. 添加MySQL JDBC依赖 #

Again, for Maven/Gradle:

<!-- Maven -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
    <scope>compile</scope>
</dependency>

3. 核心代码实现 #

This connects to MySQL, uses the config for credentials, and handles async operations (critical to avoid server lag):

import org.bukkit.Bukkit;
import org.bukkit.plugin.java.JavaPlugin;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.UUID;

public class MyMySQLPlugin extends JavaPlugin {
    private Connection dbConnection;

    @Override
    public void onEnable() {
        saveDefaultConfig(); // 生成默认配置文件
        setupMySQL();
        createPlayerTable();
        
        getLogger().info("MySQL integration ready!");
    }

    private void setupMySQL() {
        String host = getConfig().getString("mysql.host");
        int port = getConfig().getInt("mysql.port");
        String dbName = getConfig().getString("mysql.database");
        String user = getConfig().getString("mysql.username");
        String pass = getConfig().getString("mysql.password");

        try {
            // 加载MySQL驱动（新版JDBC可能自动加载，但手动加更稳妥）
            Class.forName("com.mysql.cj.jdbc.Driver");
            String jdbcUrl = String.format("jdbc:mysql://%s:%d/%s?useSSL=false&serverTimezone=UTC", host, port, dbName);
            dbConnection = DriverManager.getConnection(jdbcUrl, user, pass);
        } catch (ClassNotFoundException e) {
            getLogger().severe("MySQL driver not found!");
            getServer().getPluginManager().disablePlugin(this);
        } catch (SQLException e) {
            getLogger().severe("Failed to connect to MySQL: " + e.getMessage());
            getServer().getPluginManager().disablePlugin(this);
        }
    }

    // 数据表创建逻辑和SQLite类似，这里省略重复代码
    private void createPlayerTable() { /* ... */ }

    // 示例：异步更新玩家击杀数（永远不要在主线程执行数据库操作！）
    public void updatePlayerKills(UUID playerUuid, int newKills) {
        Bukkit.getScheduler().runTaskAsynchronously(this, () -> {
            String updateSQL = "UPDATE player_stats SET kills = ? WHERE uuid = ?";
            try (PreparedStatement stmt = dbConnection.prepareStatement(updateSQL)) {
                stmt.setInt(1, newKills);
                stmt.setString(2, playerUuid.toString());
                stmt.executeUpdate();
            } catch (SQLException e) {
                getLogger().severe("Failed to update player kills: " + e.getMessage());
            }
        });
    }

    @Override
    public void onDisable() {
        if (dbConnection != null) {
            try {
                dbConnection.close();
                getLogger().info("MySQL connection closed.");
            } catch (SQLException e) {
                getLogger().severe("Failed to close MySQL connection: " + e.getMessage());
            }
        }
    }
}

三、进阶最佳实践 #

1. 使用连接池（HikariCP） #

Instead of using DriverManager (which creates a new connection every time), use a connection pool like HikariCP to reuse connections—it’s way more efficient. Here’s a quick setup:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

// 在你的插件类里替换setup方法
private HikariDataSource dataSource;

private void setupConnectionPool() {
    HikariConfig config = new HikariConfig();
    // MySQL配置
    config.setJdbcUrl("jdbc:mysql://localhost:3306/spigot_plugin_db?useSSL=false&serverTimezone=UTC");
    config.setUsername("your_user");
    config.setPassword("your_pass");
    
    // SQLite配置（如果用SQLite）
    // config.setJdbcUrl("jdbc:sqlite:" + getDataFolder() + "/player_data.db");
    // config.setDriverClassName("org.sqlite.JDBC");

    config.setMaximumPoolSize(10); // 最大连接数
    config.setConnectionTimeout(30000); // 连接超时（30秒）
    
    dataSource = new HikariDataSource(config);
    // 测试连接
    try (Connection conn = dataSource.getConnection()) {
        getLogger().info("Connection pool initialized successfully!");
    } catch (SQLException e) {
        getLogger().severe("Connection pool setup failed: " + e.getMessage());
        getServer().getPluginManager().disablePlugin(this);
    }
}

// 获取连接时用：dataSource.getConnection()
// 关闭插件时：dataSource.close();

2. 永远异步操作数据库 #

Running database code on the Bukkit main thread will freeze your server—always wrap database calls in runTaskAsynchronously. If you need to do something with Bukkit API (like send a player message) after the database operation, switch back to the main thread with runTask.

If you hit any specific errors (like connection timeouts or syntax issues), feel free to share the stack trace and I’ll help you troubleshoot further!

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