Hikari Connection Pool e por que você DEVE usá-lo
É a melhor biblioteca para fazer connection pools de drivers de banco de dados que usam o JDBC.
ATENÇÃO! Se estiver usando PgPool ou PgBouncer entre seu software e o PostgreSQL, você não precisará do Hikari :P
Pra que serve um Connection Pool?
Se você precisa ter performance e estabilidade ao acessar o banco de dados em um software, você precisa de uma biblioteca de connection pool além da sua biblioteca de driver de conexão sql.
Por quê?
O padrão SQL definiu a muito tempo atrás como a conexão de um software com o banco de dados SQL deve ser, e essa definição trouxe dois problemas de performance. Praticamente todos os drivers de conexão com bancos de dados trazem esse problema.
Os programas de connection pool resolvem estes dois problema causados pelo padrão SQL.
Problema n.1: Conexões com o banco são single thread
Cada conexão com o banco de dados executa coisas nele de forma sequêncial. Cada conexão mantém uma transação.
Se você tem apenas uma conexão em seu programa e ele pede ao banco diferentes consultas ao mesmo tempo, o banco sempre esperará a execução de uma terminar para só então começar executar a outra.
Isso causa muita lentidão na hora de se comunicar com o banco de dados.
Mas se você tiver duas conexões abertas com o banco e pedir duas diferentes consultas ao mesmo tempo, elas serão executadas em paralelo.
Problema n.2: Criar e abrir conexões repetidamente com o banco é demorado
A forma correta de usar o banco de dados com o JDBC é:
- Criar uma conexão
- Criar um statement a partir dessa conexão
- Executar uma query a partir desse statement
- Pegar o result set a partir do statement com query executada
- Buscar os dados retornados pelo banco dentro do result set
- Fechar o result set
- Fechar o statement
- Fechar a conexão
O problema dessa abordagem é que a cada consulta realizada no banco de dados, uma conexão precisa ser aberta. Abrir conexões com o banco é uma operação custosa e demorada.
Uma das soluções é sempre manter uma conexão aberta. Nesse caso você será totalmente responsável por reiniciar a conexão em caso de erros e falhas de rede. Ter apenas uma conexão funciona se você não precisar usar transações em suas queries. Mas e se você precisar?
O PostgreSQL, por definição, não consegue executar mais de uma transação em uma mesma conexão. Se você tentar, os resultados de uma consulta serão sobrescritos pelos resultados da outra (eles não são executados de forma isolada).
Para solucionar esse problema você precisa criar várias conexões, uma para cada execução. Para não ter que ficar abrindo e fechando conexões toda hora use o Hikari para ter um pool de conexões. O Hikari deixa várias conexões com o banco abertas, e elas são dadas à voce quando você cria uma conexão através do Hikari. Toda vez que você pede à ele para criar uma nova conexão, ele na verdade ele está apenas te dando uma conexão já aberta e criada anteriormente do seu pool de conexões.
Após executar e ler uma query, sempre feche o ResultSet, Statement e Connection. Por trás dos panos o Hikari não fechará a Connection, ele apenas retornará ela ao pool de conexões (connection pool).
Exemplo de código em Kotlin com o Hikari
package io.ubivis.ubbi.sandboximport com.zaxxer.hikari.HikariConfig import com.zaxxer.hikari.HikariDataSource import java.sql.Connection import java.sql.ResultSet import java.sql.Statement import java.util.* import kotlin.collections.HashMap import kotlin.concurrent.thread fun main(){ /* TODO check how hikari works with transactions, try begin/commit OK! check if hikari is really using new connections when is called (multithread behaviour) TODO check autoCommit behaviour. TODO find a way to list executed queries on pgadmin https://github.com/brettwooldridge/HikariCP/issues/1056 Bug de transação ativa tomando todas as conexões Bug de reconexão? */ //val conf = val hikariConf = HikariConfig() hikariConf.jdbcUrl = "jdbc:postgresql://localhost:5432/postgres?application_name=hikari_test" hikariConf.username = "postgres" hikariConf.password = "postgres" /* This property controls the default auto-commit behavior of connections returned from the pool. It is a boolean value. Default: true */ hikariConf.isAutoCommit = false /* This property controls the maximum number of milliseconds that a client (that's you) will wait for a connection from the pool. If this time is exceeded without a connection becoming available, a SQLException will be thrown. Lowest acceptable connection timeout is 250 ms. Default: 30000 (30 seconds) */ // hikariConf.connectionTimeout = 30000 /* This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. This setting only applies when minimumIdle is defined to be less than maximumPoolSize. Idle connections will not be retired once the pool reaches minimumIdle connections. Whether a connection is retired as idle or not is subject to a maximum variation of +30 seconds, and average variation of +15 seconds. A connection will never be retired as idle before this timeout. A value of 0 means that idle connections are never removed from the pool. The minimum allowed value is 10000ms (10 seconds). Default: 600000 (10 minutes) */ hikariConf.idleTimeout = 600000 /* This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than maximumPoolSize, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection pool. Default: same as maximumPoolSize */ //hikariConf.minimumIdle = 5 // This is the reconnection timeout. Possibly consider using a shorter maxLifetime value. /* This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool. We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. Default: 1800000 (30 minutes) */ hikariConf.maxLifetime = 300000 /* If your driver supports JDBC4 we strongly recommend not setting this property. This is for "legacy" drivers that do not support the JDBC4 Connection.isValid() API. This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. Again, try running the pool without this property, HikariCP will log an error if your driver is not JDBC4 compliant to let you know. Default: none */ //hikariConf.connectionTestQuery = null //hikariConf.connectionInitSql = null //hikariConf.validationTimeout = 5000 /* This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. Basically this value will determine the maximum number of actual connections to the database backend. A reasonable value for this is best determined by your execution environment. When the pool reaches this size, and no idle connections are available, calls to getConnection() will block for up to connectionTimeout milliseconds before timing out. Please read about pool sizing. Default: 10 */ hikariConf.maximumPoolSize = 5 /* This property represents a user-defined name for the connection pool and appears mainly in logging and JMX management consoles to identify pools and pool configurations. Default: auto-generated */ //hikariConf.poolName = "HikariPool-1" /* This property controls whether the pool can be suspended and resumed through JMX. This is useful for certain failover automation scenarios. When the pool is suspended, calls to getConnection() will not timeout and will be held until the pool is resumed. Default: false */ //hikariConf.isAllowPoolSuspension = false /* This property controls whether Connections obtained from the pool are in read-only mode by default. Note some databases do not support the concept of read-only mode, while others provide query optimizations when the Connection is set to read-only. Whether you need this property or not will depend largely on your application and database. Default: false */ //hikariConf.isReadOnly = false /* This property controls the default transaction isolation level of connections returned from the pool. If this property is not specified, the default transaction isolation level defined by the JDBC driver is used. Only use this property if you have specific isolation requirements that are common for all queries. The value of this property is the constant name from the Connection class such as TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, etc. Default: driver default */ //hikariConf.transactionIsolation = null /* This property controls the amount of time that a connection can be out of the pool before a message is logged indicating a possible connection leak. A value of 0 means leak detection is disabled. Lowest acceptable value for enabling leak detection is 2000 (2 seconds). Default: 0 */ //hikariConf.leakDetectionThreshold = 0 // Data source Properties hikariConf.addDataSourceProperty( "cachePrepStmts" , "false" ) //hikariConf.addDataSourceProperty( "prepStmtCacheSize" , "250" ) //hikariConf.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" ) // Parametros de conexão PostgreSQL // https://www.postgresql.org/docs/11/libpq-connect.html val dataSource = HikariDataSource(hikariConf) println("""Hikari configuration username ${dataSource.username} isAutoCommit ${dataSource.isAutoCommit} connectionTimeout ${dataSource.connectionTimeout} idleTimeout ${dataSource.idleTimeout} maxLifetime ${dataSource.maxLifetime} connectionTestQuery ${dataSource.connectionTestQuery} connectionInitSql ${dataSource.connectionInitSql} validationTimeout ${dataSource.validationTimeout} maximumPoolSize ${dataSource.maximumPoolSize} poolName ${dataSource.poolName} isAllowPoolSuspension ${dataSource.isAllowPoolSuspension} isReadOnly ${dataSource.isReadOnly} transactionIsolation ${dataSource.transactionIsolation} leakDetectionThreshold ${dataSource.leakDetectionThreshold} """.trimIndent()) while(true){ println("Enter string:") val inp = readLine() thread(start = true) { var con: Connection? = null var st: Statement? = null var rs: ResultSet? = null try { println("${Thread.currentThread()} has run.") con = dataSource.connection println("Connecion info:$con") st = con.createStatement() rs = st.executeQuery("SELECT '$inp', pg_sleep(1);") val md = rs.metaData val columns = md.columnCount val list: ArrayList = ArrayList (50) while (rs.next()){ val row: MutableMap = HashMap (columns) for (i in 1..columns) { row[md.getColumnName(i)] = rs.getObject(i) } list.add(row) } println(list) } finally { try{rs?.close()} catch (e: Exception){} try{st?.close()} catch (e: Exception){} try{con?.close()} catch (e: Exception){} } } } }
Também é possível pedir para as conexões, statements e resultsets fecharem automaticamente sem que seja preciso fechar eles em um finally. Para isso, use o try-with-resources do Java ou o try-with-resources do Kotlin.
Referências
HikariCP no Maven Repository
https://mvnrepository.com/artifact/com.zaxxer/HikariCP
Tutorial Baeldung
https://www.baeldung.com/hikaricp
Sobre a obrigação de fecharmos a conexão sempre no final de um statement
https://stackoverflow.com/questions/25367261/best-approach-for-returning-connection-objects-to-hikaricp-pool
Transaction Interleaving
https://jdbc.postgresql.org/documentation/faq.html#transaction-interleaving
Multiplas transações em uma só conexão
https://stackoverflow.com/questions/11620263/postgresql-multiple-transactions-on-the-same-connection