Resource Guide: DataSource and Try-with-Resources. How to Connect to your Database properly.

Discussion in 'Spigot Plugin Development' started by Eldoria, Dec 29, 2020.

  1. Introduction
    I worked on Java applications for some time now. Since I am writing more and more Plugins with the SpigotAPI, I am more and more involved into the large community of plugin developers.
    While helping ppl with the same problems I had at the beginning I always stumble about bad design pattern and other bad stuff when especially it comes to database connections.
    Many ppl use single connections which they are trying to maintain in the best case. Some ppl just create the connection and just hope that it will never break (Hint: It will break.)

    While starting to work with Java and Databases I also used some of the mentioned bad pattern. But that was just because everyone does it like that and I thought it would be okay. But everything changed when a friend mentioned DataSources. I was curious and discovered a bunch of new possibilities in case of performance and reliability.

    Since not everyone has friends like me, which sometimes say "What are you doing? This is shit!", I want to be this friend for you.

    So let me say something, if you can answer one of these questions with "yes":

    • I never heard of a DataSource or used it
    • I never heard of Try-with-Resources in connection with database connections and queries
    • I have a file in my plugin named MySQL.java
    • I never used a method calles close() in connection with database connections and queries.
    If you anwered one or more with yes, this is for you: What are you doing? This is shit!

    Okay lets be friends. I want to help you. Lets start with changing how you interact with your database.

    DataSource
    A DataSource is an interface of the javax.sql package. Dont worry I will keep this as simple as possible.\

    A Data Source is implemented by a database driver. This driver is different for each database software. This means that you will need different driver depending on what database you want to connect.
    Since the most of you use MariaDB I will stick to this in my examples if I require some database specific stuff.
    However the DataSource is designed that you dont need to bother that much which database software you are using.

    But what does the DataSource do?
    A Data Source provides a connection to your database. If you read this you are probably using a single connection which sometimes probably fails or gets a timeout. The DataSource will handle this for you.

    Beside the DataSource we want to use connection pooling. To do this we will use HikariCP. HikariCP is a Connection Pooling Framework which allows us to get a pool of connection which are managed by Hikari.
    This means that we always have a available not broken database connection and can do parallel read and writes on our database without waiting that the current transaction finishes. Amazing right? No fear of blocking other request because your connection is busy currently. Just use another one from the pool.

    How to create a DataSource with HikariCP
    Okay lets get to the real thing.

    We will now create a database connection to a mariadb using the Hikari ConnectionPool.
    To do this you will need the data you always need. So you probably have a sql setting already. You can continue using this.

    I will start with showing you the whole code and explain everything afterwards.
    There are different ways of creating a connection pool with Hikari. I will show you the way I use.
    Hikari has reasonable default settings. We wont change much here.

    Code (Java):
       Properties props = new Properties();
       props.setProperty("dataSourceClassName", "org.mariadb.jdbc.MariaDbDataSource");
       props.setProperty("dataSource.serverName", settings.getAddress());
       props.setProperty("dataSource.portNumber", settings.getPort());
       props.setProperty("dataSource.user", settings.getUser());
       props.setProperty("dataSource.password", settings.getPassword());
       props.setProperty("dataSource.databaseName", settings.getDatabase());

       HikariConfig config = new HikariConfig(props);

       config.setMaximumPoolSize(settings.getMaxConnections());

       DataSource source = new HikariDataSource(config);
    And thats it. We have a connection pool with a defined amount of connections. This will be 10 by default, which will be enough in the most cases, especially when programming spigot plugins.

    But lets go through it step by step.

    We create a new Properties object. This is a java build in data type. Its basically a key value map. Nothing magic here.
    Now we configure our database. The first line it the only thing for us which is depending on the database software.
    You have to enter the driver class for the database. This driver has to be included in your plugin or needs to be provided in any other way. A list of what driver class needs to be used for which database can be found here.

    The rest should be pretty common for you. We set Address, Port, Username, Password and the database name.
    Important here: You can enter just user and password and dont set the rest. In this case the default values will be used. The values will be defined by the default values of the database driver you are using and will differ with each driver (Especially the port since every database uses a different port).

    After configuring our property object we just create a Hikari Config.
    The Hikari config allows us to configure the connection pool. You can define a lot here. But since the most default values are pretty reasonable, I wouldnt change much here.

    In our case we just define the max active connections to our database.

    And with these settings we finally create our HikariDataSource with a connection pool. We are done \o/

    How to use a data source
    After Creating our data source we dont care about the underlying implementation anymore. Thats why I will always refer to DataSource instead of a HikariDataSource from now on.

    In order to keep your code flow clean you will hopefully use this opportunity to get rid of your MySQL.java file.
    From now on you will pass the data source to the class where it is needed.

    But lets get real and show you how you get a connection. All code snippets are defined in a class which probably looks like this:

    Code (Java):
    public class SomeClass {
       DataSource source;

       public SomeClass(DataSource source) {
           this.source = source;
       }
    }
    A connection can be retrieved like this:

    Code (Java):
       Connection conn = source.getConnection();
    Thats all. But STOP! If you stop reading now you will just make everything worse.
    To work with database connections properly you have to use Try-with-Resources.

    And thats what we are looking at next.

    Why use try with Resources
    A Connection, Statement (PreparedStatement) and ResultSet are AutoCloseable.
    This means that they are closeable, but can be also closed automatically (obviously...).

    When you open a connection this connection will stay open until it is closed.
    A statement need cache till it is closed.
    A Result set is also cached until it is closed.\

    If you miss to close it you will have a memory leak and you will block connections and/or cache.
    You could also run out of free connections.

    To avoid this you want to use a try with resources.
    This ensures that all closeables are closed when you leave the code block.

    Here is some "pseudo code" which shows you the advantage of a auto closeable.

    Currently you probably do something like this without a data source or a try with resources. You get your connection from somewhere. This connection is probably static and some other bad stuff.

    Code (Java):
    try {
       Connection conn = getConnection();
       PreparedStatement stmt = conn.prepareStatement("SELECT some from stuff");
       stmt.setSomething(1, something);
       ResultSet rs = stmt.exeuteQuery();
       // do something with the ResultSet

       // The following part is missed most of the time. Many ppl forget to close their shit.
       conn.close();
       stmt.close(); // Closing the Statement closes the ResultSet of the statement as well.
    } catch (SQLException e){
       e.printStackstrace(); // This should be replaced with a propper logging solution. Dont do this.
    }
    With AutoCloseable you dont have to bother anymore about closing your stuff.
    We will also use a DataSource named source which we cached somewhere inside our class (No we dont get it via a static variable from somewhere. This is bad design...)

    Code (Java):
    try (Connection conn = source.getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT some from stuff")) {
       ResultSet rs = stmt.exeuteQuery();
       stmt.setSomething(1, something);
       ResultSet rs = stmt.exeuteQuery();
       // do something with the ResultSet
    } catch (SQLException e) {
       e.printStackstrace(); // This should be replaced with a propper logging solution. Dont do this.
    }
    You can see, that we dont close our stuff here, because we dont need it. Any object you assign inside the braces of the try braces will be closed when you exit the code block.
    This will return the connection to our connection pool. Free the blocked memory for the result cache and statement and we are happy and ready for the next request.
    Obviously object assigned inside the braces need to be of type AutoCloseable.
    (Hint: Many more classes are auto closeable. Like input and output streams for example. Keep a look at the stuff you are using and use try with resources wherever you can.)

    One more addition here. The result set is also a auto closeable, but we dont create it inside the try braces. It will still be closed. Lets take a look at the ResultSet documentation.
    https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

    And thats it. Thats try with resources. Your connection, statement and result set are freed when you exit the code block and you dont have to care about it anymore.
    Now get out there and fix your broken stuff. (No a MySQL.java is not good...)

    Do you have some examples?
    Of course! You may want to dive into this code. Its not perfect but better than 99% of the stuff I see on a daily base.

    Example implementation of a DataSource for MariaDB and PostgreSQL

    Example Implementation and usage of a data Source with examples for try with resources and basic sql query stuff

    If you want to see how much a DataSource and ConnectionPool can speed up your application you may want to run this small benchmark

    Oracle Documentation and examples for try-with-resources

    Oracle Documentation for DataSources

    Feel Free to submit enhancements to the git repo. I am always open to improve this stuff.
     
    #1 Eldoria, Dec 29, 2020
    Last edited: Dec 31, 2020
    • Like Like x 9
    • Useful Useful x 4
    • Winner Winner x 1
  2. You need to close the ResultSet too
     
  3. No you dont.
    Again something where the documentation would have helped if someone read it :D

    Let me Quote the documentation:
    https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

    But I will mention this directly in my Text. Thanks for pointing this out ^^
     
  4. Thats a bit too much wrapping for me xD But yes. Using this would increase the code quality of a lot plugins. But also I think that the current way is also pretty easy xD And you have more control over the stuff you do :D
    I am also only a fan of wrapping stuff when you know what happens in the background xD But I doubt that people using this looked into the code and tried to understand it xD
     
    • Like Like x 1
  5. Drepic

    Supporter


    Nice resource, I hope people will take the time to understand it.