B4J Library HikariCP - High Performance Connection Pool

This is a wrapper for:

https://github.com/brettwooldridge/HikariCP

It can be used the same way as the ConnectionPool from the jServer library.

Below you will find my implementation of using it with MariaDB/MySQL
B4X:
Dim cp As HikariCP
cp.Initialize("org.mariadb.jdbc.Driver", "jdbc:mariadb://YOUR_MARIADB_IP:YOUR_MARIADB_PORT/YOUR_MARIADB_DATABASE?characterEncoding=utf8", YOUR_MARIADB_USERNAME, YOUR_MARIADB_PASSWORD)
Dim jocp As JavaObject = cp
jocp.RunMethod("setMaximumPoolSize", Array(4))
jocp.RunMethod("addDataSourceProperty", Array("cachePrepStmts", True))
jocp.RunMethod("addDataSourceProperty", Array("prepStmtCacheSize", 250))
jocp.RunMethod("addDataSourceProperty", Array("prepStmtCacheSqlLimit", 2048))
jocp.RunMethod("addDataSourceProperty", Array("useServerPrepStmts", True))
jocp.RunMethod("addDataSourceProperty", Array("useLocalSessionState", True))
jocp.RunMethod("addDataSourceProperty", Array("useLocalTransactionState", True))
jocp.RunMethod("addDataSourceProperty", Array("rewriteBatchedStatements", True))
jocp.RunMethod("addDataSourceProperty", Array("cacheResultSetMetadata", True))
jocp.RunMethod("addDataSourceProperty", Array("cacheServerConfiguration", True))
jocp.RunMethod("addDataSourceProperty", Array("elideSetAutoCommits", True))
jocp.RunMethod("addDataSourceProperty", Array("maintainTimeStats", True))
Dim conn As SQL = cp.GetConnection
Log(conn.ExecQuerySingleResult("SELECT version()"))

This library depends on the HikariCP jar which you can get from:
https://mvnrepository.com/artifact/com.zaxxer/HikariCP

Download the latest version (2.7.6 at the time of this writing), put it in your additional libraries folder along with the HikariCP.jar and .xml from the attached archive.
Add #AdditionalJar: HikariCP-2.7.6 to your Project Attributes and check the HikariCP library from the Libraries Window.

Also you need #AdditionalJar: slf4j-api-1.7.25: https://mvnrepository.com/artifact/org.slf4j/slf4j-api

That's all ;)

The HikariCP class/object is wrapped arround HikariDataSource.

You can find more details about performance/config on the HikariCP github page.
 

Attachments

  • HikariCP-1.10.zip
    3.1 KB · Views: 562
Last edited:

mindful

Active Member
Licensed User
Would it be possible to add an additional Initialize method? For Microsoft SQL, one has to set a connectionTestString (to avoid the isValid error). But it can't be set after initialize.
Done - First post is updated with v.1.10

But I think that it could have been set with JavaObject:
B4X:
Dim jocp As JavaObject = cp
jocp.RunMethod("setConnectionTestQuery", Array("SELECT 1"))
 

alwaysbusy

Expert
Licensed User
Longtime User
Looks like there is a problem with the lib when using JDK 9 (+ HTTP2?):

My log:
B4X:
Waiting for debugger to connect...
Program started.
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Error occurred on line: 68 (DBM)
java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
   at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:654)
   at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:371)
   at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
   at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:117)
   at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:375)
   at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:204)
   at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:459)
   at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:533)
   at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:114)
   at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:97)
   at ro.mindful.wrapper.HikariCP.HikariDataSourceWrapper.GetConnection(HikariDataSourceWrapper.java:60)
   at com.onetwo.dbm._getsql(dbm.java:114)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:564)
   at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:625)
   at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
   at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:168)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:564)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
   at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:94)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
   at com.onetwo.main.main(main.java:29)
Caused by: java.io.IOException: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
   at net.sourceforge.jtds.jdbc.TdsCore.sendMSLoginPkt(TdsCore.java:1963)
   at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:617)
   ... 26 more

As in JDK 9, one cannot use the '#VirtualMachineArgs: -Xbootclasspath/p:alpn-boot-8.1.7.v20160121.jar param anymore, it looks like HikariCP still uses it.

Maybe recompiling it with JDK 9 would solve this?
 

mindful

Active Member
Licensed User
@alwaysbusy I will have a look to see if there is something special that needs to be done for java 9.

But while reading the error it looks like it raises from the jdbc driver you are using ( net.sourceforge.jtds ) which I think doesn't support java 9 o_O - I only tested this with mysql driver and mariadb. Anyway I will have a look ;)
B4X:
   at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:654)
   at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:371)
   at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
 

alwaysbusy

Expert
Licensed User
Longtime User
Turns out I forgot to put the ntlmauth.dll in the new jdk 9 bin folder. This dll is needed for secure MSSQL connections. As I don't upgrade my java with 'every' new release, I completely forgot about that one. So works fine now with your lib, JDK 9 and HTTP2
 
Top