package github.nighter.smartspawner.spawner.data.storage; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import github.nighter.smartspawner.SmartSpawner; import github.nighter.smartspawner.spawner.properties.SpawnerData; import github.nighter.smartspawner.spawner.properties.VirtualInventory; import github.nighter.smartspawner.spawner.utils.ItemStackSerializer; import org.bukkit.Bukkit; import org.bukkit.Location; import org.bukkit.World; import org.bukkit.entity.EntityType; import org.bukkit.inventory.ItemStack; import java.sql.*; import java.util.*; import java.util.concurrent.*; import java.util.logging.Level; import java.util.logging.Logger; /** * MySQL-based storage provider for spawner data. * Uses HikariCP for connection pooling. */ public class MySQLStorageProvider implements StorageProvider { private final SmartSpawner plugin; private final Logger logger; private HikariDataSource dataSource; private final ExecutorService asyncExecutor; // Configuration private final String host; private final int port; private final String database; private final String username; private final String password; private final String tablePrefix; private final boolean useSSL; // Table names private final String spawnersTable; private final String inventoryTable; private final String filterTable; public MySQLStorageProvider(SmartSpawner plugin) { this.plugin = plugin; this.logger = plugin.getLogger(); this.asyncExecutor = Executors.newFixedThreadPool(2); // Load configuration this.host = plugin.getConfig().getString("storage.mysql.host", "localhost"); this.port = plugin.getConfig().getInt("storage.mysql.port", 3306); this.database = plugin.getConfig().getString("storage.mysql.database", "smartspawner"); this.username = plugin.getConfig().getString("storage.mysql.username", "root"); this.password = plugin.getConfig().getString("storage.mysql.password", ""); this.tablePrefix = plugin.getConfig().getString("storage.mysql.table_prefix", "ss_"); this.useSSL = plugin.getConfig().getBoolean("storage.mysql.use_ssl", false); // Table names this.spawnersTable = tablePrefix + "spawners"; this.inventoryTable = tablePrefix + "inventory"; this.filterTable = tablePrefix + "filters"; } @Override public boolean initialize() { try { setupDataSource(); createTables(); logger.info("MySQL storage initialized successfully."); return true; } catch (Exception e) { logger.severe("Failed to initialize MySQL storage: " + e.getMessage()); e.printStackTrace(); return false; } } private void setupDataSource() { HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:mysql://" + host + ":" + port + "/" + database + "?useSSL=" + useSSL + "&allowPublicKeyRetrieval=true&autoReconnect=true"); config.setUsername(username); config.setPassword(password); // Pool configuration config.setMaximumPoolSize(plugin.getConfig().getInt("storage.mysql.pool.maximum_pool_size", 10)); config.setMinimumIdle(plugin.getConfig().getInt("storage.mysql.pool.minimum_idle", 2)); config.setMaxLifetime(plugin.getConfig().getInt("storage.mysql.pool.max_lifetime", 1800000)); config.setConnectionTimeout(plugin.getConfig().getInt("storage.mysql.pool.connection_timeout", 30000)); config.setIdleTimeout(plugin.getConfig().getInt("storage.mysql.pool.idle_timeout", 600000)); // Performance settings config.addDataSourceProperty("cachePrepStmts", "true"); config.addDataSourceProperty("prepStmtCacheSize", "250"); config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); config.addDataSourceProperty("useServerPrepStmts", "true"); config.addDataSourceProperty("useLocalSessionState", "true"); config.addDataSourceProperty("rewriteBatchedStatements", "true"); config.addDataSourceProperty("cacheResultSetMetadata", "true"); config.addDataSourceProperty("cacheServerConfiguration", "true"); config.addDataSourceProperty("elideSetAutoCommits", "true"); config.addDataSourceProperty("maintainTimeStats", "false"); config.setPoolName("SmartSpawner-MySQL-Pool"); dataSource = new HikariDataSource(config); } private void createTables() throws SQLException { try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) { // Create spawners table stmt.execute("CREATE TABLE IF NOT EXISTS " + spawnersTable + " (" + "spawner_id VARCHAR(64) PRIMARY KEY, " + "world_name VARCHAR(64) NOT NULL, " + "x INT NOT NULL, " + "y INT NOT NULL, " + "z INT NOT NULL, " + "entity_type VARCHAR(64) NOT NULL, " + "stack_size INT DEFAULT 1, " + "spawner_exp BIGINT DEFAULT 0, " + "spawner_active BOOLEAN DEFAULT TRUE, " + "spawn_delay INT DEFAULT 200, " + "filter_enabled BOOLEAN DEFAULT FALSE, " + "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " + "updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " + "INDEX idx_location (world_name, x, y, z)" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"); // Create inventory table stmt.execute("CREATE TABLE IF NOT EXISTS " + inventoryTable + " (" + "id INT AUTO_INCREMENT PRIMARY KEY, " + "spawner_id VARCHAR(64) NOT NULL, " + "item_data TEXT NOT NULL, " + "amount BIGINT NOT NULL, " + "slot_index INT NOT NULL, " + "FOREIGN KEY (spawner_id) REFERENCES " + spawnersTable + "(spawner_id) ON DELETE CASCADE, " + "INDEX idx_spawner (spawner_id)" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"); // Create filter table stmt.execute("CREATE TABLE IF NOT EXISTS " + filterTable + " (" + "id INT AUTO_INCREMENT PRIMARY KEY, " + "spawner_id VARCHAR(64) NOT NULL, " + "item_type VARCHAR(128) NOT NULL, " + "FOREIGN KEY (spawner_id) REFERENCES " + spawnersTable + "(spawner_id) ON DELETE CASCADE, " + "INDEX idx_spawner_filter (spawner_id)" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"); logger.info("MySQL tables created/verified successfully."); } } @Override public Map loadAllSpawners() { Map spawners = new ConcurrentHashMap<>(); try (Connection conn = dataSource.getConnection()) { // Load all spawners String query = "SELECT * FROM " + spawnersTable; try (PreparedStatement stmt = conn.prepareStatement(query); ResultSet rs = stmt.executeQuery()) { while (rs.next()) { try { SpawnerData spawner = loadSpawnerFromResultSet(rs); if (spawner != null) { // Load inventory for this spawner loadInventory(conn, spawner); // Load filters for this spawner loadFilters(conn, spawner); spawners.put(spawner.getSpawnerId(), spawner); } } catch (Exception e) { logger.warning("Error loading spawner from MySQL: " + e.getMessage()); } } } logger.info("Loaded " + spawners.size() + " spawners from MySQL database."); } catch (SQLException e) { logger.severe("Error loading spawners from MySQL: " + e.getMessage()); e.printStackTrace(); } return spawners; } private SpawnerData loadSpawnerFromResultSet(ResultSet rs) throws SQLException { String spawnerId = rs.getString("spawner_id"); String worldName = rs.getString("world_name"); int x = rs.getInt("x"); int y = rs.getInt("y"); int z = rs.getInt("z"); World world = Bukkit.getWorld(worldName); if (world == null) { logger.warning("World '" + worldName + "' not found for spawner " + spawnerId); return null; } Location location = new Location(world, x, y, z); EntityType entityType = parseEntityType(rs.getString("entity_type")); SpawnerData spawner = new SpawnerData(plugin, spawnerId, location, entityType); spawner.setStackSize(rs.getInt("stack_size")); spawner.setSpawnerExp(rs.getLong("spawner_exp")); spawner.setSpawnerActive(rs.getBoolean("spawner_active")); spawner.setSpawnDelay(rs.getInt("spawn_delay")); // Set filter enabled state spawner.getVirtualInventory().setItemFilterEnabled(rs.getBoolean("filter_enabled")); return spawner; } private void loadInventory(Connection conn, SpawnerData spawner) throws SQLException { String query = "SELECT item_data, amount FROM " + inventoryTable + " WHERE spawner_id = ? ORDER BY slot_index"; try (PreparedStatement stmt = conn.prepareStatement(query)) { stmt.setString(1, spawner.getSpawnerId()); try (ResultSet rs = stmt.executeQuery()) { Map items = new LinkedHashMap<>(); while (rs.next()) { String itemData = rs.getString("item_data"); long amount = rs.getLong("amount"); ItemStack item = ItemStackSerializer.deserialize(itemData); if (item != null && amount > 0) { items.put(item, amount); } } spawner.getVirtualInventory().loadItemsFromMap(items); } } } private void loadFilters(Connection conn, SpawnerData spawner) throws SQLException { String query = "SELECT item_type FROM " + filterTable + " WHERE spawner_id = ?"; try (PreparedStatement stmt = conn.prepareStatement(query)) { stmt.setString(1, spawner.getSpawnerId()); try (ResultSet rs = stmt.executeQuery()) { List filters = new ArrayList<>(); while (rs.next()) { filters.add(rs.getString("item_type")); } spawner.getVirtualInventory().getFilteredItems().clear(); spawner.getVirtualInventory().getFilteredItems().addAll(filters); } } } @Override public void saveAllSpawners(Map spawners) { if (spawners == null || spawners.isEmpty()) { return; } asyncExecutor.submit(() -> { try { saveAllSpawnersInternal(spawners); } catch (Exception e) { logger.severe("Error saving spawners to MySQL: " + e.getMessage()); e.printStackTrace(); } }); } @Override public void saveAllSpawnersSync(Map spawners) { if (spawners == null || spawners.isEmpty()) { return; } try { saveAllSpawnersInternal(spawners); } catch (Exception e) { logger.severe("Error saving spawners to MySQL: " + e.getMessage()); e.printStackTrace(); } } private void saveAllSpawnersInternal(Map spawners) throws SQLException { // Create snapshots on the calling thread Map snapshots = new HashMap<>(); for (Map.Entry entry : spawners.entrySet()) { try { snapshots.put(entry.getKey(), new SpawnerSnapshot(entry.getValue())); } catch (Exception e) { logger.warning("Failed to snapshot spawner " + entry.getKey() + ": " + e.getMessage()); } } try (Connection conn = dataSource.getConnection()) { conn.setAutoCommit(false); try { for (Map.Entry entry : snapshots.entrySet()) { saveSpawnerSnapshot(conn, entry.getKey(), entry.getValue()); } conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; } finally { conn.setAutoCommit(true); } } } private void saveSpawnerSnapshot(Connection conn, String spawnerId, SpawnerSnapshot snapshot) throws SQLException { // Upsert spawner data String upsertQuery = "INSERT INTO " + spawnersTable + " (spawner_id, world_name, x, y, z, entity_type, stack_size, spawner_exp, spawner_active, spawn_delay, filter_enabled) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " + "ON DUPLICATE KEY UPDATE " + "world_name = VALUES(world_name), x = VALUES(x), y = VALUES(y), z = VALUES(z), " + "entity_type = VALUES(entity_type), stack_size = VALUES(stack_size), spawner_exp = VALUES(spawner_exp), " + "spawner_active = VALUES(spawner_active), spawn_delay = VALUES(spawn_delay), filter_enabled = VALUES(filter_enabled)"; try (PreparedStatement stmt = conn.prepareStatement(upsertQuery)) { stmt.setString(1, spawnerId); stmt.setString(2, snapshot.worldName); stmt.setInt(3, snapshot.x); stmt.setInt(4, snapshot.y); stmt.setInt(5, snapshot.z); stmt.setString(6, snapshot.entityType); stmt.setInt(7, snapshot.stackSize); stmt.setLong(8, snapshot.spawnerExp); stmt.setBoolean(9, snapshot.spawnerActive); stmt.setInt(10, snapshot.spawnDelay); stmt.setBoolean(11, snapshot.filterEnabled); stmt.executeUpdate(); } // Delete old inventory try (PreparedStatement stmt = conn.prepareStatement("DELETE FROM " + inventoryTable + " WHERE spawner_id = ?")) { stmt.setString(1, spawnerId); stmt.executeUpdate(); } // Insert new inventory if (!snapshot.items.isEmpty()) { String insertInventory = "INSERT INTO " + inventoryTable + " (spawner_id, item_data, amount, slot_index) VALUES (?, ?, ?, ?)"; try (PreparedStatement stmt = conn.prepareStatement(insertInventory)) { int index = 0; for (Map.Entry entry : snapshot.items.entrySet()) { stmt.setString(1, spawnerId); stmt.setString(2, ItemStackSerializer.serialize(entry.getKey())); stmt.setLong(3, entry.getValue()); stmt.setInt(4, index++); stmt.addBatch(); } stmt.executeBatch(); } } // Delete old filters try (PreparedStatement stmt = conn.prepareStatement("DELETE FROM " + filterTable + " WHERE spawner_id = ?")) { stmt.setString(1, spawnerId); stmt.executeUpdate(); } // Insert new filters if (!snapshot.filteredItems.isEmpty()) { String insertFilter = "INSERT INTO " + filterTable + " (spawner_id, item_type) VALUES (?, ?)"; try (PreparedStatement stmt = conn.prepareStatement(insertFilter)) { for (String itemType : snapshot.filteredItems) { stmt.setString(1, spawnerId); stmt.setString(2, itemType); stmt.addBatch(); } stmt.executeBatch(); } } } @Override public CompletableFuture saveSpawner(SpawnerData spawner) { return CompletableFuture.runAsync(() -> { try (Connection conn = dataSource.getConnection()) { conn.setAutoCommit(false); try { SpawnerSnapshot snapshot = new SpawnerSnapshot(spawner); saveSpawnerSnapshot(conn, spawner.getSpawnerId(), snapshot); conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; } finally { conn.setAutoCommit(true); } } catch (SQLException e) { logger.severe("Error saving spawner " + spawner.getSpawnerId() + " to MySQL: " + e.getMessage()); } }, asyncExecutor); } @Override public CompletableFuture deleteSpawner(String spawnerId) { return CompletableFuture.runAsync(() -> { try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement("DELETE FROM " + spawnersTable + " WHERE spawner_id = ?")) { stmt.setString(1, spawnerId); stmt.executeUpdate(); } catch (SQLException e) { logger.severe("Error deleting spawner " + spawnerId + " from MySQL: " + e.getMessage()); } }, asyncExecutor); } @Override public void shutdown() { asyncExecutor.shutdown(); try { if (!asyncExecutor.awaitTermination(10, TimeUnit.SECONDS)) { asyncExecutor.shutdownNow(); } } catch (InterruptedException e) { asyncExecutor.shutdownNow(); Thread.currentThread().interrupt(); } if (dataSource != null && !dataSource.isClosed()) { dataSource.close(); logger.info("MySQL connection pool closed."); } } @Override public String getStorageType() { return "MySQL"; } private EntityType parseEntityType(String typeName) { if (typeName == null) { return EntityType.PIG; } try { return EntityType.valueOf(typeName.toUpperCase()); } catch (IllegalArgumentException e) { return EntityType.PIG; } } /** * Check if the MySQL connection is valid */ public boolean isConnected() { if (dataSource == null || dataSource.isClosed()) { return false; } try (Connection conn = dataSource.getConnection()) { return conn.isValid(5); } catch (SQLException e) { return false; } } // =============================================================== // Snapshot inner class // =============================================================== private static class SpawnerSnapshot { final String worldName; final int x, y, z; final String entityType; final int stackSize; final long spawnerExp; final boolean spawnerActive; final int spawnDelay; final boolean filterEnabled; final List filteredItems; final Map items; SpawnerSnapshot(SpawnerData spawner) { Location loc = spawner.getLocation(); this.worldName = loc.getWorld() != null ? loc.getWorld().getName() : "world"; this.x = loc.getBlockX(); this.y = loc.getBlockY(); this.z = loc.getBlockZ(); this.entityType = spawner.getEntityType().name(); this.stackSize = spawner.getStackSize(); this.spawnerExp = spawner.getSpawnerExp(); this.spawnerActive = spawner.isSpawnerActive(); this.spawnDelay = spawner.getSpawnDelay(); VirtualInventory vi = spawner.getVirtualInventory(); this.filterEnabled = vi.isItemFilterEnabled(); this.filteredItems = new ArrayList<>(vi.getFilteredItems()); this.items = new LinkedHashMap<>(vi.getAllItems()); } } }