Differences

This shows you the differences between two versions of the page.

--- jvx:common:setup:connectin_pooling [2018/02/02 10:08]
admin created
+++ jvx:common:setup:connectin_pooling [2025/06/20 10:51] (current)
admin
@@ Line 2: / Line 2: @@
 ~~Title: Connection Pooling~~
-DBAccess supports connection pooling by the interface com.sibvisions.rad.persist.jdbc.IConnectionPool.
+Our [[https://www.sibvisions.com/files/jvx/current/api/com/sibvisions/rad/persist/jdbc/DBAccess.html|DBAccess]] supports a simple configurable connection pool with [[https://www.sibvisions.com/files/jvx/current/api/com/sibvisions/rad/persist/jdbc/ConnectionPoolProvider.html|ConnectionPoolProvider]].
-DBAccess gets and releases connections from the pool with
+The ConnectionPoolProvider creates a default DataSource and an [[https://www.sibvisions.com/files/jvx/current/api/com/sibvisions/rad/persist/jdbc/IConnectionPool.html|IConnectionPool]].
-<file java>
+We currently use [[https://github.com/brettwooldridge/HikariCP|Hikari]] and [[https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html|Tomcat]] for our connection pool implementation. With Hikari we have an independent solution and Tomcat will only work on Tomcat application servers.
-public Connection getConnection() throws SQLException;
-public void releaseConnection(Connection pConnection);
-</file>
-DBAccess has following methods for handling connection pooling:
+== Configuration ==
-<file java>
+The connection pool can be enabled by simply calling ''setConnectionPoolEnabled(true)'' on DBAccess, or it can be enabled in [[jvx:common:setup:dbindependent_config|config.xml]].
-public void releaseConnection() throws DataSourceException
-public boolean isConnectionPoolEnabled()
-public void setConnectionPoolEnabled(boolean pConnectionPoolEnabled)
-public EventHandler<IConfigureConnectionListener> eventConfigureConnection()
-public EventHandler<IUnconfigureConnectionListener> eventUnconfigureConnection()
-</file>
-The method releaseConnection is for manual releasing connections. If there is an open transaction, releaseConnection throws a DataSourceException with message “Connection is modified and cannot be released!”
+Example:
-By default, connection pooling is disabled which means that a connection is created on open, and released on close of DBAccess.
-If connection pooling is enabled and autocommit was enabled, a connection is created on demand where needed, and is automatically released when no transaction is open after last call. This check will be queued via invokeFinally (see [[jvx:server:lco:call_events|Server-side Call events]]).
-If connection pooling is enabled and autocommit was disabled, the connection will be released after each request as long as no transaction was started. If a transaction was started, the connection will be released with next commit or rollback call.
-To be able to configure custom connection settings, use eventConfigureConnection and eventUnconfigureConnection. Especially configure connection is important because it's not guaranteed that the same connection will be used for different calls. If you need custom settings, use events to configure the connection. The configure event will be triggered automatically by open and getConnection and unconfigure will be triggered by close and releaseConnection.
-If you don't use connection pooling, you can configure your connection without events, because there will be exactly one database connection per logged-in user. We recommend using event for connection configuration!
-== Configuring Connection Pool ==
-Using an application server it's possible to configure a DataSource resource.
-The JNDI name can be configured in your applications config.xml:
 <file xml>
-<datasource>
+<application>
-  <db name="default">
+   ...
-    <url>jdbc/dbname</url>
+   <connectionpool enabled = "true"/>
-  </db>
+   ...
-</datasource>
+</application>
 </file>
-The DBAccess is created in Session life-cycle object:
+We support different use-cases:
-<file java>
+  - Implementing getDataSource() in application life cycle object (Application.java)
-protected DBAccess getDBAccess() throws Exception
+  - Configure specific IDataSourceCreator in config.xml, e.g.\\ <code>
-{
+  <connectionpool enabled = "true">
-    DBAccess dba = (DBAccess)get("dBAccess");
+    <dataSourceCreatorClass>com.sibvisions.rad.persist.jdbc.datasource.TomcatDataSourceCreator</dataSourceCreatorClass>
+  </connectionpool>
+</code>
+  - Configure ServiceLoader for IDataSourceCreator
+  - Register custom IDataSourceCreator in ConnectionPoolProvider, e.g. ''ConnectionPoolProvider.registerDataSourceCreator(CustomDataSourceCreator.class);'' \\ There are 2 IDataSourceCreator registered by default:\\ * HikariDataSourceCreator\\ * TomcatDataSourceCreator
-    if (dba == null)
+The default implementations of IDataSourceCreator supports several parameter by default.
-    {
+The following example config.xml shows all supported parameters.
-        DBCredentials dbcred = DBSecurityManager.getCredentials(
-                                              SessionContext.getCurrentSessionConfig());
-        dba = DBAccess.getDBAccess(dbcred);
-        // enables connection pooling
-        dba.setConnectionPoolEnabled(true);
-        dba.open();
-        put("dBAccess", dba);
-    }
-    return dba;
-}
-</file>
-It is also possible to create the connection pool with standard JDBC configuration.
 <file xml>
-<datasource>
+<application>
-  <db name="default">
+   ...
-    <url>jdbc:oracle:thin:@localhost:1521:XE</url>
+   <connectionpool enabled = "true">
-    <username>test</username>
+     <!-- ---- General parameters ---- -->
-    <password>test</password>
-  </db>
+     <!-- dataSourceCreatorClass is optional, HikaraDataSourceCreator or TomcatDataSourceCreator will be used by default depending on availability.
-</datasource>
+     <dataSourceCreatorClass>com.sibvisions.rad.persist.jdbc.datasource.TomcatDataSourceCreator</dataSourceCreatorClass> -->
-</file>
+     <!-- maxActive is optional, 80 will be used by default.
+     <maxActive>80</maxActive> -->
-In this case, the DataSource has to be created manually, e.g. in Application life-cycle object:
+     <!-- minIdle is optional, 10 will be used by default.
+     <minIdle>10</minIdle> -->
-<file java>
+     <!-- useAliveQuery is optional, true sets DBAccess.getAliveQuery(), false is default, therefore either validationQuery or connection.isValid() will be used.
-protected DataSource getDataSource()
+     <useAliveQuery>true</useAliveQuery> -->
-{
+     <!-- validationQuery is optional, if it is not set, Hikari and Tomcat pool uses connection.isValid().
-    DataSource dataSource = (DataSource)get("dataSource");
+     <validationQuery>select 1</validationQuery> -->
+     <!-- validationQueryTimeout is optional, in seconds, if it is not set, Hikari and Tomcat default values are used.
-    if (dataSource == null)
+     <validationQueryTimeout>3</validationQueryTimeout> -->
-    {
-        DBCredentials dbcred = DBSecurityManager.getCredentials(
+     <!-- ---- Hikari specific parameters ---- -->
-                                              SessionContext.getCurrentSessionConfig());
+     <!-- validationTimeout is optional, in milliseconds, if it is not set, validationQueryTimeout or Hikari default value is used.
-        dataSource = new OracleDataSource();
+     <validationTimeout>1000</validationTimeout> -->
-        dataSource.setURL(dbcred.getUrl());
+     <!-- keepaliveTime is optional, in milliseconds, if it is not set, Hikari default value is used.
-        dataSource.setUser(dbcred.getUserName());
+     <keepaliveTime>1000</keepaliveTime> -->
-        dataSource.setPassword(dbcred.getPassword());
+     <!-- ---- Tomcat specific parameters ---- -->
-        put("dataSource", dataSource);
-    }
+     <!-- maxIdle is optional, if it is not set, minIdle is used.
+     <maxIdle>10</maxIdle> -->
-    return dataSource;
+     <!-- initialSize is optional, if it is not set, minIdle is used.
-}
+     <initialSize>10</initialSize> -->
-</file>
+     <!-- testWhileIdle is optional, if it is not set, false is used.
+     <testWhileIdle>true</testWhileIdle> -->
-and in the Session life-cycle object:
+     <!-- testOnBorrow is optional, if it is not set, true is used.
+     <testOnBorrow>true</testOnBorrow> -->
-<file java>
+     <!-- testOnReturn is optional, if it is not set, false is used.
-protected DBAccess getDBAccess() throws Exception
+     <testOnReturn>true</testOnReturn> -->
-{
+     <!-- validationInterval is optional, in milliseconds, if it is not set, Tomcat default value is used.
-    DBAccess dba = (DBAccess)get("dBAccess");
+     <validationInterval>3000</validationInterval> -->
+     <!-- timeBetweenEvictionRunsMillis is optional, in milliseconds, if it is not set, Tomcat default value is used.
-    if (dba == null)
+     <timeBetweenEvictionRunsMillis>5000</timeBetweenEvictionRunsMillis> -->
-    {
+     <!-- minEvictableIdleTimeMillis is optional, in milliseconds, if it is not set, Tomcat default value is used.
-        dba = DBAccess.getDBAccess(new DataSourceConnectionPool(getDataSource()));
+     <minEvictableIdleTimeMillis>3000</minEvictableIdleTimeMillis> -->
-        // enables connection pooling
+     <!-- removeAbandoned is optional, if it is not set, Tomcat default value is used.
-        dba.setConnectionPoolEnabled(true);
+     <removeAbandoned>true</removeAbandoned> -->
-        dba.open();
+     <!-- removeAbandonedTimeout is optional, in seconds, if it is not set, Tomcat default value is used.
+     <removeAbandonedTimeout>3000</removeAbandonedTimeout> -->
-        put("dBAccess", dba);
-    }
+   </connectionpool>
+   ...
-    return dba;
+</application>
-}
-</file>
-== Configuring stateful Database connections ==
-It is possible to configure every connection, taken from the connection pool. In Oracle for example it's possible to store user information in packages.
-<file java>
-public DBAccess getDBAccess() throws Exception
-{
-    DBAccess dba = (DBAccess)get("dBAccess");
-    if (dba == null)
-    {
-        DBCredentials dbcred = DBSecurityManager.getCredentials(
-                                              SessionContext.getCurrentSessionConfig());
-        dba = DBAccess.getDBAccess(dbcred);
-        // enables connection pooling
-        dba.setConnectionPoolEnabled(true);
-        dba.eventConfigureConnection().addListener(new IConfigureConnectionListener()
-        {
-            public void configureConnection(ConnectionEvent pEvent) throws SQLException
-            {
-                // sets user for database internal usage, e.g. logging
-                ISession session = SessionContext.getCurrentSession();
-                pEvent.getDBAccess().executeProcedure("REGISTRY.login", session.getUserName());
-            }
-        });
-        dba.open();
-        put("dBAccess", dba);
-    }
-    return dba;
-}
 </file>