dev [Tutorial] Como otimizar sua conexão MySQL! - ConnectionPool

[Gcunha]

Tutorial ConnectionPool MySql

 

Introdução
Criei esse tutorial com o intuito de repassar meu pouco conhecimento em relação a Java para a comunidade, gostaria desde já convidar todos aqueles que possuem tal habilidade na linguagem ou qualquer outra área que passem a ser mais ativos na comunidade, repassando conhecimento e sabedoria para que todos nós sejamos beneficiados.
Nesse tutorial é importante que você tenha alguns requisitos para que vocês consigam tirar maior proveito do tutorial, sendo eles:

• HikariCP (http://search.maven.org/remotecontent?filepath=com/zaxxer/HikariCP/2.7.6/HikariCP-2.7.6.jar);
• Básico do Java (incluindo orientação orientada a objetos).

O que é conexão ?
Para entender o que é ConnectionPool, precisamos primeiro entender o que é conexão. Conexão basicamente é uma ligação entre o seu aplicativo e um banco de dados SQL(nesse caso). Toda vez que é feito uma consulta ou um update ao banco de dados, demora cerca de 1~2 segundos para esse consulta ser realizada. Caso essa consulta seja realizada na Thread principal do seu plugin, ele ficaria lagado em 1~2 segundos. Toda vez que você realizar uma consulta através de uma conexão temos os seguintes passos:

• A conexão é criada;
• A consulta é enviada através da conexão ;
• A resposta do SQL é enviada de volta através da conexão;
• A conexão é encerrada.

Durante esse passo-a-passo o que mais consome recursos da sua aplicação é a abertura de conexão, os outros passos são insignificante em termos de consumo. Levando isso em consideração, como podemos resolver isso?
 
O que é ConnectionPool ?
ConnectionPool, se traduzido ao pé da letra do inglês seria basicamente uma piscina de conexão. Ai você me pergunta, como assim uma piscina Gcunha ? Bom, os falantes da língua inglesa usam o termo piscina para se referenciar à um conjunto. Uma piscina de contatos para eles seriam um conjunto de contatos.
Agora que vocês sabem o que quer dizer a tradução, eu gostaria de voltar um pouco no ultimo tópico, onde eu disse que o problema é a abertura de conexão, como resolver isso ? Simples, use um conjunto de conexão que está sempre aberto, dessa maneira não é necessário abrir e fechar toda hora! Mas como fazer isso ? Facil, basicamente você envia pequenos pacotes de dados de 130kb, que é um tamanho irrelevantes, constantemente para que a conexão nunca seja fechada. Dessa maneira temos uma conexão que está sempre aberta, portanto não é necessário abrir e fechar uma conexão toda vez que vai fazer uma consulta melhorando a velocidade em que uma consulta é feita em até 100 VEZES !
 
Como usar
 
 O primeiro passo é baixar o HikariCP e importar ele para o seu projeto ( mesma coisa que importar o Spigot).
 Feito isso vamos para a programação:
 
*IMPORANTE LER OS COMENTARIOS NOS CÓDIGOS PARA ENTENDER *
 Crie uma classe chamada ConnectionPoolManager:

 

 

public class ConnectionPoolManager {
 
    private final Main plugin;
    
    // Aqui temos a variavel que vai armazenar nossa conexao 
    private HikariDataSource dataSource; 

    //Aqui criamos as variaveis que carregam as propriedades do banco de dados
    private String hostname;
    private String port;
    private String database;
    private String username;
    private String password;
    
    // Aqui temos as variaveis que carregam as propriedades da conexao
    private int minimumConnections;
    private int maximumConnections;
    private long connectionTimeout;
 
    public ConnectionPoolManager(Main plugin) {
        this.plugin = plugin;
        init();
        setupPool();
        makeTable();
    }
    
    // Esse metodo eh responsavel por setar os valores das variaveis
    private void init() {
        hostname = plugin.getConfig().getSQLHostname();
        port = plugin.getConfig().getSQLPort();
        database = plugin.getConfig().getSQLDatabase();
        username = plugin.getConfig().getSQLUsername();
        password = plugin.getConfig().getSQLPassword();
        minimumConnections = plugin.getConfig().getSQLPoolMinimumConnections();
        maximumConnections = plugin.getConfig().getSQLPoolMaximumConnections();
        connectionTimeout = plugin.getConfig().getSQLPoolConnectionTimeoutMillis();
    }
    
    //Esse metodo eh responsavel por criar a conexao fazendo uso das variaveis que foram setadas anteriormente. 
    private void setupPool() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(
                "jdbc:mysql://" +
                        hostname +
                        ":" +
                        port +
                        "/" +
                        database
        );
        config.setDriverClassName("com.mysql.jdbc.Driver");
        config.setUsername(username);
        config.setPassword(password);
        config.setMinimumIdle(minimumConnections);
        config.setMaximumPoolSize(maximumConnections);
        config.setConnectionTimeout(connectionTimeout);
        dataSource = new HikariDataSource(config);
    }
    
    // Esse eh o metodo responsavel por encerrar a consulta (nao encerra a conexao)
    public void close(Connection conn, PreparedStatement ps, ResultSet res) {
        if (conn != null) try { conn.close(); } catch (SQLException ignored) {}
        if (ps != null) try { ps.close(); } catch (SQLException ignored) {}
        if (res != null) try { res.close(); } catch (SQLException ignored) {}
    }
    
    //Esse metodo eh responsavel por encerrar a conexao
    public void closePool() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }
}

   

  

Bom, agora temos tudo pronto e podemos partir para as QUERY'S, para isso crie uma classe chamada SQLManager:

 

  

public class SQLManager {
    // Aqui armazenamos nossas constantes, no caso a Main e a conexao
    private final Main plugin;
    private final ConnectionPoolManager pool;
    
    //Vamos adicionar os valores para as variaveis e iniciar o nosso metodo makeTable() no construtor.
    public SQLManager(Main plugin) {
        this.plugin = plugin;
        pool = new ConnectionPoolManager(plugin);
        makeTable();
    }
    
    // Nesse metodo iremos fazer uma consulta no SQL para criar uma tabela caso nao exista com um unico campo para armazenarmos o UUID 
    private void makeTable() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = pool.getConnection();
            ps = conn.prepareStatement(
                    "CREATE TABLE IF NOT EXISTS `Test` " +
                            "(" +
                            "UUID varchar(30)" +
                            ")"
            );
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();

        //Perceba que eh importante finalizar a query apos realiza-la. 
        } finally {
            pool.close(conn, ps, null);
        }
    }
    
    //Esse metodo eh o responsavel por fechar nossa conexao, iremos utiliza-lo no encerramento do plugin
    public void onDisable() {
        pool.closePool();
    }
 
}

 

   

 Agora temos tudo pronto para utilizar em nosso plugin, apenas iniciar a conexao no onEnable() e fecha-la no onDisable() de nosso plugin:
   

  

public class Messenger extends JavaPlugin {
 
    private SQLManager sql;
    

    //Iniciamos uma conexao ao banco de dados
    @Override
    public void onEnable() {
        initDatabase();
    }
    
    // Encerramos a conexao
    @Override
    public void onDisable() {
        sql.onDisable();
    }
    
    // Metodo que inicializa a conexao/manager do sql
    private void initDatabase() {
        sql = new SQLManager(this);
    }
    
    //Metodo utilizado para resgatar a instancia do MySql
    public SQLManager getSQLManager() {
        return sql;
    }
 
}

 

     

É extremamente importante que você não crie diversas instancias do SQLManager e sempre utilize a que foi inicializada na main. Para fazer isso é simples, apenas instanciar a main e utilizar o método getSQLManager().
Exemplo:
   plugin.getSQLManager().metodo()
 
Importante
 
GitHub do HikariCP(recomendo dar uma lida): https://github.com/brettwooldridge/HikariCP
Se gostou e te ajudou não se esqueça de deixar um Like no tópico !
 
Propriedades 
No próprio GitHub do projeto, existem algumas propriedades que o desenvolvedor recomenda para utilizar em conexões ao MySQL, sendo elas:

 

  

jdbcUrl=jdbc:mysql://localhost:3306/simpsons
user=test
password=test
dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.prepStmtCacheSqlLimit=2048
dataSource.useServerPrepStmts=true
dataSource.useLocalSessionState=true
dataSource.useLocalTransactionState=true
dataSource.rewriteBatchedStatements=true
dataSource.cacheResultSetMetadata=true
dataSource.cacheServerConfiguration=true
dataSource.elideSetAutoCommits=true
dataSource.maintainTimeStats=false

 

  

[VictorHeaven]

Parabéns pelo tutorial, irá ajudar muitos, inclusive eu!

[Gcunha]

 

Parabéns pelo tutorial, irá ajudar muitos, inclusive eu!

 

 Muito obrigado, ainda tem algumas coisinhas para adicionar, mas em breve acrescento!

[Wolf_131]

Tutorial muito bem e muito bem organizado. Parabéns e obrigado por passar o seu conhecimento, irá ajudar muitos!

[Saimon]

WOW, Completinho parabens kk

[Gcunha]

WOW, Completinho parabens kk

 

 

  

 

Tutorial muito bem e muito bem organizado. Parabéns e obrigado por passar o seu conhecimento, irá ajudar muitos!

 

Muito obrigado aos dois !

[TequilAxBr]

Alguns desenvolvedores amigos já tinham me dito desse ConnectionPool, mas nunca tinha pesquisado a fundo.

Parabéns pelo tutorial, realmente muito bom.

[Gcunha]

Alguns desenvolvedores amigos já tinham me dito desse ConnectionPool, mas nunca tinha pesquisado a fundo.

Parabéns pelo tutorial, realmente muito bom.

 

Obrigado lindão, tamo junto !

[DuoSkypeConfia]

Tutorial muito bom e super organizado, parabéns sempre ajudando a comunidade!

[Gui_KillerBR]

Tutorial organizado ... bem completo

Parabéns ira ajuda d+

[Gcunha]

 

Tutorial muito bom e super organizado, parabéns sempre ajudando a comunidade!

 

 

 

Tutorial organizado ... bem completo

Parabéns ira ajuda d+

 

 

 

Muito obrigado pelo elogios, a gente tenta ajudar né hahaha!

 

Muito obrigado pelo elogio, a gente tenta ajudar né hahaha!

 

[Fernando]

Parabéns pelo tópico com certeza vai ajudar muita gente =)

Eu já tinha visto algo parecido no canal de um YouTuber gringo,

acho que é este aqui: https://www.youtube.com/user/SgtCaze

 

Tem um outro tutorial em um tópico no fórum do Spigot com alguns

exemplos de uso: https://www.spigotmc.org/threads/tutorial-implement-mysql-in-your-plugin-with-pooling.61678/

 

Excelente tópico, acho que esse é um dos melhores, merece 1000 likes!

[Gcunha]

Parabéns pelo tópico com certeza vai ajudar muita gente =)

Eu já tinha visto algo parecido no canal de um YouTuber gringo,

acho que é este aqui: 

https://www.youtube.com/user/SgtCaze

 

Tem um outro tutorial em um tópico no fórum do Spigot com alguns

exemplos de uso: 

https://www.spigotmc.org/threads/tutorial-implement-mysql-in-your-plugin-with-pooling.61678/

 

Excelente tópico, acho que esse é um dos melhores, merece 1000 likes!

 

Muito obrigado!

Realmente na gringa tem muito mais conteudo(inclusive aprendi lá), mas aqui no Nr a galera não compartilha o que sabe.

[0tavio]

Esse tópico tá tão arrumadinho que até quero aprender isso aí. kkk

[Fernando]

Muito obrigado!

Realmente na gringa tem muito mais conteudo(inclusive aprendi lá), mas aqui no Nr a galera não compartilha o que sabe.

Falou tudo! Daqui uns dias vou criar alguns tutoriais feras aqui na GB,

agora to meio sem tempo.

[Gcunha]

Esse tópico tá tão arrumadinho que até quero aprender isso aí. kkk

 

hauhauauha, obrigado !

 

 

Falou tudo! Daqui uns dias vou criar alguns tutoriais feras aqui na GB,

agora to meio sem tempo.

 

 

Boa amigão ! Isso ai, vamos tentar somar na comunidade.

[cheat645]

Opa obrigado

[SnowRunescape]

gostei, e vou ate utilizar, estava olhando aqui na net, e parece que não é necessario utilizar o HikariCP, posso estar falando besteira, vou tentar aplicar sem ele, caso não de certo, kk é..

 

vlw

[Gcunha]

Opa obrigado

 

 

 

 

gostei, e vou ate utilizar, estava olhando aqui na net, e parece que não é necessario utilizar o HikariCP, posso estar falando besteira, vou tentar aplicar sem ele, caso não de certo, kk é..

 

vlw

 

 

Cara, não precisa usar o HikariCP, mas se você quiser fazer connectionpool sem ele vai dar uma dorzinha de cabeça. Dificilmente você vai conseguir alcançar o resultado do Hikari 

[AnonyDEV]

Gerou um erro Error occurred while enabling yStack v1.0 (Is it up to date?)

java.lang.NoClassDefFoundError: com/zaxxer/hikari/HikariDataSource

        at yStack.mysql.MySQLSetup.<init>(MySQLSetup.java:14) ~[?:?]

        at yStack.main.Main.onEnable(Main.java:43) ~[?:?]

        at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:321) ~[paper.jar:git-PaperSpigot-"4c7641d"]

        at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:332) [paper.jar:git-PaperSpigot-"4c7641d"]

        at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:407) [paper.jar:git-PaperSpigot-"4c7641d"]

        at org.bukkit.craftbukkit.v1_8_R3.CraftServer.loadPlugin(CraftServer.java:359) [paper.jar:git-PaperSpigot-"4c7641d"]

        at org.bukkit.craftbukkit.v1_8_R3.CraftServer.enablePlugins(CraftServer.java:318) [paper.jar:git-PaperSpigot-"4c7641d"]

        at net.minecraft.server.v1_8_R3.MinecraftServer.s(MinecraftServer.java:408) [paper.jar:git-PaperSpigot-"4c7641d"]

        at net.minecraft.server.v1_8_R3.MinecraftServer.k(MinecraftServer.java:372) [paper.jar:git-PaperSpigot-"4c7641d"]

        at net.minecraft.server.v1_8_R3.MinecraftServer.a(MinecraftServer.java:327) [paper.jar:git-PaperSpigot-"4c7641d"]

        at net.minecraft.server.v1_8_R3.DedicatedServer.init(DedicatedServer.java:267) [paper.jar:git-PaperSpigot-"4c7641d"]

        at net.minecraft.server.v1_8_R3.MinecraftServer.run(MinecraftServer.java:563) [paper.jar:git-PaperSpigot-"4c7641d"]

        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_162]

Caused by: java.lang.ClassNotFoundException: com.zaxxer.hikari.HikariDataSource

        at java.net.URLClassLoader.findClass(URLClassLoader.java:381) ~[?:1.8.0_162]

        at org.bukkit.plugin.java.PluginClassLoader.findClass(PluginClassLoader.java:102) ~[paper.jar:git-PaperSpigot-"4c7641d"]

        at org.bukkit.plugin.java.PluginClassLoader.findClass(PluginClassLoader.java:87) ~[paper.jar:git-PaperSpigot-"4c7641d"]

        at java.lang.ClassLoader.loadClass(ClassLoader.java:424) ~[?:1.8.0_162]

        at java.lang.ClassLoader.loadClass(ClassLoader.java:357) ~[?:1.8.0_162]

        ... 13 more

[TreedGF]

Esse tutorial foi copiado da net, você apenas resumiu os passos entregando as classes finalizadas do post original.

ConnectionPool Spigot

Editado Setembro 3, 2019 por TreedGF