feature: connection pooling & SQL refactoring

Bukkit/build.gradle.kts

@@ -89,6 +89,8 @@ tasks.named<ShadowJar>("shadowJar") {
     relocate("net.jcip", "com.plotsquared.core.annotations.jcip")
     relocate("edu.umd.cs.findbugs", "com.plotsquared.core.annotations.findbugs")
     relocate("com.intellectualsites.annotations", "com.plotsquared.core.annotations.informative")
+    relocate("org.jdbi.v3", "com.plotsquared.core.jdbi")
+    relocate("com.zaxxer.hikari", "com.plotsquared.core.hikari")
 
     // Get rid of all the libs which are 100% unused.
     minimize()

Bukkit/src/main/java/com/plotsquared/bukkit/BukkitPlatform.java

@@ -82,6 +82,8 @@ import com.plotsquared.core.inject.annotations.DefaultGenerator;
 import com.plotsquared.core.inject.annotations.ImpromptuPipeline;
 import com.plotsquared.core.inject.annotations.WorldConfig;
 import com.plotsquared.core.inject.annotations.WorldFile;
+import com.plotsquared.core.inject.modules.DatabaseModule;
+import com.plotsquared.core.inject.modules.JdbiModule;
 import com.plotsquared.core.inject.modules.PlotSquaredModule;
 import com.plotsquared.core.listener.PlotListener;
 import com.plotsquared.core.listener.WESubscriber;
@@ -140,6 +142,7 @@ import org.checkerframework.checker.nullness.qual.NonNull;
 import org.checkerframework.checker.nullness.qual.Nullable;
 import org.incendo.serverlib.ServerLib;
 
+import javax.xml.crypto.Data;
 import java.io.File;
 import java.io.IOException;
 import java.lang.reflect.Method;
@@ -292,7 +295,9 @@ public final class BukkitPlatform extends JavaPlugin implements Listener, PlotPl
                         new WorldManagerModule(),
                         new PlotSquaredModule(),
                         new BukkitModule(this),
-                        new BackupModule()
+                        new BackupModule(),
+                        new JdbiModule(),
+                        new DatabaseModule()
                 );
         this.injector.injectMembers(this);
 

Core/build.gradle.kts

@@ -37,6 +37,11 @@ dependencies {
     // Logging
     compileOnlyApi(libs.log4j)
 
+    // Database
+    api(libs.hikaricp)
+    api(libs.jdbiCore)
+    api(libs.jdbiGuice)
+
     // Other libraries
     api(libs.prtree)
     api(libs.aopalliance)

Core/src/main/java/com/plotsquared/core/PlotSquared.java

@@ -18,6 +18,7 @@
  */
 package com.plotsquared.core;
 
+import com.google.inject.Key;
 import com.plotsquared.core.configuration.ConfigurationSection;
 import com.plotsquared.core.configuration.ConfigurationUtil;
 import com.plotsquared.core.configuration.MemorySection;
@@ -31,14 +32,12 @@ import com.plotsquared.core.configuration.caption.load.DefaultCaptionProvider;
 import com.plotsquared.core.configuration.file.YamlConfiguration;
 import com.plotsquared.core.configuration.serialization.ConfigurationSerialization;
 import com.plotsquared.core.database.DBFunc;
-import com.plotsquared.core.database.Database;
-import com.plotsquared.core.database.MySQL;
 import com.plotsquared.core.database.SQLManager;
-import com.plotsquared.core.database.SQLite;
 import com.plotsquared.core.generator.GeneratorWrapper;
 import com.plotsquared.core.generator.HybridPlotWorld;
 import com.plotsquared.core.generator.HybridUtils;
 import com.plotsquared.core.generator.IndependentPlotGenerator;
+import com.plotsquared.core.inject.annotations.PlotDatabase;
 import com.plotsquared.core.inject.factory.HybridPlotWorldFactory;
 import com.plotsquared.core.listener.PlotListener;
 import com.plotsquared.core.location.Location;
@@ -75,7 +74,9 @@ import org.checkerframework.checker.nullness.qual.MonotonicNonNull;
 import org.checkerframework.checker.nullness.qual.NonNull;
 import org.checkerframework.checker.nullness.qual.Nullable;
 
+import javax.sql.DataSource;
 import java.io.BufferedReader;
+import java.io.Closeable;
 import java.io.File;
 import java.io.FileInputStream;
 import java.io.FileOutputStream;
@@ -1236,8 +1237,11 @@ public class PlotSquared {
             DBFunc.validatePlots(plots);
 
             // Close the connection
-            DBFunc.close();
+            final DataSource dataSource = platform().injector().getInstance(Key.get(DataSource.class, PlotDatabase.class));
-        } catch (NullPointerException throwable) {
+            if (dataSource instanceof Closeable closeable) {
+                closeable.close();
+            }
+        } catch (IOException | NullPointerException throwable) {
             LOGGER.error("Could not close database connection", throwable);
             throwable.printStackTrace();
         }
@@ -1289,26 +1293,7 @@ public class PlotSquared {
             if (DBFunc.dbManager != null) {
                 DBFunc.dbManager.close();
             }
-            Database database;
+            DBFunc.dbManager = platform().injector().getInstance(SQLManager.class);
-            if (Storage.MySQL.USE) {
-                database = new MySQL(Storage.MySQL.HOST, Storage.MySQL.PORT, Storage.MySQL.DATABASE,
-                        Storage.MySQL.USER, Storage.MySQL.PASSWORD
-                );
-            } else if (Storage.SQLite.USE) {
-                File file = FileUtils.getFile(platform.getDirectory(), Storage.SQLite.DB + ".db");
-                database = new SQLite(file);
-            } else {
-                LOGGER.error("No storage type is set. Disabling PlotSquared");
-                this.platform.shutdown(); //shutdown used instead of disable because no database is set
-                return;
-            }
-            DBFunc.dbManager = new SQLManager(
-                    database,
-                    Storage.PREFIX,
-                    this.eventDispatcher,
-                    this.plotListener,
-                    this.worldConfiguration
-            );
             this.plots_tmp = DBFunc.getPlots();
             if (getPlotAreaManager() instanceof SinglePlotAreaManager) {
                 SinglePlotArea area = ((SinglePlotAreaManager) getPlotAreaManager()).getArea();
@@ -1322,13 +1307,13 @@ public class PlotSquared {
             }
             this.clustersTmp = DBFunc.getClusters();
             LOGGER.info("Connection to database established. Type: {}", Storage.MySQL.USE ? "MySQL" : "SQLite");
-        } catch (ClassNotFoundException | SQLException e) {
+        } catch (Exception e) {
             LOGGER.error(
                     "Failed to open database connection ({}). Disabling PlotSquared",
                     Storage.MySQL.USE ? "MySQL" : "SQLite"
             );
             LOGGER.error("==== Here is an ugly stacktrace, if you are interested in those things ===");
-            e.printStackTrace();
+            LOGGER.error("", e);
             LOGGER.error("==== End of stacktrace ====");
             LOGGER.error(
                     "Please go to the {} 'storage.yml' and configure the database correctly",

Core/src/main/java/com/plotsquared/core/database/Database.java

@@ -29,6 +29,7 @@ import java.sql.Statement;
  * @author -_Husky_-
  * @author tips48
  */
+@Deprecated(forRemoval = true)
 public abstract class Database {
 
     public abstract Connection forceConnection() throws SQLException, ClassNotFoundException;

Core/src/main/java/com/plotsquared/core/database/MySQL.java

@@ -33,6 +33,7 @@ import java.sql.Statement;
  * @author -_Husky_-
  * @author tips48
  */
+@Deprecated(forRemoval = true)
 public class MySQL extends Database {
 
     private final String user;

Core/src/main/java/com/plotsquared/core/database/SQLManager.java

@@ -25,6 +25,7 @@ import com.plotsquared.core.configuration.Settings;
 import com.plotsquared.core.configuration.Storage;
 import com.plotsquared.core.configuration.caption.CaptionUtility;
 import com.plotsquared.core.configuration.file.YamlConfiguration;
+import com.plotsquared.core.inject.annotations.PlotDatabase;
 import com.plotsquared.core.inject.annotations.WorldConfig;
 import com.plotsquared.core.listener.PlotListener;
 import com.plotsquared.core.location.BlockLoc;
@@ -44,10 +45,14 @@ import com.plotsquared.core.util.HashUtil;
 import com.plotsquared.core.util.StringMan;
 import com.plotsquared.core.util.task.RunnableVal;
 import com.plotsquared.core.util.task.TaskManager;
+import jakarta.inject.Inject;
 import org.apache.logging.log4j.LogManager;
 import org.apache.logging.log4j.Logger;
 import org.checkerframework.checker.nullness.qual.NonNull;
+import org.checkerframework.checker.nullness.qual.Nullable;
 
+import javax.sql.DataSource;
+import java.io.Closeable;
 import java.sql.Connection;
 import java.sql.DatabaseMetaData;
 import java.sql.PreparedStatement;
@@ -92,12 +97,12 @@ public class SQLManager implements AbstractDB {
 
     // Private Final
     private final String prefix;
-    private final Database database;
     private final boolean mySQL;
     @SuppressWarnings({"unused", "FieldCanBeLocal"})
     private final EventDispatcher eventDispatcher;
     @SuppressWarnings({"unused", "FieldCanBeLocal"})
     private final PlotListener plotListener;
+    private final DataSource dataSource;
     private final YamlConfiguration worldConfiguration;
     /**
      * important tasks
@@ -129,21 +134,12 @@ public class SQLManager implements AbstractDB {
      */
     public volatile ConcurrentHashMap<PlotCluster, Queue<UniqueStatement>> clusterTasks;
     // Private
-    private Connection connection;
     private boolean supportsGetGeneratedKeys;
     private boolean closed = false;
 
-    /**
+    @Inject
-     * Constructor
-     *
-     * @param database
-     * @param prefix   prefix
-     * @throws SQLException
-     * @throws ClassNotFoundException
-     */
     public SQLManager(
-            final @NonNull Database database,
+            final @NonNull @PlotDatabase DataSource dataSource,
-            final @NonNull String prefix,
             final @NonNull EventDispatcher eventDispatcher,
             final @NonNull PlotListener plotListener,
             @WorldConfig final @NonNull YamlConfiguration worldConfiguration
@@ -153,24 +149,26 @@ public class SQLManager implements AbstractDB {
         this.eventDispatcher = eventDispatcher;
         this.plotListener = plotListener;
         this.worldConfiguration = worldConfiguration;
-        this.database = database;
+        this.dataSource = dataSource;
-        this.connection = database.openConnection();
+        this.mySQL = Storage.MySQL.USE;
-        final DatabaseMetaData databaseMetaData = this.connection.getMetaData();
-        this.supportsGetGeneratedKeys = databaseMetaData.supportsGetGeneratedKeys();
-        this.mySQL = database instanceof MySQL;
         this.globalTasks = new ConcurrentLinkedQueue<>();
         this.notifyTasks = new ConcurrentLinkedQueue<>();
         this.plotTasks = new ConcurrentHashMap<>();
         this.playerTasks = new ConcurrentHashMap<>();
         this.clusterTasks = new ConcurrentHashMap<>();
-        this.prefix = prefix;
+        this.prefix = Storage.PREFIX;
 
-        if (mySQL && !supportsGetGeneratedKeys) {
+        try (final Connection connection = dataSource.getConnection()) {
-            String driver = databaseMetaData.getDriverName();
+            final DatabaseMetaData metaData = connection.getMetaData();
-            String driverVersion = databaseMetaData.getDriverVersion();
+            this.supportsGetGeneratedKeys = metaData.supportsGetGeneratedKeys();
+
+            if (this.mySQL && !this.supportsGetGeneratedKeys) {
+                final String driver = metaData.getDriverName();
+                final String driverVersion = metaData.getDriverVersion();
                 throw new SQLException("Database Driver for MySQL does not support Statement#getGeneratedKeys - which breaks " +
                         "PlotSquared functionality (Using " + driver + ":" + driverVersion + ")");
             }
+        }
 
         this.SET_OWNER = "UPDATE `" + this.prefix
                 + "plot` SET `owner` = ? WHERE `plot_id_x` = ? AND `plot_id_z` = ? AND `world` = ?";
@@ -220,11 +218,6 @@ public class SQLManager implements AbstractDB {
                         !globalTasks.isEmpty() || !playerTasks.isEmpty() || !plotTasks.isEmpty()
                                 || !clusterTasks.isEmpty();
                 if (hasTask) {
-                    if (SQLManager.this.mySQL && System.currentTimeMillis() - last > 550000
-                            || !isValid()) {
-                        last = System.currentTimeMillis();
-                        reconnect();
-                    }
                     if (!sendBatch()) {
                         try {
                             if (!getNotifyTasks().isEmpty()) {
@@ -249,32 +242,6 @@ public class SQLManager implements AbstractDB {
         });
     }
 
-    public boolean isValid() {
-        try {
-            if (connection.isClosed()) {
-                return false;
-            }
-        } catch (SQLException e) {
-            return false;
-        }
-        try (PreparedStatement stmt = this.connection.prepareStatement("SELECT 1")) {
-            stmt.execute();
-            return true;
-        } catch (Throwable e) {
-            return false;
-        }
-    }
-
-    public void reconnect() {
-        try {
-            close();
-            SQLManager.this.closed = false;
-            SQLManager.this.connection = database.forceConnection();
-        } catch (SQLException | ClassNotFoundException e) {
-            e.printStackTrace();
-        }
-    }
-
     public synchronized Queue<Runnable> getGlobalTasks() {
         return this.globalTasks;
     }
@@ -293,7 +260,7 @@ public class SQLManager implements AbstractDB {
             task = new UniqueStatement(String.valueOf(plot.hashCode())) {
 
                 @Override
-                public PreparedStatement get() {
+                public @Nullable PreparedStatement get(final @NonNull Connection connection) {
                     return null;
                 }
 
@@ -327,7 +294,7 @@ public class SQLManager implements AbstractDB {
             task = new UniqueStatement(String.valueOf(uuid.hashCode())) {
 
                 @Override
-                public PreparedStatement get() {
+                public PreparedStatement get(final @NonNull Connection connection) {
                     return null;
                 }
 
@@ -358,7 +325,7 @@ public class SQLManager implements AbstractDB {
             task = new UniqueStatement(String.valueOf(cluster.hashCode())) {
 
                 @Override
-                public PreparedStatement get() {
+                public @Nullable PreparedStatement get(final @NonNull Connection connection) {
                     return null;
                 }
 
@@ -390,10 +357,10 @@ public class SQLManager implements AbstractDB {
     }
 
     public boolean sendBatch() {
-        try {
+        try (Connection connection = this.dataSource().getConnection()) {
             if (!getGlobalTasks().isEmpty()) {
-                if (this.connection.getAutoCommit()) {
+                if (connection.getAutoCommit()) {
-                    this.connection.setAutoCommit(false);
+                    connection.setAutoCommit(false);
                 }
                 Runnable task = getGlobalTasks().remove();
                 if (task != null) {
@@ -404,18 +371,19 @@ public class SQLManager implements AbstractDB {
                         LOGGER.error("============ DATABASE ERROR ============");
                         LOGGER.error("There was an error updating the database.");
                         LOGGER.error(" - It will be corrected on shutdown");
-                        e.printStackTrace();
+                        LOGGER.error("", e);
                         LOGGER.error("========================================");
                     }
                 }
-                commit();
+                commit(connection);
                 return true;
             }
+
             int count = -1;
             if (!this.plotTasks.isEmpty()) {
                 count = Math.max(count, 0);
-                if (this.connection.getAutoCommit()) {
+                if (connection.getAutoCommit()) {
-                    this.connection.setAutoCommit(false);
+                    connection.setAutoCommit(false);
                 }
                 String method = null;
                 PreparedStatement statement = null;
@@ -469,8 +437,8 @@ public class SQLManager implements AbstractDB {
             }
             if (!this.playerTasks.isEmpty()) {
                 count = Math.max(count, 0);
-                if (this.connection.getAutoCommit()) {
+                if (connection.getAutoCommit()) {
-                    this.connection.setAutoCommit(false);
+                    connection.setAutoCommit(false);
                 }
                 String method = null;
                 PreparedStatement statement = null;
@@ -514,8 +482,8 @@ public class SQLManager implements AbstractDB {
             }
             if (!this.clusterTasks.isEmpty()) {
                 count = Math.max(count, 0);
-                if (this.connection.getAutoCommit()) {
+                if (connection.getAutoCommit()) {
-                    this.connection.setAutoCommit(false);
+                    connection.setAutoCommit(false);
                 }
                 String method = null;
                 PreparedStatement statement = null;
@@ -559,12 +527,12 @@ public class SQLManager implements AbstractDB {
                 }
             }
             if (count > 0) {
-                commit();
+                commit(connection);
                 return true;
             }
             if (count != -1) {
-                if (!this.connection.getAutoCommit()) {
+                if (!connection.getAutoCommit()) {
-                    this.connection.setAutoCommit(true);
+                    connection.setAutoCommit(true);
                 }
             }
             if (!this.clusterTasks.isEmpty()) {
@@ -584,10 +552,6 @@ public class SQLManager implements AbstractDB {
         return false;
     }
 
-    public Connection getConnection() {
-        return this.connection;
-    }
-
     /**
      * Set Plot owner
      *
@@ -606,8 +570,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(SQLManager.this.SET_OWNER);
+                return connection.prepareStatement(SQLManager.this.SET_OWNER);
             }
         });
     }
@@ -615,7 +579,7 @@ public class SQLManager implements AbstractDB {
     @Override
     public void createPlotsAndData(final List<Plot> myList, final Runnable whenDone) {
         addGlobalTask(() -> {
-            try {
+            try (Connection connection = this.dataSource().getConnection()) {
                 // Create the plots
                 createPlots(myList, () -> {
                     final Map<PlotId, Integer> idMap = new HashMap<>();
@@ -632,7 +596,7 @@ public class SQLManager implements AbstractDB {
                         final ArrayList<UUIDPair> denied = new ArrayList<>();
 
                         // Populating structures
-                        try (PreparedStatement stmt = SQLManager.this.connection
+                        try (PreparedStatement stmt = connection
                                 .prepareStatement(SQLManager.this.GET_ALL_PLOTS);
                              ResultSet result = stmt.executeQuery()) {
                             while (result.next()) {
@@ -657,13 +621,14 @@ public class SQLManager implements AbstractDB {
                             }
                         }
 
-                        createFlags(idMap, myList, () -> createSettings(
+                        createFlags(connection, idMap, myList, () -> createSettings(
+                                connection,
                                 settings,
                                 () -> createTiers(helpers, "helpers",
                                         () -> createTiers(trusted, "trusted",
                                                 () -> createTiers(denied, "denied", () -> {
                                                     try {
-                                                        SQLManager.this.connection.commit();
+                                                        connection.commit();
                                                     } catch (SQLException e) {
                                                         e.printStackTrace();
                                                     }
@@ -677,7 +642,7 @@ public class SQLManager implements AbstractDB {
                     } catch (SQLException e) {
                         LOGGER.warn("Failed to set all flags and member tiers for plots", e);
                         try {
-                            SQLManager.this.connection.commit();
+                            connection.commit();
                         } catch (SQLException e1) {
                             e1.printStackTrace();
                         }
@@ -685,11 +650,6 @@ public class SQLManager implements AbstractDB {
                 });
             } catch (Exception e) {
                 LOGGER.warn("Warning! Failed to set all helper for plots", e);
-                try {
-                    SQLManager.this.connection.commit();
-                } catch (SQLException e1) {
-                    e1.printStackTrace();
-                }
             }
         });
     }
@@ -746,8 +706,13 @@ public class SQLManager implements AbstractDB {
         setBulk(myList, mod, whenDone);
     }
 
-    public void createFlags(Map<PlotId, Integer> ids, List<Plot> plots, Runnable whenDone) {
+    private void createFlags(
-        try (final PreparedStatement preparedStatement = this.connection.prepareStatement(
+            final @NonNull Connection connection,
+            final @NonNull Map<@NonNull PlotId, @NonNull Integer> ids,
+            final @NonNull List<@NonNull Plot> plots,
+            final @NonNull Runnable whenDone
+    ) {
+        try (final PreparedStatement preparedStatement = connection.prepareStatement(
                 "INSERT INTO `" + SQLManager.this.prefix
                         + "plot_flags`(`plot_id`, `flag`, `value`) VALUES(?, ?, ?)")) {
             for (final Plot plot : plots) {
@@ -845,6 +810,7 @@ public class SQLManager implements AbstractDB {
     }
 
     public <T> void setBulk(List<T> objList, StmtMod<T> mod, Runnable whenDone) {
+        try (Connection connection = this.dataSource().getConnection()) {
             int size = objList.size();
             if (size == 0) {
                 if (whenDone != null) {
@@ -872,13 +838,13 @@ public class SQLManager implements AbstractDB {
                     if (last == -1) {
                         last = subList.size();
                         statement = mod.getCreateMySQL(subList.size());
-                    preparedStmt = this.connection.prepareStatement(statement);
+                        preparedStmt = connection.prepareStatement(statement);
                     }
                     if (subList.size() != last || count % 5000 == 0 && count > 0) {
                         preparedStmt.executeBatch();
                         preparedStmt.close();
                         statement = mod.getCreateMySQL(subList.size());
-                    preparedStmt = this.connection.prepareStatement(statement);
+                        preparedStmt = connection.prepareStatement(statement);
                     }
                     for (int i = 0; i < subList.size(); i++) {
                         count++;
@@ -914,13 +880,13 @@ public class SQLManager implements AbstractDB {
                     if (last == -1) {
                         last = subList.size();
                         statement = mod.getCreateSQLite(subList.size());
-                    preparedStmt = this.connection.prepareStatement(statement);
+                        preparedStmt = connection.prepareStatement(statement);
                     }
                     if (subList.size() != last || count % 5000 == 0 && count > 0) {
                         preparedStmt.executeBatch();
                         preparedStmt.clearParameters();
                         statement = mod.getCreateSQLite(subList.size());
-                    preparedStmt = this.connection.prepareStatement(statement);
+                        preparedStmt = connection.prepareStatement(statement);
                     }
                     for (int i = 0; i < subList.size(); i++) {
                         count++;
@@ -937,7 +903,7 @@ public class SQLManager implements AbstractDB {
                 e.printStackTrace();
                 LOGGER.error("2: | {}", objList.get(0).getClass().getCanonicalName());
                 LOGGER.error("Could not bulk save!");
-            try (PreparedStatement preparedStmt = this.connection
+                try (PreparedStatement preparedStmt = connection
                         .prepareStatement(mod.getCreateSQL())) {
                     for (T obj : objList) {
                         mod.setSQL(preparedStmt, obj);
@@ -952,10 +918,16 @@ public class SQLManager implements AbstractDB {
             if (whenDone != null) {
                 whenDone.run();
             }
+        } catch (Exception e) {
+            LOGGER.error("Failed to create SQL connection", e);
+        }
     }
 
-    public void createSettings(final ArrayList<LegacySettings> myList, final Runnable whenDone) {
+    private void createSettings(
-        try (final PreparedStatement preparedStatement = this.connection.prepareStatement(
+            final @NonNull Connection connection,
+            final @NonNull List<@NonNull LegacySettings> myList,
+            final @NonNull Runnable whenDone) {
+        try (PreparedStatement preparedStatement = connection.prepareStatement(
                 "INSERT INTO `" + SQLManager.this.prefix + "plot_settings`"
                         + "(`plot_plot_id`,`biome`,`rain`,`custom_time`,`time`,`deny_entry`,`alias`,`merged`,`position`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
 
@@ -1082,8 +1054,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         SQLManager.this.CREATE_PLOT_SAFE,
                         Statement.RETURN_GENERATED_KEYS
                 );
@@ -1109,8 +1081,8 @@ public class SQLManager implements AbstractDB {
                                 }
 
                                 @Override
-                                public PreparedStatement get() throws SQLException {
+                                public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                                    return SQLManager.this.connection.prepareStatement(
+                                    return connection.prepareStatement(
                                             "INSERT INTO `" + SQLManager.this.prefix
                                                     + "plot_settings`(`plot_plot_id`) VALUES(?)");
                                 }
@@ -1129,17 +1101,14 @@ public class SQLManager implements AbstractDB {
         });
     }
 
-    public void commit() {
+    private void commit(final @NonNull Connection connection) {
-        if (this.closed) {
-            return;
-        }
         try {
-            if (!this.connection.getAutoCommit()) {
+            if (!connection.getAutoCommit()) {
-                this.connection.commit();
+                connection.commit();
-                this.connection.setAutoCommit(true);
+                connection.setAutoCommit(true);
             }
         } catch (SQLException e) {
-            e.printStackTrace();
+            LOGGER.error("Failed to commit database transaction", e);
         }
     }
 
@@ -1156,8 +1125,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection
+                return connection
                         .prepareStatement(SQLManager.this.CREATE_PLOT, Statement.RETURN_GENERATED_KEYS);
             }
 
@@ -1182,8 +1151,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "INSERT INTO `" + SQLManager.this.prefix
                                 + "plot_settings`(`plot_plot_id`) VALUES(?)");
             }
@@ -1198,6 +1167,7 @@ public class SQLManager implements AbstractDB {
      */
     @Override
     public void createTables() throws SQLException {
+        try (Connection connection = this.dataSource().getConnection()) {
             String[] tables =
                     new String[]{"plot", "plot_denied", "plot_helpers", "plot_comments", "plot_trusted",
                             "plot_rating", "plot_settings", "cluster", "player_meta", "plot_flags"};
@@ -1215,7 +1185,7 @@ public class SQLManager implements AbstractDB {
                 return;
             }
             boolean addConstraint = create == tables.length;
-        try (Statement stmt = this.connection.createStatement()) {
+            try (Statement stmt = connection.createStatement()) {
                 if (this.mySQL) {
                     stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + this.prefix + "plot` ("
                             + "`id` INT(11) NOT NULL AUTO_INCREMENT," + "`plot_id_x` INT(11) NOT NULL,"
@@ -1350,6 +1320,7 @@ public class SQLManager implements AbstractDB {
                 stmt.clearBatch();
             }
         }
+    }
 
     @Override
     public void deleteSettings(final Plot plot) {
@@ -1360,8 +1331,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "DELETE FROM `" + SQLManager.this.prefix
                                 + "plot_settings` WHERE `plot_plot_id` = ?");
             }
@@ -1380,8 +1351,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "DELETE FROM `" + SQLManager.this.prefix
                                 + "plot_helpers` WHERE `plot_plot_id` = ?");
             }
@@ -1400,8 +1371,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "DELETE FROM `" + SQLManager.this.prefix
                                 + "plot_trusted` WHERE `plot_plot_id` = ?");
             }
@@ -1420,8 +1391,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "DELETE FROM `" + SQLManager.this.prefix
                                 + "plot_denied` WHERE `plot_plot_id` = ?");
             }
@@ -1438,8 +1409,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "DELETE FROM `" + SQLManager.this.prefix
                                 + "plot_comments` WHERE `world` = ? AND `hashcode` = ?");
             }
@@ -1458,8 +1429,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "DELETE FROM `" + SQLManager.this.prefix
                                 + "plot_rating` WHERE `plot_plot_id` = ?");
             }
@@ -1486,8 +1457,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "DELETE FROM `" + SQLManager.this.prefix + "plot` WHERE `id` = ?");
             }
         });
@@ -1508,8 +1479,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "INSERT INTO `" + SQLManager.this.prefix
                                 + "plot_settings`(`plot_plot_id`) VALUES(?)");
             }
@@ -1518,16 +1489,12 @@ public class SQLManager implements AbstractDB {
 
     @Override
     public int getClusterId(PlotCluster cluster) {
-        if (cluster.temp > 0) {
+        try (Connection connection = this.dataSource.getConnection()) {
-            return cluster.temp;
-        }
-        try {
-            commit();
             if (cluster.temp > 0) {
                 return cluster.temp;
             }
             int c_id;
-            try (PreparedStatement stmt = this.connection.prepareStatement(
+            try (PreparedStatement stmt = connection.prepareStatement(
                     "SELECT `id` FROM `" + this.prefix
                             + "cluster` WHERE `pos1_x` = ? AND `pos1_z` = ? AND `pos2_x` = ? AND `pos2_z` = ? AND `world` = ? ORDER BY `timestamp` ASC")) {
                 stmt.setInt(1, cluster.getP1().getX());
@@ -1561,13 +1528,12 @@ public class SQLManager implements AbstractDB {
         if (plot.temp > 0) {
             return plot.temp;
         }
-        try {
+        try (Connection connection = this.dataSource().getConnection()) {
-            commit();
             if (plot.temp > 0) {
                 return plot.temp;
             }
             int id;
-            try (PreparedStatement statement = this.connection.prepareStatement(
+            try (PreparedStatement statement = connection.prepareStatement(
                     "SELECT `id` FROM `" + this.prefix
                             + "plot` WHERE `plot_id_x` = ? AND `plot_id_z` = ? AND world = ? ORDER BY `timestamp` ASC")) {
                 statement.setInt(1, plot.getId().getX());
@@ -1596,15 +1562,15 @@ public class SQLManager implements AbstractDB {
 
     @Override
     public void updateTables(int[] oldVersion) {
-        try {
+        try (Connection connection = this.dataSource().getConnection()) {
             if (this.mySQL && !PlotSquared.get().checkVersion(oldVersion, 3, 3, 2)) {
-                try (Statement stmt = this.connection.createStatement()) {
+                try (Statement stmt = connection.createStatement()) {
                     stmt.executeUpdate(
                             "ALTER TABLE `" + this.prefix + "plots` DROP INDEX `unique_alias`");
                 } catch (SQLException ignored) {
                 }
             }
-            DatabaseMetaData data = this.connection.getMetaData();
+            DatabaseMetaData data = connection.getMetaData();
             ResultSet rs =
                     data.getColumns(null, null, this.prefix + "plot_comments", "plot_plot_id");
             if (rs.next()) {
@@ -1612,7 +1578,7 @@ public class SQLManager implements AbstractDB {
                 rs = data.getColumns(null, null, this.prefix + "plot_comments", "hashcode");
                 if (!rs.next()) {
                     rs.close();
-                    try (Statement statement = this.connection.createStatement()) {
+                    try (Statement statement = connection.createStatement()) {
                         statement.addBatch("DROP TABLE `" + this.prefix + "plot_comments`");
                         if (Storage.MySQL.USE) {
                             statement.addBatch(
@@ -1647,7 +1613,7 @@ public class SQLManager implements AbstractDB {
             rs.close();
             rs = data.getColumns(null, null, this.prefix + "plot_denied", "plot_plot_id");
             if (rs.next()) {
-                try (Statement statement = this.connection.createStatement()) {
+                try (Statement statement = connection.createStatement()) {
                     statement.executeUpdate("DELETE FROM `" + this.prefix
                             + "plot_denied` WHERE `plot_plot_id` NOT IN (SELECT `id` FROM `"
                             + this.prefix + "plot`)");
@@ -1656,7 +1622,7 @@ public class SQLManager implements AbstractDB {
                 }
 
                 rs.close();
-                try (Statement statement = this.connection.createStatement()) {
+                try (Statement statement = connection.createStatement()) {
                     for (String table : new String[]{"plot_denied", "plot_helpers",
                             "plot_trusted"}) {
                         ResultSet result = statement.executeQuery(
@@ -1728,9 +1694,12 @@ public class SQLManager implements AbstractDB {
     @Override
     public boolean convertFlags() {
         final Map<Integer, Map<String, String>> flagMap = new HashMap<>();
-        try (Statement statement = this.connection.createStatement()) {
+        try (
-            try (ResultSet resultSet = statement
+                Connection connection = this.dataSource().getConnection();
-                    .executeQuery("SELECT * FROM `" + this.prefix + "plot_settings`")) {
+                Statement statement = connection.createStatement();
+                ResultSet resultSet = statement
+                        .executeQuery("SELECT * FROM `" + this.prefix + "plot_settings`")
+        ) {
             while (resultSet.next()) {
                 final int id = resultSet.getInt("plot_plot_id");
                 final String plotFlags = resultSet.getString("flags");
@@ -1751,14 +1720,14 @@ public class SQLManager implements AbstractDB {
                     }
                 }
             }
-            }
         } catch (final Exception e) {
             LOGGER.error("Failed to load old flag values", e);
             return false;
         }
         LOGGER.info("Loaded {} plot flag collections...", flagMap.size());
         LOGGER.info("Attempting to store these flags in the new table...");
-        try (final PreparedStatement preparedStatement = this.connection.prepareStatement(
+        try (Connection connection = this.dataSource().getConnection(); PreparedStatement preparedStatement =
+                connection.prepareStatement(
                 "INSERT INTO `" + SQLManager.this.prefix
                         + "plot_flags`(`plot_id`, `flag`, `value`) VALUES(?, ?, ?)")) {
 
@@ -1843,7 +1812,7 @@ public class SQLManager implements AbstractDB {
             /*
              * Getting plots
              */
-            try (Statement statement = this.connection.createStatement()) {
+            try (Connection connection = this.dataSource().getConnection(); Statement statement = connection.createStatement()) {
                 int id;
                 String o;
                 UUID user;
@@ -2176,8 +2145,8 @@ public class SQLManager implements AbstractDB {
             }
 
             @Override
-            public PreparedStatement get() throws SQLException {
+            public @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException {
-                return SQLManager.this.connection.prepareStatement(
+                return connection.prepareStatement(
                         "UPDATE `" + SQLManager.this.prefix
                                 + "plot_settings` SET `merged` = ? WHERE `plot_plot_id` = ?");
             }
@@ -3162,6 +3131,8 @@ public class SQLManager implements AbstractDB {
 
             @Override
             public PreparedStatement get() throws SQLException {
+                try (final Connection connection = )
+
                 return SQLManager.this.connection.prepareStatement(
                         "DELETE FROM `" + SQLManager.this.prefix
                                 + "cluster_invited` WHERE `cluster_id` = ? AND `user_uuid` = ?");
@@ -3429,13 +3400,16 @@ public class SQLManager implements AbstractDB {
     @Override
     public void close() {
         try {
-            this.closed = true;
+            ((Closeable) this.dataSource()).close();
-            this.connection.close();
+        } catch (Exception e) {
-        } catch (SQLException e) {
+            LOGGER.error("Failed to close data source", e);
-            e.printStackTrace();
         }
     }
 
+    private @NonNull DataSource dataSource() {
+        return this.dataSource;
+    }
+
     private record LegacySettings(
             int id,
             PlotSettings settings
@@ -3459,7 +3433,7 @@ public class SQLManager implements AbstractDB {
             statement.executeBatch();
         }
 
-        public abstract PreparedStatement get() throws SQLException;
+        public abstract @Nullable PreparedStatement get(final @NonNull Connection connection) throws SQLException;
 
         public abstract void set(PreparedStatement statement) throws SQLException;
 

Core/src/main/java/com/plotsquared/core/database/SQLite.java

@@ -33,6 +33,7 @@ import java.sql.Statement;
 /**
  * Connects to and uses a SQLite database.
  */
+@Deprecated(forRemoval = true)
 public class SQLite extends Database {
 
     private static final Logger LOGGER = LogManager.getLogger("PlotSquared/" + SQLite.class.getSimpleName());

Core/src/main/java/com/plotsquared/core/inject/annotations/PlotDatabase.java

@@ -0,0 +1,12 @@
+package com.plotsquared.core.inject.annotations;
+
+import java.lang.annotation.ElementType;
+import java.lang.annotation.Retention;
+import java.lang.annotation.RetentionPolicy;
+import java.lang.annotation.Target;
+
+@Target({ElementType.FIELD, ElementType.TYPE, ElementType.PARAMETER})
+@Retention(RetentionPolicy.RUNTIME)
+public @interface PlotDatabase {
+
+}

Core/src/main/java/com/plotsquared/core/inject/modules/DatabaseModule.java

@@ -0,0 +1,72 @@
+package com.plotsquared.core.inject.modules;
+
+import com.google.inject.AbstractModule;
+import com.plotsquared.core.PlotSquared;
+import com.plotsquared.core.configuration.Storage;
+import com.plotsquared.core.inject.annotations.PlotDatabase;
+import com.plotsquared.core.util.FileUtils;
+import com.plotsquared.core.util.StringMan;
+import com.zaxxer.hikari.HikariConfig;
+import com.zaxxer.hikari.HikariDataSource;
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+
+import javax.sql.DataSource;
+import java.io.File;
+
+public class DatabaseModule extends AbstractModule {
+
+    private static final Logger LOGGER = LogManager.getLogger("PlotSquared/" + PlotSquared.class.getSimpleName());
+
+    @Override
+    protected void configure() {
+        try {
+            if (Storage.MySQL.USE) {
+                this.configureMySQL();
+            } else if (Storage.SQLite.USE) {
+                this.configureSQLite();
+            } else {
+                LOGGER.error("No storage type is set. Disabling PlotSquared");
+                PlotSquared.platform().shutdown();
+            }
+        } catch (final Exception e) {
+            LOGGER.error("Unable to initialize database", e);
+            PlotSquared.platform().shutdown();
+        }
+    }
+
+    private void configureSQLite() throws Exception {
+        final File file = FileUtils.getFile(PlotSquared.platform().getDirectory(), Storage.SQLite.DB + ".db");
+        if (!file.exists()) {
+            file.createNewFile();
+        }
+        Class.forName("org.sqlite.JDBC");
+
+        final HikariConfig config = new HikariConfig();
+        config.setJdbcUrl("jdbc:sqlite:" + file);
+        config.setDriverClassName("org.sqlite.JDBC");
+        final DataSource dataSource = new HikariDataSource();
+
+        binder().bind(DataSource.class).annotatedWith(PlotDatabase.class).toInstance(dataSource);
+    }
+
+    private void configureMySQL() {
+        final HikariConfig hikariConfig = new HikariConfig();
+        hikariConfig.setJdbcUrl(
+                String.format(
+                        "jdbc:mysql://%s:%s/%s?%s",
+                        Storage.MySQL.HOST,
+                        Storage.MySQL.PORT,
+                        Storage.MySQL.DATABASE,
+                        StringMan.join(Storage.MySQL.PROPERTIES, "&")
+                ));
+        hikariConfig.setUsername(Storage.MySQL.USER);
+        hikariConfig.setPassword(Storage.MySQL.PASSWORD);
+        hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
+        hikariConfig.addDataSourceProperty("prepStmtCacheSize", "512");
+        hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
+        final DataSource dataSource = new HikariDataSource(hikariConfig);
+
+        binder().bind(DataSource.class).annotatedWith(PlotDatabase.class).toInstance(dataSource);
+    }
+}

Core/src/main/java/com/plotsquared/core/inject/modules/JdbiModule.java

@@ -0,0 +1,15 @@
+package com.plotsquared.core.inject.modules;
+
+import com.plotsquared.core.inject.annotations.PlotDatabase;
+import org.jdbi.v3.guice.AbstractJdbiDefinitionModule;
+
+public class JdbiModule extends AbstractJdbiDefinitionModule {
+
+    public JdbiModule() {
+        super(PlotDatabase.class);
+    }
+    @Override
+    public void configureJdbi() {
+
+    }
+}

gradle/libs.versions.toml

@@ -19,6 +19,10 @@ luckperms = "5.4"
 essentialsx = "2.20.1"
 mvdwapi = "3.1.1"
 
+# Datebase
+hikaricp = "5.0.1"
+jdbi = "3.41.3"
+
 # Third party
 prtree = "2.0.1"
 aopalliance = "1.0"
@@ -63,6 +67,11 @@ essentialsx = { group = "net.essentialsx", name = "EssentialsX", version.ref = "
 faweCore = { group = "com.fastasyncworldedit", name = "FastAsyncWorldEdit-Core", version.ref = "fawe" }
 faweBukkit = { group = "com.fastasyncworldedit", name = "FastAsyncWorldEdit-Bukkit", version.ref = "fawe" }
 
+# Database
+hikaricp = { group = "com.zaxxer", name = "HikariCP", version.ref = "hikaricp" }
+jdbiCore = { group = "org.jdbi", name = "jdbi3-core", version.ref = "jdbi" }
+jdbiGuice = { group = "org.jdbi", name = "jdbi3-guice", version.ref = "jdbi" }
+
 # Third party
 prtree = { group = "com.intellectualsites.prtree", name = "PRTree", version.ref = "prtree" }
 aopalliance = { group = "aopalliance", name = "aopalliance", version.ref = "aopalliance" }