强曰为道

与天地相似，故不违。知周乎万物，而道济天下，故不过。旁行而不流，乐天知命，故不忧.
文档目录

第 12 章：数据库集成

第 12 章：数据库集成

掌握 SQLite、MySQL 和 Redis 的集成方法，为插件添加持久化存储能力。

12.1 数据存储方案对比

方案适用场景优点缺点
YAML/JSON 文件简单配置、少量数据无需额外依赖，易读不适合频繁读写，并发差
SQLite中小型服务器、单机零配置，嵌入式不适合高并发写入
MySQL大型服务器、多服共享高并发，成熟稳定需要独立服务，配置复杂
Redis缓存、会话、排行榜极高性能，支持发布订阅数据需要持久化策略
MongoDB文档型数据、日志灵活 schemaJava 驱动较重

12.2 SQLite 集成

SQLite 是嵌入式数据库，无需额外服务，非常适合中小型服务器。

添加 JDBC 驱动

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.46.0.0</version>
</dependency>

数据库管理器

package com.example.myplugin.database;

import org.bukkit.plugin.java.JavaPlugin;

import java.io.File;
import java.sql.*;

public class SQLiteManager {

    private final JavaPlugin plugin;
    private Connection connection;

    public SQLiteManager(JavaPlugin plugin) {
        this.plugin = plugin;
    }

    /**
     * 初始化数据库连接
     */
    public void initialize() throws SQLException {
        File dbFile = new File(plugin.getDataFolder(), "data.db");
        plugin.getDataFolder().mkdirs();

        String url = "jdbc:sqlite:" + dbFile.getAbsolutePath();
        connection = DriverManager.getConnection(url);

        // 启用 WAL 模式（提升并发性能）
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("PRAGMA journal_mode=WAL");
            stmt.execute("PRAGMA synchronous=NORMAL");
        }

        // 创建表
        createTables();

        plugin.getLogger().info("SQLite 数据库已初始化！");
    }

    /**
     * 创建数据表
     */
    private void createTables() throws SQLException {
        String sql = """
            CREATE TABLE IF NOT EXISTS players (
                uuid TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                balance REAL DEFAULT 0.0,
                level INTEGER DEFAULT 1,
                play_time INTEGER DEFAULT 0,
                first_join TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_join TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """;

        try (Statement stmt = connection.createStatement()) {
            stmt.execute(sql);
        }
    }

    /**
     * 获取连接
     */
    public Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            initialize();
        }
        return connection;
    }

    /**
     * 关闭连接
     */
    public void close() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            plugin.getLogger().severe("关闭数据库失败: " + e.getMessage());
        }
    }
}

基本 CRUD 操作

public class PlayerDataDAO {

    private final SQLiteManager dbManager;

    public PlayerDataDAO(SQLiteManager dbManager) {
        this.dbManager = dbManager;
    }

    /**
     * 插入或更新玩家数据
     */
    public void savePlayer(PlayerData data) throws SQLException {
        String sql = """
            INSERT INTO players (uuid, name, balance, level, play_time, last_join)
            VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
            ON CONFLICT(uuid) DO UPDATE SET
                name = excluded.name,
                balance = excluded.balance,
                level = excluded.level,
                play_time = excluded.play_time,
                last_join = CURRENT_TIMESTAMP
            """;

        try (Connection conn = dbManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, data.getUuid().toString());
            pstmt.setString(2, data.getName());
            pstmt.setDouble(3, data.getBalance());
            pstmt.setInt(4, data.getLevel());
            pstmt.setLong(5, data.getPlayTime());
            pstmt.executeUpdate();
        }
    }

    /**
     * 查询玩家数据
     */
    public PlayerData loadPlayer(UUID uuid) throws SQLException {
        String sql = "SELECT * FROM players WHERE uuid = ?";

        try (Connection conn = dbManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, uuid.toString());

            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return new PlayerData(
                    uuid,
                    rs.getString("name"),
                    rs.getDouble("balance"),
                    rs.getInt("level"),
                    rs.getLong("play_time")
                );
            }
        }

        return null; // 玩家不存在
    }

    /**
     * 批量查询
     */
    public List<PlayerData> getTopPlayers(int limit) throws SQLException {
        String sql = "SELECT * FROM players ORDER BY balance DESC LIMIT ?";
        List<PlayerData> result = new ArrayList<>();

        try (Connection conn = dbManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, limit);

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                result.add(new PlayerData(
                    UUID.fromString(rs.getString("uuid")),
                    rs.getString("name"),
                    rs.getDouble("balance"),
                    rs.getInt("level"),
                    rs.getLong("play_time")
                ));
            }
        }

        return result;
    }

    /**
     * 删除玩家数据
     */
    public void deletePlayer(UUID uuid) throws SQLException {
        String sql = "DELETE FROM players WHERE uuid = ?";

        try (Connection conn = dbManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, uuid.toString());
            pstmt.executeUpdate();
        }
    }
}

12.3 MySQL 集成

MySQL 适合多服务器共享数据或高并发场景。

连接池（HikariCP 推荐）

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

MySQL 连接池管理

package com.example.myplugin.database;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.bukkit.configuration.file.FileConfiguration;

import java.sql.Connection;
import java.sql.SQLException;

public class MySQLManager {

    private HikariDataSource dataSource;

    /**
     * 初始化连接池
     */
    public void initialize(FileConfiguration config) {
        HikariConfig hikariConfig = new HikariConfig();

        String host = config.getString("database.host", "localhost");
        int port = config.getInt("database.port", 3306);
        String database = config.getString("database.name", "minecraft");
        String username = config.getString("database.username", "root");
        String password = config.getString("database.password", "");

        hikariConfig.setJdbcUrl(
            "jdbc:mysql://" + host + ":" + port + "/" + database
                + "?useSSL=false&autoReconnect=true&serverTimezone=Asia/Shanghai"
        );
        hikariConfig.setUsername(username);
        hikariConfig.setPassword(password);

        // 连接池配置
        hikariConfig.setMaximumPoolSize(10);          // 最大连接数
        hikariConfig.setMinimumIdle(5);               // 最小空闲连接
        hikariConfig.setConnectionTimeout(30000);     // 连接超时 30 秒
        hikariConfig.setIdleTimeout(600000);          // 空闲超时 10 分钟
        hikariConfig.setMaxLifetime(1800000);         // 最大生命周期 30 分钟

        // 性能优化
        hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
        hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
        hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        dataSource = new HikariDataSource(hikariConfig);
    }

    /**
     * 获取连接
     */
    public Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 关闭连接池
     */
    public void close() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }
}

配置文件（config.yml）

database:
  type: mysql  # sqlite 或 mysql
  host: localhost
  port: 3306
  name: minecraft
  username: root
  password: ""

12.4 异步数据库操作

关键原则: 不要在主线程执行数据库操作！

异步查询

public class AsyncDatabase {

    private final MySQLManager dbManager;
    private final ExecutorService executor;

    public AsyncDatabase(MySQLManager dbManager) {
        this.dbManager = dbManager;
        // 创建固定大小的线程池
        this.executor = Executors.newFixedThreadPool(4);
    }

    /**
     * 异步保存玩家数据
     */
    public void savePlayerAsync(PlayerData data, Consumer<Boolean> callback) {
        executor.submit(() -> {
            try {
                new PlayerDataDAO(dbManager).savePlayer(data);
                if (callback != null) callback.accept(true);
            } catch (SQLException e) {
                e.printStackTrace();
                if (callback != null) callback.accept(false);
            }
        });
    }

    /**
     * 异步加载玩家数据
     */
    public void loadPlayerAsync(UUID uuid, Consumer<PlayerData> callback) {
        executor.submit(() -> {
            try {
                PlayerData data = new PlayerDataDAO(dbManager).loadPlayer(uuid);
                // 回到主线程回调
                Bukkit.getScheduler().runTask(plugin, () -> callback.accept(data));
            } catch (SQLException e) {
                e.printStackTrace();
                Bukkit.getScheduler().runTask(plugin, () -> callback.accept(null));
            }
        });
    }

    /**
     * 关闭线程池
     */
    public void shutdown() {
        executor.shutdown();
        try {
            if (!executor.awaitTermination(10, TimeUnit.SECONDS)) {
                executor.shutdownNow();
            }
        } catch (InterruptedException e) {
            executor.shutdownNow();
        }
    }
}

使用 CompletableFuture

/**
 * 使用 CompletableFuture 的异步查询
 */
public CompletableFuture<PlayerData> loadPlayer(UUID uuid) {
    return CompletableFuture.supplyAsync(() -> {
        try {
            return new PlayerDataDAO(dbManager).loadPlayer(uuid);
        } catch (SQLException e) {
            throw new CompletionException(e);
        }
    }, executor);
}

// 使用方式
asyncDB.loadPlayer(player.getUniqueId())
    .thenAcceptAsync(data -> {
        // 主线程处理
        if (data != null) {
            player.sendMessage("§a你的余额: " + data.getBalance());
        }
    }, runnable -> Bukkit.getScheduler().runTask(plugin, runnable))
    .exceptionally(ex -> {
        player.sendMessage("§c加载数据失败！");
        return null;
    });

12.5 Redis 集成

Redis 适合缓存热数据、排行榜、会话管理和发布/订阅。

依赖

<dependency>
    <groupId>redis.clients</groupId>
    <artifactId>jedis</artifactId>
    <version>5.1.0</version>
</dependency>

Redis 管理器

package com.example.myplugin.database;

import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;
import redis.clients.jedis.JedisPoolConfig;

public class RedisManager {

    private JedisPool jedisPool;

    public void initialize(String host, int port, String password) {
        JedisPoolConfig poolConfig = new JedisPoolConfig();
        poolConfig.setMaxTotal(16);
        poolConfig.setMaxIdle(8);
        poolConfig.setMinIdle(2);
        poolConfig.setTestOnBorrow(true);

        if (password == null || password.isEmpty()) {
            jedisPool = new JedisPool(poolConfig, host, port);
        } else {
            jedisPool = new JedisPool(poolConfig, host, port, 2000, password);
        }
    }

    /**
     * 执行 Redis 操作
     */
    public <T> T execute(Function<Jedis, T> action) {
        try (Jedis jedis = jedisPool.getResource()) {
            return action.apply(jedis);
        }
    }

    public void close() {
        if (jedisPool != null) {
            jedisPool.close();
        }
    }
}

Redis 使用示例

public class RedisCache {

    private final RedisManager redis;
    private static final String PREFIX = "myplugin:";

    public RedisCache(RedisManager redis) {
        this.redis = redis;
    }

    /**
     * 缓存玩家余额
     */
    public void setBalance(UUID uuid, double balance) {
        redis.execute(jedis -> {
            jedis.setex(
                PREFIX + "balance:" + uuid,
                300, // 5 分钟过期
                String.valueOf(balance)
            );
            return null;
        });
    }

    /**
     * 获取缓存的余额
     */
    public OptionalDouble getBalance(UUID uuid) {
        String value = redis.execute(jedis ->
            jedis.get(PREFIX + "balance:" + uuid));

        if (value == null) return OptionalDouble.empty();
        return OptionalDouble.of(Double.parseDouble(value));
    }

    /**
     * 更新排行榜
     */
    public void updateLeaderboard(String board, String member, double score) {
        redis.execute(jedis -> {
            jedis.zadd(PREFIX + "leaderboard:" + board, score, member);
            return null;
        });
    }

    /**
     * 获取排行榜前 N 名
     */
    public List<String> getTopPlayers(String board, int count) {
        return redis.execute(jedis ->
            new ArrayList<>(jedis.zrevrange(
                PREFIX + "leaderboard:" + board, 0, count - 1))
        );
    }

    /**
     * 发布消息（跨服务器通信）
     */
    public void publish(String channel, String message) {
        redis.execute(jedis -> {
            jedis.publish(PREFIX + channel, message);
            return null;
        });
    }
}

12.6 数据迁移

/**
 * SQLite 迁移到 MySQL
 */
public class DataMigration {

    public void migrateSQLiteToMySQL(SQLiteManager sqlite, MySQLManager mysql) {
        // 从 SQLite 读取所有数据
        List<PlayerData> allPlayers;
        try {
            allPlayers = new PlayerDataDAO(sqlite).getAllPlayers();
        } catch (SQLException e) {
            throw new RuntimeException("读取 SQLite 数据失败", e);
        }

        // 批量写入 MySQL
        try (Connection conn = mysql.getConnection()) {
            conn.setAutoCommit(false);

            String sql = "INSERT INTO players (uuid, name, balance, level, play_time) "
                + "VALUES (?, ?, ?, ?, ?) "
                + "ON DUPLICATE KEY UPDATE "
                + "name=VALUES(name), balance=VALUES(balance), "
                + "level=VALUES(level), play_time=VALUES(play_time)";

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                int batchCount = 0;
                for (PlayerData data : allPlayers) {
                    pstmt.setString(1, data.getUuid().toString());
                    pstmt.setString(2, data.getName());
                    pstmt.setDouble(3, data.getBalance());
                    pstmt.setInt(4, data.getLevel());
                    pstmt.setLong(5, data.getPlayTime());
                    pstmt.addBatch();

                    batchCount++;
                    if (batchCount % 1000 == 0) {
                        pstmt.executeBatch();
                    }
                }
                pstmt.executeBatch();
                conn.commit();
            }
        } catch (SQLException e) {
            throw new RuntimeException("写入 MySQL 数据失败", e);
        }
    }
}

12.7 连接池管理

连接池最佳实践

public class ConnectionPoolManager {

    /**
     * 使用 try-with-resources 确保连接释放
     */
    public void safeQuery() {
        try (Connection conn = pool.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM players")) {

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                // 处理数据
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 连接自动归还到池中
    }

    /**
     * 事务处理
     */
    public void transaction() throws SQLException {
        try (Connection conn = pool.getConnection()) {
            conn.setAutoCommit(false);
            try {
                // 执行多个操作
                updatePlayer(conn, uuid, balance);
                logTransaction(conn, uuid, amount);

                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            } finally {
                conn.setAutoCommit(true);
            }
        }
    }
}

12.8 业务场景：玩家数据管理器

public class PlayerDataManager {

    private final AsyncDatabase asyncDB;
    private final RedisCache redisCache;

    /**
     * 玩家加入时加载数据
     */
    @EventHandler
    public void onJoin(PlayerJoinEvent event) {
        Player player = event.getPlayer();
        UUID uuid = player.getUniqueId();

        // 先从 Redis 缓存读取
        OptionalDouble cached = redisCache.getBalance(uuid);
        if (cached.isPresent()) {
            // 缓存命中，直接使用
            applyBalance(player, cached.getAsDouble());
        }

        // 异步从数据库加载完整数据
        asyncDB.loadPlayerAsync(uuid, data -> {
            if (data == null) {
                // 新玩家
                data = new PlayerData(uuid, player.getName(), 0, 1, 0);
            }
            applyPlayerData(player, data);

            // 更新 Redis 缓存
            redisCache.setBalance(uuid, data.getBalance());
        });
    }

    /**
     * 玩家退出时保存数据
     */
    @EventHandler
    public void onQuit(PlayerQuitEvent event) {
        Player player = event.getPlayer();
        PlayerData data = getPlayerData(player);

        if (data != null) {
            asyncDB.savePlayerAsync(data, success -> {
                if (!success) {
                    plugin.getLogger().severe(
                        "保存玩家数据失败: " + player.getName());
                }
            });
        }
    }
}

12.9 常见问题排查

问题原因解决方案
主线程卡顿同步数据库操作全部使用异步
连接超时连接池配置不当调整超时参数
内存溢出未关闭连接使用 try-with-resources
数据竞争并发写入使用事务 + 乐观锁
SQLite 锁定多线程写入使用 WAL 模式或改为 MySQL

12.10 扩展阅读

12.11 本章小结

要点内容
SQLite零配置嵌入式数据库，适合中小型服务器
MySQL高并发关系型数据库，使用 HikariCP 连接池
Redis高性能缓存，适合排行榜、会话管理
异步操作所有数据库操作必须在异步线程执行
连接管理try-with-resources 确保连接释放
数据迁移批量操作 + 事务保证一致性

下一章: 第 13 章：任务调度 — 学习同步/异步任务、定时器和延迟任务。