PaperMC 插件开发完全指南 / 第 12 章：数据库集成
第 12 章：数据库集成
掌握 SQLite、MySQL 和 Redis 的集成方法，为插件添加持久化存储能力。
12.1 数据存储方案对比
| 方案 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| YAML/JSON 文件 | 简单配置、少量数据 | 无需额外依赖，易读 | 不适合频繁读写，并发差 |
| SQLite | 中小型服务器、单机 | 零配置，嵌入式 | 不适合高并发写入 |
| MySQL | 大型服务器、多服共享 | 高并发，成熟稳定 | 需要独立服务，配置复杂 |
| Redis | 缓存、会话、排行榜 | 极高性能，支持发布订阅 | 数据需要持久化策略 |
| MongoDB | 文档型数据、日志 | 灵活 schema | Java 驱动较重 |
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 章：任务调度 — 学习同步/异步任务、定时器和延迟任务。