1.16.5 [MySQL] [Hikari] First attempt at a Hikari/MySQL Async plugin

Discussion in 'Spigot Plugin Development' started by boolean, Feb 22, 2021.

  1. Hey there! You may have recently seen me post about caching within MySQL, Hikari was reccomended to me as a connection pool API to help with database accessing, especially on a large-demand.

    Spent the last day-or-so fiddling around with Hikari, CompletableFuture's and MySQL in general to see if I could get to a stage I was relatively happy with, eventually came out with this project, which is entirely for testing and seeing if things were working as expected. As it's my first (or second, I think? I've wrote this plugin a couple of times out, losing it once before posting so had to completely rewrite the plugin from decompiled JAR sourcecode which was fun) attempt at something like this, any criticism, feedback or suggestions for improvements would be very welcome! Just trying to figure out if what I'm doing is right, before I start down the path of developing bad coding habits :)

    Thanks,
    Ryan
     
    • Like Like x 1
  2. Only one real thing that you're doing that weirds me - this close method. Why are you doing that at all? All of those objects are autocloseable, which means you can use them in a try-with-resources.
    For example, your Database#createTable method:
    Code (Java):
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
      connection = Database.this.connectionPool.getHikari().getConnection();
      preparedStatement = connection.prepareStatement("CREATE TABLE IF NOT EXISTS data (UUID VARCHAR(36),POINTS INT(10) DEFAULT 0,PRIMARY KEY(UUID))");
      preparedStatement.executeUpdate();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      connectionPool.close(connection, preparedStatement, null);
    }
    This is what I would do:
    Code (Java):
    try (
        Connection connection = Database.this.connectionPool.getHikari().getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("CREATE TABLE IF NOT EXISTS data (UUID VARCHAR(36),POINTS INT(10) DEFAULT 0,PRIMARY KEY(UUID))")
        ) {
      preparedStatement.executeUpdate();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    I'd also make all your strings for prepared statements constants, but that's a personal stylistic choice - I find it easier to implement support for other database types that way.
     
  3. Code (Java):
    public int getPoints(Player player) throws ExecutionException, InterruptedException {
        CompletableFuture<Integer> result = CompletableFuture.supplyAsync(() -> {
           Connection connection = null;
           PreparedStatement preparedStatement = null;
           ResultSet resultSet = null;

           try {
               connection = connectionPool.getHikari().getConnection();
               preparedStatement = connection.prepareStatement("SELECT POINTS FROM data WHERE UUID=?");
               preparedStatement.setString(1,player.getUniqueId().toString());
               resultSet = preparedStatement.executeQuery();

               if(resultSet.next()) return resultSet.getInt("POINTS");
           }

           catch(SQLException e) {
               e.printStackTrace();
           }

           finally {
               connectionPool.close(connection, preparedStatement, resultSet);
           }

           return null;
        });

        return result.get();
    }
    result.get() will block until the future is completed which defeats the purpose of using the future. The method should be returning the future itself then the code that calls getPoints would use one of the returned future's methods such as thenAccept to do something when the future completes.
     
  4. I'd seen it done using try-with-resources but hadn't realised they were autoclosing! Thanks for the tip. Everything I'd read at the time (granted, this was early morning at the time) had said to ensure you were closing connections, preparedstatements and resultsets - didn't realise they automatically did it themselves with twr.


    Ahh, right! Thanks for the heads up haha, I'd sorta just pieced that together from what I could find online and testing out what worked and what didn't. I'll get that changed!
     
  5. If you only use MySQL you could use an SP that checks if there is a row and if it does not exist that creates it with default values, I am using this and it is much easier for me to apply the code in java.