Database Integration

Edoardo Midali

Perché Usare un Database?

I database permettono di salvare dati persistenti in modo strutturato e efficiente. Sono ideali per:

Statistiche giocatori (kill, morti, playtime)
Economia (coins, transazioni)
Dati di guild/clan
Leaderboard
Inventari persistenti

SQLite vs MySQL

SQLite

Pro: Nessuna configurazione server, file locale, facile da usare
Contro: Non adatto per network multi-server
Uso: Server singoli, sviluppo/testing

MySQL

Pro: Condivisione dati tra server, performance migliori per grandi dataset
Contro: Richiede server MySQL separato
Uso: Network multi-server, produzione

Setup SQLite

1. Dipendenze (Maven)

```xml org.xerial sqlite-jdbc 3.41.2.2 ```

2. Database Manager

```java public class SQLiteDatabase { private final Plugin plugin; private Connection connection;

public SQLiteDatabase(Plugin plugin) {
    this.plugin = plugin;
}

public void connect() {
    try {
        File dataFolder = plugin.getDataFolder();
        if (!dataFolder.exists()) {
            dataFolder.mkdirs();
        }
        
        String url = "jdbc:sqlite:" + dataFolder.getAbsolutePath() + "/database.db";
        connection = DriverManager.getConnection(url);
        
        plugin.getLogger().info("Database connesso!");
        
        createTables();
    } catch (SQLException e) {
        plugin.getLogger().severe("Errore connessione database!");
        e.printStackTrace();
    }
}

public void disconnect() {
    try {
        if (connection != null && !connection.isClosed()) {
            connection.close();
            plugin.getLogger().info("Database disconnesso!");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public Connection getConnection() {
    return connection;
}

private void createTables() {
    String sql = "CREATE TABLE IF NOT EXISTS player_stats (" +
                 "uuid TEXT PRIMARY KEY," +
                 "name TEXT NOT NULL," +
                 "kills INTEGER DEFAULT 0," +
                 "deaths INTEGER DEFAULT 0," +
                 "coins INTEGER DEFAULT 0," +
                 "playtime BIGINT DEFAULT 0" +
                 ");";
    
    try (Statement statement = connection.createStatement()) {
        statement.execute(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

} ```

3. Usare il Database

```java public class PlayerStatsManager { private final SQLiteDatabase database;

public PlayerStatsManager(SQLiteDatabase database) {
    this.database = database;
}

public void createPlayer(UUID uuid, String name) {
    String sql = "INSERT OR IGNORE INTO player_stats (uuid, name) VALUES (?, ?)";
    
    try (PreparedStatement statement = database.getConnection().prepareStatement(sql)) {
        statement.setString(1, uuid.toString());
        statement.setString(2, name);
        statement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public PlayerStats getStats(UUID uuid) {
    String sql = "SELECT * FROM player_stats WHERE uuid = ?";
    
    try (PreparedStatement statement = database.getConnection().prepareStatement(sql)) {
        statement.setString(1, uuid.toString());
        
        ResultSet result = statement.executeQuery();
        
        if (result.next()) {
            return new PlayerStats(
                UUID.fromString(result.getString("uuid")),
                result.getString("name"),
                result.getInt("kills"),
                result.getInt("deaths"),
                result.getInt("coins"),
                result.getLong("playtime")
            );
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    return null;
}

public void addKill(UUID uuid) {
    String sql = "UPDATE player_stats SET kills = kills + 1 WHERE uuid = ?";
    
    try (PreparedStatement statement = database.getConnection().prepareStatement(sql)) {
        statement.setString(1, uuid.toString());
        statement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public void addCoins(UUID uuid, int amount) {
    String sql = "UPDATE player_stats SET coins = coins + ? WHERE uuid = ?";
    
    try (PreparedStatement statement = database.getConnection().prepareStatement(sql)) {
        statement.setInt(1, amount);
        statement.setString(2, uuid.toString());
        statement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public List<PlayerStats> getTopKillers(int limit) {
    List<PlayerStats> topPlayers = new ArrayList<>();
    String sql = "SELECT * FROM player_stats ORDER BY kills DESC LIMIT ?";
    
    try (PreparedStatement statement = database.getConnection().prepareStatement(sql)) {
        statement.setInt(1, limit);
        
        ResultSet result = statement.executeQuery();
        
        while (result.next()) {
            topPlayers.add(new PlayerStats(
                UUID.fromString(result.getString("uuid")),
                result.getString("name"),
                result.getInt("kills"),
                result.getInt("deaths"),
                result.getInt("coins"),
                result.getLong("playtime")
            ));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    return topPlayers;
}

}

public class PlayerStats { private final UUID uuid; private final String name; private final int kills; private final int deaths; private final int coins; private final long playtime;

// Costruttore, getter...

} ```

Setup MySQL

1. Dipendenze (Maven)

```xml mysql mysql-connector-java 8.0.33 ```

2. Configurazione (config.yml)

```yaml database: host: localhost port: 3306 database: minecraft username: root password: password ```

3. MySQL Manager

```java public class MySQLDatabase { private final Plugin plugin; private Connection connection; private String host, database, username, password; private int port;

public MySQLDatabase(Plugin plugin) {
    this.plugin = plugin;
    loadConfig();
}

private void loadConfig() {
    FileConfiguration config = plugin.getConfig();
    host = config.getString("database.host");
    port = config.getInt("database.port");
    database = config.getString("database.database");
    username = config.getString("database.username");
    password = config.getString("database.password");
}

public void connect() {
    try {
        String url = "jdbc:mysql://" + host + ":" + port + "/" + database + 
                     "?useSSL=false&autoReconnect=true";
        
        connection = DriverManager.getConnection(url, username, password);
        plugin.getLogger().info("MySQL connesso!");
        
        createTables();
    } catch (SQLException e) {
        plugin.getLogger().severe("Errore connessione MySQL!");
        e.printStackTrace();
    }
}

public void disconnect() {
    try {
        if (connection != null && !connection.isClosed()) {
            connection.close();
            plugin.getLogger().info("MySQL disconnesso!");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public Connection getConnection() {
    try {
        if (connection == null || connection.isClosed()) {
            connect(); // Auto-reconnect
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return connection;
}

private void createTables() {
    String sql = "CREATE TABLE IF NOT EXISTS player_stats (" +
                 "uuid VARCHAR(36) PRIMARY KEY," +
                 "name VARCHAR(16) NOT NULL," +
                 "kills INT DEFAULT 0," +
                 "deaths INT DEFAULT 0," +
                 "coins INT DEFAULT 0," +
                 "playtime BIGINT DEFAULT 0" +
                 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
    
    try (Statement statement = connection.createStatement()) {
        statement.execute(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

} ```

Query Asincrone

IMPORTANTE: Non eseguire mai query sul main thread! Usa task async:

```java public class AsyncDatabaseExample { private final Plugin plugin; private final PlayerStatsManager statsManager;

public AsyncDatabaseExample(Plugin plugin, PlayerStatsManager statsManager) {
    this.plugin = plugin;
    this.statsManager = statsManager;
}

public void loadPlayerStatsAsync(Player player) {
    Bukkit.getScheduler().runTaskAsynchronously(plugin, () -> {
        // Query eseguita in un thread separato
        PlayerStats stats = statsManager.getStats(player.getUniqueId());
        
        // Torna al main thread per interagire con l'API Bukkit
        Bukkit.getScheduler().runTask(plugin, () -> {
            if (stats != null) {
                player.sendMessage("§aKills: " + stats.getKills());
                player.sendMessage("§aDeaths: " + stats.getDeaths());
                player.sendMessage("§aCoins: " + stats.getCoins());
            } else {
                player.sendMessage("§cNessun dato trovato!");
            }
        });
    });
}

public void savePlayerStatsAsync(UUID uuid, int kills, int deaths) {
    Bukkit.getScheduler().runTaskAsynchronously(plugin, () -> {
        String sql = "UPDATE player_stats SET kills = ?, deaths = ? WHERE uuid = ?";
        
        try (PreparedStatement statement = database.getConnection().prepareStatement(sql)) {
            statement.setInt(1, kills);
            statement.setInt(2, deaths);
            statement.setString(3, uuid.toString());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    });
}

} ```

Connection Pool (HikariCP)

Per performance migliori, usa un connection pool:

Dipendenze

```xml com.zaxxer HikariCP 5.0.1 ```

Implementazione

```java public class HikariDatabase { private HikariDataSource dataSource;

public void connect(String host, int port, String database, String username, String password) {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:mysql://" + host + ":" + port + "/" + database);
    config.setUsername(username);
    config.setPassword(password);
    
    // Configurazioni pool
    config.setMaximumPoolSize(10);
    config.setMinimumIdle(2);
    config.setConnectionTimeout(30000);
    config.setIdleTimeout(600000);
    config.setMaxLifetime(1800000);
    
    dataSource = new HikariDataSource(config);
}

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

public void disconnect() {
    if (dataSource != null && !dataSource.isClosed()) {
        dataSource.close();
    }
}

} ```

Best Practices

Usa PreparedStatement: Previene SQL injection e migliora performance
Query asincrone: Mai bloccare il main thread
Connection pool: Usa HikariCP per produzione
Chiudi risorse: Usa try-with-resources per Statement e ResultSet
Gestisci errori: Logga sempre le SQLException
Indici database: Crea indici su colonne usate spesso in WHERE/ORDER BY

Errori Comuni

Query sul main thread

// SBAGLIATO: blocca il server!
public void onJoin(PlayerJoinEvent event) {
    PlayerStats stats = statsManager.getStats(event.getPlayer().getUniqueId());
}

// CORRETTO: usa async
public void onJoin(PlayerJoinEvent event) {
    Bukkit.getScheduler().runTaskAsynchronously(plugin, () -> {
        PlayerStats stats = statsManager.getStats(event.getPlayer().getUniqueId());
    });
}

Non chiudere risorse

// SBAGLIATO: memory leak!
Statement statement = connection.createStatement();
statement.execute(sql);

// CORRETTO: try-with-resources
try (Statement statement = connection.createStatement()) {
    statement.execute(sql);
}

SQL Injection

// SBAGLIATO: vulnerabile a SQL injection!
String sql = "SELECT * FROM players WHERE name = '" + playerName + "'";

// CORRETTO: usa PreparedStatement
String sql = "SELECT * FROM players WHERE name = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, playerName);

Conclusione

L’integrazione di database nei plugin Spigot/Paper permette di salvare dati persistenti in modo efficiente e scalabile. Che tu scelga SQLite per semplicità o MySQL per network multi-server, seguire le best practices garantisce performance e sicurezza.