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

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.

 

[mindful]

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:

Dim jocp As JavaObject = cp
jocp.RunMethod("setConnectionTestQuery", Array("SELECT 1"))

 

[alwaysbusy]

No that did not work, after the Initialize

Thanks!

 

[alwaysbusy]

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

My log:

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?

 

[jmon]

It works for me with jdk 10.

 

[mindful]

@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 - I only tested this with mysql driver and mariadb. Anyway I will have a look

   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)

 

[mindful]

Also you can try updating HikariCP - latest version is 3.1.0 now (https://mvnrepository.com/artifact/com.zaxxer/HikariCP/3.1.0) and also try switching your jdbc driver to a more actual one in your case I think you are connecting to mssql so you can use com.microsoft.sqlserver.jdbc.

 

[alwaysbusy]

The weird thing is I’m now getting the same error with JDK 8 so I think installing JDK 9 has somehow screwed up my machine

 

[alwaysbusy]

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