Resource SQLHelper

Discussion in 'Spigot Plugin Development' started by MrIvanPlays, Mar 11, 2020.

  1. GitHub | [​IMG]

    So, you're making your super cool best plugin which needs a database, suddenly you realize - oh god, sql, so much types of sql storage I should implement... Well, here's your solution!

    What is it?
    SQLHelper is a library which is gonna help you to type queries and maintain connections easily. You just have to install it.

    Disclaimer
    The library does not handle database driver dependencies, nor does compile the dependencies it uses (hikari). It is your own responcibility to handle and relocate dependencies. As long as the dependency is loaded into your plugin's class loader, everything should be fine with the library.

    Installation
    SQLHelper installs like any other dependency.

    Maven:

    HTML:
        <repositories>

         <repository>
             <id>ivan</id>
             <url>https://repo.mrivanplays.com/repository/ivan/</url>
         </repository>
        </repositories>

        <dependencies>
            <dependency>
                <groupId>com.mrivanplays</groupId>
                <artifactId>sql-helper</artifactId>
                <version>VERSION</version> <!-- Replace with latest version -->
                <scope>compile</scope>
            </dependency>
        </dependencies>
    Gradle:
    Code (Text):
    repositories {
        maven {
            url "https://repo.mrivanplays.com/repository/ivan/"
        }
    }

    dependencies {
        implementation 'com.mrivanplays:sql-helper:VERSION' // Replace VERSION with latest version
    }
    Don't forget to relocate the dependency!

    As mentioned in the disclaimer, handling and relocating the database driver dependencies and the dependency, used in this library (hikari) is your own responcibility.

    Usage
    Examples of usage can be found here
    Here is the code from the link:
    Code (Text):
    package com.mrivanplays.sqlhelper;

    import java.util.concurrent.Executors;
    import org.junit.After;
    import org.junit.Assert;
    import org.junit.Before;
    import org.junit.Test;

    public class SQLHelperTest
    {

        private SQLHelper sqlHelper;

        @Before
        public void initialize()
        {
            ConnectionConfig connectionConfig = new ConnectionConfig();
            connectionConfig.setIp( "localhost" );
            connectionConfig.setPort( 3306 );
            connectionConfig.setUsername( "root" );
            connectionConfig.setPassword( "" );
            connectionConfig.setConnectionType( ConnectionType.MYSQL );
            connectionConfig.setDatabaseName( "testserver" );

            sqlHelper = new SQLHelper( connectionConfig, Executors.newSingleThreadExecutor() );
            sqlHelper.connect();
        }

        @After
        public void closeConnection()
        {
            sqlHelper.close();
        }

        @Test
        public void connectAndPerformTasks()
        {
            sqlHelper.create().tableName( "test_table" ).columns( "id INTEGER", "name VARCHAR(255)" ).executeUpdate();
            sqlHelper.insert().into( "test_table" ).columns( "id", "name" ).executeUpdate( 1, "MrIvanPlays" );
            sqlHelper.insert().into( "test_table" ).columns( "id", "name" ).executeUpdate( 2, "Gbtank" );
            sqlHelper.select().everything().from( "test_table" ).executeQuery().async( (resultSet) ->
            {
                Assert.assertTrue( resultSet.getResults().size() > 0 );
            }, error ->
            {
                error.printStackTrace();
                Assert.fail();
            } );

            sqlHelper.create().tableName( "another_test" ).columns( "id INTEGER", "performAction BOOLEAN" ).executeUpdate();
            sqlHelper.insert().into( "another_test" ).columns( "id", "performAction" ).executeUpdate( 1, true );
            sqlHelper.select().everything().from( "another_test" ).executeQuery().async( (resultSet) ->
            {
                Assert.assertTrue( resultSet.getResults().size() > 0 );
            }, error ->
            {
                error.printStackTrace();
                Assert.fail();
            } );
            sqlHelper.update().tableName( "another_test" )
                .values( new String[] { "performAction" }, new Object[] { false } )
                .where( new String[] { "id" }, new Object[] { 1 } )
                .executeUpdate();
            sqlHelper.select().everything().from( "another_test" )
                .where( new String[] { "id" }, new Object[] { 1 } )
                .executeQuery()
                .async( (resultSet) ->
                {
                    Assert.assertTrue( resultSet.getResults().size() > 0 );
                }, error ->
                {
                    error.printStackTrace();
                    Assert.fail();
                } );

            sqlHelper.create().tableName( "only_one_column" ).columns( "id INTEGER" ).executeUpdate();
            sqlHelper.insert().into( "only_one_column" ).columns( "id" ).executeUpdate( 1 );
        }
    }

    Javadocs
    Javadocs can be found here.

    Minecraft versions
    The library is version independent; meaning, you can run it on whatever version you'd like.
     
    #1 MrIvanPlays, Mar 11, 2020
    Last edited: Mar 18, 2020
    • Like Like x 1
  2. SteelPhoenix

    Moderator

    SQL is pretty easy to understand as it is almost plain english, so I don't see any reason for anyone to use this as it really only functions as a wrapper for hikari and statment creation/execution.
    Then again, queries can get more complex but this library only supports basic statements anyways.
    Also, this way of statement building is very prone to sql injection.

    Side note, setting up your helper and connection (credentials) should not be a part of a unit test. The @BeforeAll annotation can be used for this (and its its derivatives like @BeforeEach), and you don't really test anything anyways - you just fail on exception
     
    #2 SteelPhoenix, Mar 11, 2020
    Last edited: Mar 11, 2020
    • Agree Agree x 3
  3. I do test and the test doesn't fail on my pc. Thanks for the suggestions tho.

    Wdym

    Show an example of "more complex" sql statement? Yes I do realize there are "create table" statements which feature retrieving data from existing table, yes I do realize there are the sql statements "trunk" and "drop" which aren't featured in the library. Also I don't think you need something different than the provided queries.
     
    #3 MrIvanPlays, Mar 11, 2020
    Last edited by a moderator: Mar 11, 2020
  4. drives_a_ford

    Moderator

    A test is generally something where you compare the expected result of some behaviour with the actual result of some behaviour. You're not doing that here. At the very least you should use Assert#assertNotNull instead of manually null-checking.

    Not exactly complex, but you don't support stuff like ON DUPLICATE KEY UPDATE.
     
  5. SteelPhoenix

    Moderator

    You aren't really testing anything though.

    https://en.wikipedia.org/wiki/SQL_injection

    You barely support anything tbh...
    Here's a query from one of the database assignments I made in my university course.
    Code (Text):
    WITH T AS (SELECT planet, (departure_time - arrival_time) AS time FROM timetable)
    SELECT T.planet, MAX(T.time)
    FROM T INNER JOIN (SELECT planet, MAX(time) AS maxx FROM T GROUP BY planet) AS IT
    ON T.planet=IT.planet AND T.time<maxx
     
     
  6. K I understood.

    What malicious things can happen with a database in spigot plugin? Well yes, there are some dum developers which make a login plugin, and store the password as a plain text, but this isn't library's fault.

    The project is open source. Whatever you'd like to be added, you can make a PR with it and i'll review and if it's good, i'll merge.
     
  7. There. SQL injection vulnerability fixed.
     
  8. It seems to be really useful for basic saving and retrieving operations. Why not?
     
    #8 patri9ck, Mar 11, 2020
    Last edited: Mar 11, 2020
  9. He means your library probably does not perform validation like PrepareStatement does, and so it allows like .where("1=1 UNION (ignore this parenthesis) select xxx")
     
  10. As I said, this was fixed. You can check out the latest code.
     
  11. Maybe it's just me, but doesnt this only allow you building queries? And with a structure that looks very similar to SQL? Than why shouldn't I just use the JDBC?
    You should add functionality beyond that to make this useful to the most people. Maybe add caching or smth like that?
     
  12. Not only building queries. Also maintaining connections. And the caching should already be done by hikari
     
  13. Maintaning connections is also done by Hikari...
     
  14. The newest updates feature a new StatementCompletionStage and ditch completable futures. Also SQLHelper is now fully documented and the documentation can be viewed here.