Skip to content

Database

Jump to bottom
Samuel De Oliveira edited this page Jan 12, 2026 · 2 revisions

🗄️ Database

Manage SQL database connections with automatic pooling, reconnection, and multi-database support.

Overview

The Database API provides a robust system for managing SQL database connections:

  • MySQL Support — Full MySQL/MariaDB compatibility
  • PostgreSQL Support — Native PostgreSQL support
  • Connection Pooling — Global pool with labeled connections
  • Auto-Reconnect — Automatic reconnection on failure
  • Multiple Databases — Manage multiple database connections
  • Simple API — Easy-to-use connection management

Table of Contents

Getting Started

Adding Dependencies

The Database API is included in DreamAPI. No additional dependencies required.

Basic Setup

import fr.dreamin.dreamapi.api.database.sql.MysqlManager;
import fr.dreamin.dreamapi.api.database.sql.core.SqlPool;

public class MyPlugin extends DreamPlugin {

  @Override
  public void onDreamEnable() {
    // Register MySQL connection
    MysqlManager db = SqlPool.register(new MysqlManager(
      "main",              // Label
      "localhost",         // Host
      3306,                // Port
      "minecraft",         // Database name
      "root",              // Username
      "password"           // Password
    ));

    // Test connection
    if (db.testConnection()) {
      getLogger().info("Database connected!");
    }
  }

  @Override
  public void onDreamDisable() {
    // Close all connections
    SqlPool.closeAll();
  }
}

MySQL Database

Create Connection

import fr.dreamin.dreamapi.api.database.sql.MysqlManager;

MysqlManager mysql = new MysqlManager(
  "main",              // Unique label
  "localhost",         // Host
  3306,                // Port
  "minecraft_db",      // Database name
  "root",              // Username
  "password"           // Password
);

// Connect
mysql.connect();

With Auto-Reconnect

MysqlManager mysql = new MysqlManager(
  "main",
  "localhost",
  3306,
  "minecraft_db",
  "root",
  "password",
  true  // Enable auto-reconnect
);

mysql.connect();

Connection Methods

// Check if connected
if (mysql.isOnline()) {
  getLogger().info("Database is online");
}

// Get connection
Connection conn = mysql.getConnection();

// Test connection
if (mysql.testConnection()) {
  getLogger().info("Connection is healthy");
}

// Reconnect
mysql.reconnect();

// Disconnect
mysql.disconnect();

Execute Query

try (Connection conn = mysql.getConnection();
     PreparedStatement stmt = conn.prepareStatement("SELECT * FROM players WHERE uuid = ?")) {

  stmt.setString(1, player.getUniqueId().toString());

  try (ResultSet rs = stmt.executeQuery()) {
    if (rs.next()) {
      String name = rs.getString("name");
      int level = rs.getInt("level");

      player.sendMessage(
        Component.text("Name: " + name, NamedTextColor.GREEN)
      );
      player.sendMessage(
        Component.text("Level: " + level, NamedTextColor.GOLD)
      );
    }
  }
} catch (SQLException e) {
  getLogger().severe("Query failed: " + e.getMessage());
}

PostgreSQL Database

Create Connection

import fr.dreamin.dreamapi.api.database.sql.PostgresManager;

PostgresManager postgres = new PostgresManager(
  "main",              // Unique label
  "localhost",         // Host
  5432,                // Port
  "minecraft_db",      // Database name
  "postgres",          // Username
  "password"           // Password
);

postgres.connect();

Usage

PostgreSQL usage is identical to MySQL:

try (Connection conn = postgres.getConnection();
     PreparedStatement stmt = conn.prepareStatement("SELECT * FROM players")) {

  try (ResultSet rs = stmt.executeQuery()) {
    while (rs.next()) {
      String name = rs.getString("name");
      getLogger().info("Player: " + name);
    }
  }
} catch (SQLException e) {
  getLogger().severe("Query failed: " + e.getMessage());
}

SqlPool — Connection Pool

The SqlPool manages all database connections globally.

Register Database

import fr.dreamin.dreamapi.api.database.sql.core.SqlPool;

// Register and connect automatically
MysqlManager db = SqlPool.register(new MysqlManager(
  "main",
  "localhost",
  3306,
  "minecraft_db",
  "root",
  "password"
));

Retrieve Database

// Get by label (throws if not found)
SqlManager db = SqlPool.get("main");

// Find by label (returns Optional)
Optional<SqlManager> optional = SqlPool.find("main");
if (optional.isPresent()) {
  SqlManager db = optional.get();
}

Get or Register

// Get existing or create new
MysqlManager db = SqlPool.getOrRegister("main", () -> 
  new MysqlManager("main", "localhost", 3306, "minecraft_db", "root", "password")
);

Manage Connections

// Check if exists
if (SqlPool.exists("main")) {
  getLogger().info("Database 'main' is registered");
}

// Get all registered databases
Collection<SqlManager> all = SqlPool.all();
for (SqlManager db : all) {
  getLogger().info("Database: " + db.getLabel());
}

// Unregister single database
SqlPool.unregister("main");

// Close all connections
SqlPool.closeAll();

Query Examples

Create Table

public void createTables() {
  String sql = "CREATE TABLE IF NOT EXISTS players (" +
    "id INT AUTO_INCREMENT PRIMARY KEY, " +
    "uuid VARCHAR(36) UNIQUE NOT NULL, " +
    "name VARCHAR(16) NOT NULL, " +
    "level INT DEFAULT 0, " +
    "coins INT DEFAULT 0, " +
    "last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
    ")";

  try (Connection conn = SqlPool.get("main").getConnection();
       Statement stmt = conn.createStatement()) {

    stmt.execute(sql);
    getLogger().info("Table 'players' created");

  } catch (SQLException e) {
    getLogger().severe("Failed to create table: " + e.getMessage());
  }
}

Insert Data

public void savePlayer(Player player, int level, int coins) {
  String sql = "INSERT INTO players (uuid, name, level, coins) VALUES (?, ?, ?, ?) " +
    "ON DUPLICATE KEY UPDATE " +
    "name = VALUES(name), level = VALUES(level), coins = VALUES(coins), " +
    "last_login = CURRENT_TIMESTAMP";

  try (Connection conn = SqlPool.get("main").getConnection();
       PreparedStatement stmt = conn.prepareStatement(sql)) {

    stmt.setString(1, player.getUniqueId().toString());
    stmt.setString(2, player.getName());
    stmt.setInt(3, level);
    stmt.setInt(4, coins);

    int rows = stmt.executeUpdate();
    getLogger().info("Saved player " + player.getName() + " (" + rows + " rows)");

  } catch (SQLException e) {
    getLogger().severe("Failed to save player: " + e.getMessage());
  }
}

Select Data

public PlayerData loadPlayer(UUID uuid) {
  String sql = "SELECT * FROM players WHERE uuid = ?";

  try (Connection conn = SqlPool.get("main").getConnection();
       PreparedStatement stmt = conn.prepareStatement(sql)) {

    stmt.setString(1, uuid.toString());

    try (ResultSet rs = stmt.executeQuery()) {
      if (rs.next()) {
        return new PlayerData(
          rs.getString("name"),
          rs.getInt("level"),
          rs.getInt("coins")
        );
      }
    }
  } catch (SQLException e) {
    getLogger().severe("Failed to load player: " + e.getMessage());
  }

  return null;
}

Update Data

public void addCoins(UUID uuid, int amount) {
  String sql = "UPDATE players SET coins = coins + ? WHERE uuid = ?";

  try (Connection conn = SqlPool.get("main").getConnection();
       PreparedStatement stmt = conn.prepareStatement(sql)) {

    stmt.setInt(1, amount);
    stmt.setString(2, uuid.toString());

    int rows = stmt.executeUpdate();
    if (rows > 0) {
      getLogger().info("Added " + amount + " coins");
    }

  } catch (SQLException e) {
    getLogger().severe("Failed to add coins: " + e.getMessage());
  }
}

Delete Data

public void deletePlayer(UUID uuid) {
  String sql = "DELETE FROM players WHERE uuid = ?";

  try (Connection conn = SqlPool.get("main").getConnection();
       PreparedStatement stmt = conn.prepareStatement(sql)) {

    stmt.setString(1, uuid.toString());

    int rows = stmt.executeUpdate();
    if (rows > 0) {
      getLogger().info("Deleted player data");
    }

  } catch (SQLException e) {
    getLogger().severe("Failed to delete player: " + e.getMessage());
  }
}

Batch Operations

public void savePlayers(List<Player> players) {
  String sql = "INSERT INTO players (uuid, name) VALUES (?, ?) " +
    "ON DUPLICATE KEY UPDATE name = VALUES(name)";

  try (Connection conn = SqlPool.get("main").getConnection();
       PreparedStatement stmt = conn.prepareStatement(sql)) {

    for (Player player : players) {
      stmt.setString(1, player.getUniqueId().toString());
      stmt.setString(2, player.getName());
      stmt.addBatch();
    }

    int[] results = stmt.executeBatch();
    getLogger().info("Saved " + results.length + " players in batch");

  } catch (SQLException e) {
    getLogger().severe("Batch save failed: " + e.getMessage());
  }
}

Advanced Usage

Multiple Databases

public class MultiDbPlugin extends DreamPlugin {

  @Override
  public void onPluginEnable() {
    // Main database
    SqlPool.register(new MysqlManager(
      "main",
      "localhost",
      3306,
      "minecraft_main",
      "root",
      "password"
    ));

    // Stats database
    SqlPool.register(new MysqlManager(
      "stats",
      "localhost",
      3306,
      "minecraft_stats",
      "root",
      "password"
    ));

    // Economy database
    SqlPool.register(new PostgresManager(
      "economy",
      "localhost",
      5432,
      "minecraft_economy",
      "postgres",
      "password"
    ));
  }

  public void savePlayer(Player player) {
    // Save to main database
    try (Connection conn = SqlPool.get("main").getConnection()) {
      // Save player data
    } catch (SQLException e) {
      e.printStackTrace();
    }

    // Save stats to separate database
    try (Connection conn = SqlPool.get("stats").getConnection()) {
      // Save player stats
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

Async Operations

import org.bukkit.Bukkit;

public void savePlayerAsync(Player player, int level, int coins) {
  Bukkit.getScheduler().runTaskAsynchronously(this, () -> {
    String sql = "INSERT INTO players (uuid, name, level, coins) VALUES (?, ?, ?, ?)";

    try (Connection conn = SqlPool.get("main").getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setString(1, player.getUniqueId().toString());
      stmt.setString(2, player.getName());
      stmt.setInt(3, level);
      stmt.setInt(4, coins);

      stmt.executeUpdate();

      // Return to main thread for Bukkit operations
      Bukkit.getScheduler().runTask(this, () -> {
        player.sendMessage(
          Component.text("Data saved!", NamedTextColor.GREEN)
        );
      });

    } catch (SQLException e) {
      getLogger().severe("Failed to save player: " + e.getMessage());
    }
  });
}

Transaction Example

public void transferCoins(UUID from, UUID to, int amount) {
  Connection conn = null;
  try {
    conn = SqlPool.get("main").getConnection();
    conn.setAutoCommit(false);  // Start transaction

    // Deduct from sender
    try (PreparedStatement stmt = conn.prepareStatement(
      "UPDATE players SET coins = coins - ? WHERE uuid = ?")) {
      stmt.setInt(1, amount);
      stmt.setString(2, from.toString());
      stmt.executeUpdate();
    }

    // Add to receiver
    try (PreparedStatement stmt = conn.prepareStatement(
      "UPDATE players SET coins = coins + ? WHERE uuid = ?")) {
      stmt.setInt(1, amount);
      stmt.setString(2, to.toString());
      stmt.executeUpdate();
    }

    conn.commit();  // Commit transaction
    getLogger().info("Transferred " + amount + " coins");

  } catch (SQLException e) {
    if (conn != null) {
      try {
        conn.rollback();  // Rollback on error
        getLogger().warning("Transaction rolled back");
      } catch (SQLException ex) {
        ex.printStackTrace();
      }
    }
    getLogger().severe("Transfer failed: " + e.getMessage());
  } finally {
    if (conn != null) {
      try {
        conn.setAutoCommit(true);
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

Best Practices

✅ Do's

  • Use try-with-resources — Always close connections, statements, and result sets
  • Use prepared statements — Prevent SQL injection
  • Register on enable — Initialize databases in onPluginEnable()
  • Close on disable — Call SqlPool.closeAll() in onPluginDisable()
  • Use async for heavy queries — Don't block the main thread
  • Handle exceptions — Always catch SQLException
  • Test connections — Use testConnection() on startup
  • Use connection pooling — Consider HikariCP for production

❌ Don'ts

  • Don't concatenate SQL — Use prepared statements instead
  • Don't forget to close resources — Use try-with-resources
  • Don't run queries on main thread — Use async for database operations
  • Don't ignore exceptions — Log and handle all errors
  • Don't hardcode credentials — Use configuration files
  • Don't leak connections — Always close in finally block
  • Don't use SELECT * — Specify columns explicitly

Troubleshooting

Connection Refused

Failed to connect: Connection refused

Solution:

  • Check if MySQL/PostgreSQL is running
  • Verify host and port are correct
  • Check firewall settings
  • Ensure database exists

Access Denied

Failed to connect: Access denied for user 'root'@'localhost'

Solution:

  • Verify username and password
  • Check user privileges
  • Grant permissions: GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

Table Doesn't Exist

Table 'minecraft.players' doesn't exist

Solution:

  • Create table before querying
  • Run table creation in onPluginEnable()
  • Check database name is correct

Connection Timeout

Communications link failure

Solution:

  • Enable auto-reconnect
  • Check network stability
  • Increase connection timeout
  • Use connection pooling

Memory Leak

Solution:

  • Always close ResultSet, Statement, Connection
  • Use try-with-resources
  • Don't store connections as fields

Complete Example

public class DatabasePlugin extends DreamPlugin {

  private static final String DB_LABEL = "main";

  @Override
  public void onDreamEnable() {
    // Register database
    SqlPool.register(new MysqlManager(
      DB_LABEL,
      "localhost",
      3306,
      "minecraft",
      "root",
      "password",
      true  // auto-reconnect
    ));

    // Create tables
    createTables();

    // Test connection
    if (SqlPool.get(DB_LABEL).testConnection()) {
      getLogger().info("Database connected successfully!");
    } else {
      getLogger().severe("Database connection test failed!");
      getServer().getPluginManager().disablePlugin(this);
    }
  }

  @Override
  public void onDreamDisable() {
    // Close all connections
    SqlPool.closeAll();
  }

  private void createTables() {
    String sql = "CREATE TABLE IF NOT EXISTS players (" +
      "id INT AUTO_INCREMENT PRIMARY KEY, " +
      "uuid VARCHAR(36) UNIQUE NOT NULL, " +
      "name VARCHAR(16) NOT NULL, " +
      "level INT DEFAULT 0, " +
      "coins INT DEFAULT 0, " +
      "last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
      "INDEX idx_uuid (uuid)" +
      ")";

    try (Connection conn = SqlPool.get(DB_LABEL).getConnection();
         Statement stmt = conn.createStatement()) {

      stmt.execute(sql);
      getLogger().info("Tables created");

    } catch (SQLException e) {
      getLogger().severe("Failed to create tables: " + e.getMessage());
    }
  }

  public void savePlayer(Player player, int level, int coins) {
    Bukkit.getScheduler().runTaskAsynchronously(this, () -> {
      String sql = "INSERT INTO players (uuid, name, level, coins) VALUES (?, ?, ?, ?) " +
        "ON DUPLICATE KEY UPDATE " +
        "name = VALUES(name), level = VALUES(level), coins = VALUES(coins), " +
        "last_login = CURRENT_TIMESTAMP";

      try (Connection conn = SqlPool.get(DB_LABEL).getConnection();
           PreparedStatement stmt = conn.prepareStatement(sql)) {

        stmt.setString(1, player.getUniqueId().toString());
        stmt.setString(2, player.getName());
        stmt.setInt(3, level);
        stmt.setInt(4, coins);

        stmt.executeUpdate();

        Bukkit.getScheduler().runTask(this, () -> {
          player.sendMessage(
            Component.text("Data saved!", NamedTextColor.GREEN)
          );
        });

      } catch (SQLException e) {
        getLogger().severe("Failed to save player: " + e.getMessage());
      }
    });
  }

  public void loadPlayer(Player player) {
    Bukkit.getScheduler().runTaskAsynchronously(this, () -> {
      String sql = "SELECT * FROM players WHERE uuid = ?";

      try (Connection conn = SqlPool.get(DB_LABEL).getConnection();
           PreparedStatement stmt = conn.prepareStatement(sql)) {

        stmt.setString(1, player.getUniqueId().toString());

        try (ResultSet rs = stmt.executeQuery()) {
          if (rs.next()) {
            int level = rs.getInt("level");
            int coins = rs.getInt("coins");

            Bukkit.getScheduler().runTask(this, () -> {
              player.sendMessage(
                Component.text("Level: " + level, NamedTextColor.GOLD)
              );
              player.sendMessage(
                Component.text("Coins: " + coins, NamedTextColor.YELLOW)
              );
            });
          }
        }
      } catch (SQLException e) {
        getLogger().severe("Failed to load player: " + e.getMessage());
      }
    });
  }
}

Next Steps

  • 🔧 Configuration — Store database credentials in config
  • 📦 Services — Use dependency injection for database access
  • ⚡ Scheduler — Schedule database cleanup tasks

Clone this wiki locally