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.

 

[alwaysbusy]

I was lucky to be able to test this library in preview, and I can tell you this is one hell of a connection pool!

 

[XbNnX_507]

Can it be used in B4A ?

 

[mindful]

Can it be used in B4A ?

Good question I really can't answer because I do not code in B4A (yet), only B4J ... but look at this:
https://github.com/brettwooldridge/HikariCP/issues/439#issuecomment-144748599
and I quote: "... there are actually users running HikariCP on Android ..."

so you can give it a try!

 

[XbNnX_507]

This is what i got

PARSE ERROR:
unsupported class file version 52.0
...while parsing ro/mindful/wrapper/HikariCP/HikariDataSourceWrapper.class
PARSE ERROR:
unsupported class file version 52.0
...while parsing com/zaxxer/hikari/HikariConfig.class
2 errors; aborting

 

[mindful]

I guess it doesn't work because of the java 1.8 ...

 

[XbNnX_507]

Possibly because you compiled with 1.8 ? or do i have to downgrade to 1.7?

 

[mindful]

I compiled with java 1.8 ... and HikariCP for java 1.7 can be found here: https://mvnrepository.com/artifact/com.zaxxer/HikariCP-java7 version 2.4.13 is the latest.
I don't have java 1.7 installed because I don't need it ... anyway I think that wrapping a library for android I need another jar in my project so it's much of a headache for me ... if you like I can send you the source code or I can post it here or you can take it from the github: https://github.com/AnywhereSoftware...ywheresoftware/b4j/object/ConnectionPool.java as it's basically the same thing, the difference being that it wraps around HikariDataSource instead of ComboPooledDataSource ....

 

[incendio]

Can this pool use in jRDC?

 

[mindful]

Yes. I don't use jRDC - but it as jRDC is a B4J app it will work.

 

[MichalK73]

'Non-UI application (console / server application)
#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
    #AdditionalJar: mariadb-java-client-2.2.1.jar
#End Region

Sub Process_Globals
    Private sql As SQL
    Private cp As HikariCP
    Private host As String ="myhost"
    Private user As String ="user"
    Private pass As String = "password"
End Sub

Sub AppStart (Args() As String)
    cp.Initialize("org.mariadb.jdbc.Driver", host, user, pass)
    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()"))
End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

I have an error.
Any suggestions?
Java8

B4J Version: 6.00
Parsing code.    (0.00s)
Compiling code.    (0.02s)
Compiling layouts code.    (0.00s)
Organizing libraries.    (0.00s)
Compiling generated Java code.    Error
B4J line: 18
Dim jocp As JavaObject = cp
javac 1.8.0_151
src\b4j\example\main.java:83: error: cannot access HikariDataSource
_jocp.setObject((java.lang.Object)(_cp.getObject()));
                                                ^
  class file for com.zaxxer.hikari.HikariDataSource not found
1 error

 

[MichalK73]

Sorry. Ok.
I forget
#AdditionalJar: HikariCP-2.7.6.jar

 

[jmon]

I was lucky to be able to test this library in preview, and I can tell you this is one hell of a connection pool!

Could you explain a bit more? Would it be worth for me to change from the jServer connection pool to this one? What would be the benefits?
Thanks

 

[mindful]

According to https://github.com/brettwooldridge/HikariCP it is a hole lot performant ...you could test it and see if there are any improvments...

 

[alwaysbusy]

What would be the benefits

idd, Just check out the Benchmarks in the link mindful posted

 

[alienhunter]

Hi
i get an error using Java 8 UI /mysql
any clue what this is ?
thanks

java.lang.NoClassDefFoundError: org/slf4j/LoggerFactory
at com.zaxxer.hikari.HikariConfig.<clinit>(HikariConfig.java:51)
at ro.mindful.wrapper.HikariCP.HikariDataSourceWrapper.Initialize(HikariDataSourceWrapper.java:28)
at b4j.example.main._appstart(main.java:103)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:613)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:231)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
at b4j.example.main.start(main.java:38)
at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.ClassNotFoundException: org.slf4j.LoggerFactory
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 27 more
java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/slf4j/LoggerFactory
[/CODE]

 

[Erel]

I'm not familiar with this library implementation so maybe there is a different solution.

Download: http://central.maven.org/maven2/org/slf4j/slf4j-log4j12/1.7.25/slf4j-log4j12-1.7.25.jar
Add it with #AdditionalJar: slf4j-log4j12-1.7.25

 

[alienhunter]

I'm not familiar with this library implementation so maybe there is a different solution.

Download: http://central.maven.org/maven2/org/slf4j/slf4j-log4j12/1.7.25/slf4j-log4j12-1.7.25.jar
Add it with #AdditionalJar: slf4j-log4j12-1.7.25

thanks it worked

 

[mindful]

Or you can just download slf4j-api-1.7.25: https://mvnrepository.com/artifact/org.slf4j/slf4j-api

The HikariCP uses the SLF4J for logging. SLF4J is just a layer is not the logging implementation (for that u need to use log4j or logback) etc.

I updated the first post also.

 

[alwaysbusy]

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.

This seems to work

public void Initialize2(String DriverClass, String JdbcUrl, String userName, String password, String connectionTestQuery) throws PropertyVetoException  {
       HikariDataSource localHikariDataSource = new HikariDataSource();
       localHikariDataSource.setConnectionTestQuery(connectionTestQuery);
       localHikariDataSource.setDriverClassName(DriverClass);
       localHikariDataSource.setJdbcUrl(JdbcUrl);
       localHikariDataSource.addDataSourceProperty("user", userName);
       localHikariDataSource.addDataSourceProperty("password", password);
       setObject(localHikariDataSource);
}