B4J Library HikariCP - High Performance Connection Pool

Discussion in 'B4J Libraries & Classes' started by mindful, Jan 17, 2018.

Similar threads

B4J Tutorial [Server] Building web servers with B4J
B4J Code Snippet Redirect the output to a file
B4J Tutorial [Server] Performance measures
B4J Code Snippet Get Image Pixels
B4J Tutorial Non-UI Applications
  1. mindful

    mindful Active Member Licensed User

    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
    Code:
    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.
     

    Attached Files:

    Last edited: Apr 3, 2018
    jmon, DonManfred, OliverA and 8 others like this.
  2. alwaysbusy

    alwaysbusy Well-Known Member Licensed User

    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!
     
  3. XbNnX_507

    XbNnX_507 Active Member Licensed User

    Can it be used in B4A ?
     
    joulongleu and mindful like this.
  4. mindful

    mindful Active Member Licensed User

    joulongleu likes this.
  5. XbNnX_507

    XbNnX_507 Active Member Licensed User

    This is what i got
    Code:
    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
     
  6. mindful

    mindful Active Member Licensed User

    I guess it doesn't work because of the java 1.8 ...
     
    joulongleu likes this.
  7. XbNnX_507

    XbNnX_507 Active Member Licensed User

    Possibly because you compiled with 1.8 ? or do i have to downgrade to 1.7?
     
    joulongleu likes this.
  8. mindful

    mindful Active Member Licensed User

    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 ....
     
    joulongleu likes this.
  9. incendio

    incendio Well-Known Member Licensed User

    Can this pool use in jRDC?
     
    joulongleu likes this.
  10. mindful

    mindful Active Member Licensed User

    Yes. I don't use jRDC - but it as jRDC is a B4J app it will work.
     
    incendio likes this.
  11. MichalK73

    MichalK73 Active Member Licensed User

    Code:
    '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 StringAs Boolean
        
    Return True
    End Sub
    I have an error.
    Any suggestions?
    Java8

     
    joulongleu likes this.
  12. MichalK73

    MichalK73 Active Member Licensed User

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

    jmon Well-Known Member Licensed User

    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
     
  14. mindful

    mindful Active Member Licensed User

    inakigarm and jmon like this.
  15. alwaysbusy

    alwaysbusy Well-Known Member Licensed User

    idd, Just check out the Benchmarks in the link mindful posted
     
    jmon likes this.
  16. alienhunter

    alienhunter Active Member Licensed User

    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]
     
  17. Erel

    Erel Administrator Staff Member Licensed User

    mindful and alienhunter like this.
  18. alienhunter

    alienhunter Active Member Licensed User

  19. mindful

    mindful Active Member Licensed User

    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.
     
    MichalK73 and Erel like this.
  20. alwaysbusy

    alwaysbusy Well-Known 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.

    This seems to work

    Code:
    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);
    }
     
    Last edited: Apr 3, 2018
    mindful likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice