Page History
...
DBMS Product | DBMS Version | JDBC Driver Version | JDBC Driver File | JDBC Driver License | Included with JS7 | Comment |
---|---|---|---|---|---|---|
H2® | 1.4.200 | 1.4.200 | h2-1.4.200.jar | MPL 2.0, EPL 1.0 | no | DBMS and JDBC Drivers are Open Source |
MariaDB® | 10 | 2.7.2 | mariadb-java-client-2.7.2.jar | LGPL | yes | DBMS and JDBC Drivers are Open Source |
MySQL® | 5.7, 8.0 | 8.x | mysql-connector-java-8.x.jar | Proprietary License | no | DBMS is Open Source, JDBC Drivers are not Open Source The MariaDB® JDBC Driver can be used for access to MySQL® databases |
Oracle® | 12c, 18c, 19c, 21c | 1921.78 | ojdbc8-1819.319.0.0.jar | FUTC License | yes | DBMS and JDBC Drivers are not Open Source Use |
PostgreSQL® | 10, 11, 12, 13, 14, 15 | 42.24.193 | postgresql-42.24.193.jar | BSD 2-clause | yes | DBMS and JDBC Drivers are Open Source |
SQL Server® | 2016, 2017, 2019 | 9.x, 10.x, 11.x | mssql-jdbc-9.x<version>.jre8.jar mssql-jdbc-9.x<version>.jre11.jar mssql-jdbc-9<version>.xjre17.jre15.jar | Proprietary License | no | DBMS and JDBC Drivers are not Open Source |
In most situations it is technically possible to operate JS7 with newer versions of a DBMS or JDBC Driver if the version is actively supported by the DBMS vendor. Users frequently ask if JS7 is compatible with newer versions of a DBMS or JDBC Driver. It's the wrong question and your DBA should know the answer. Consider the following example for Oracle® that similarly applies to other DBMSs:
...
- Hibernate configuration files are used in an XML format.
- JS7 - JOC Cockpit Installation On Premises
- During installation the relevant hibernate configuration file is created and can be manually updated later on.
- After installation of the JOC Cockpit the
hibernate.cfg.xml
Hibernate configuration file is available in- Unix
/var/sos-berlin.com/js7/joc/jetty_base/resources/joc
- Windows
C:\ProgramData\sos-berlin.com\js7\joc\jetty_base\resources\joc
- A different location for the configuration directory can be specified during installation of the JOC Cockpit.
- Unix
- JS7 - JOC Cockpit Installation for Docker Containers
- The JOC Cockpit ships preinstalled from a Docker® imagecontainer image.
- It is therefore recommended that users:
- download/copy one of the Hibernate configuration files listed below to:
/var/sos-berlin.com/js7/joc/jetty_base/resources/joc
- adjust changes to reflect their database connection. For use with H2® you can use the below sample without changes.
- download/copy one of the Hibernate configuration files listed below to:
- By default the Hikari Connection Pool is used.Pool is used.
- Consider that XML requires special characters to be used with escape characters, for example a password in the Hibernate configuration file that includes the character
&
is written as&
. This applies to any values of XML elements and attributes in Hibernate configuration files.- Wrong:
<property name="hibernate.connection.password">sec&ret</property>
- Right:
<property name="hibernate.connection.password">sec&ret</property>
- Wrong:
Hibernate Configuration for Embedded H2®
...
- The example uses the MySQL® database server running with hostname
mysqlsrv
and port3306
running with hostnamemysqlsrv
and port3306
. - The example applies to use of a MySQL® JDBC Driver. Such drivers do not ship with JS7 as they are not available from open source compatible license. Users can download such drivers from the vendor's site.
- For use with MySQL® databases the MariaDB® JDBC Driver can be used that ships with JS7. The class name for this driver is
org.mariadb.jdbc.Driver,
see example for MariaDB®.
Hibernate Configuration for Oracle®
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property> <property name="hibernate.connection.password">jobscheduler</property> <property name="hibernate.connection.url">jdbc:sqlserver://sqlserversrv:1433;sendStringParametersAsUnicode=falsetrue;selectMethod=cursor;databaseName=jobscheduler</property> <property name="hibernate.connection.username">jobscheduler</property> <property name="hibernate.dialect">org.hibernate.dialect.SQLServer2005Dialect</property> <property name="hibernate.show_sql">false</property> <property name="hibernate.connection.autocommit">false</property> <property name="hibernate.format_sql">true</property> <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property> <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property> <property name="hibernate.hikari.maximumPoolSize">10</property> </session-factory> </hibernate-configuration> |
...
- Note that the JDBC Driver for SQL Server® has to be downloaded separately as it does not ship with JS7.
- The example uses the SQL Server® database server running with hostname
sqlserversrv
and port1433
. - Note that use of the above parameters with the JDBC URL is required:
sendStringParametersAsUnicode=falsetrue;selectMethod=cursor
- For Windows environments, access to SQL Server can be configured to use integrated security, i.e. using the account that the JOC Cockpit Windows Service is operated with.
- Specify empty values for the
<property name="hibernate.connection.username">
and<property name="hibernate.connection.password">
elements. - Add to the JDBC URL:
integratedSecurity=true
- The account used for the JOC Cockpit Windows Service has to be configured with SQL Server® to allow access to the database.
- The JDBC Driver distribution usually ships with a library for authentication purposes with a name such as
sql_jdbc.dll
ormssql-jdbc_auth-9.2.1.x64.dll
or similar. This library should be added to a location that is specified with the WindowsPATH
environment variable for the JOC Cockpit Windows Service or simply stored in theC:\Windows\System32
directory or in thebin
directory of the Java JDK/JRE in use.
- Specify empty values for the
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
# grant all permissions
GRANT ALL ON js7.* TO 'jobscheduler'@'%';
# alternatively grant individual permissions
GRANT CREATE, CREATE VIEW, DROP, ALTER, EXECUTE, SELECT, UPDATE, INSERT, DELETE on js7.* to 'jobscheduler'@'%'; |
...
- By default a JOC Cockpit REST Web Service is configured to use up to 10 connections from the pool. As a rule of thumb 1 connection can be shared by approx. 3 simultaneous users.
- Adjust this setting to the max. number of connections required for sharing between simultaneous users of the JOC Cockpit.
- Note that connections will be disconnected and returned to the pool if they are not used for a longer time.
- Additional connections are used by JS7 Services. Consider the total number of connections allowed when setting up your JS7 database.
- Some DBMS products by default limit the number of connections per user account and process.
- JOC Cockpit is running from a single process.
- Oracle by default limits the connections according to the formula: (1.5 * number of processes) + 22
- When operating a JOC Cockpit cluster additional connections for the REST Web Service will be created per JOC Cockpit instance.
JOC Cockpit Component | Min. Connections | Max. Connections | Configurable | Comment | |||
---|---|---|---|---|---|---|---|
JS7 - REST Web Service API | 110 | unbounded (default: 10) | yes | Recommendation: no. number of simultaneous users / 3. | |||
1 | 1 | no | |||||
JS7 - Cleanup Service | 10 | 10 | no5 | unbounded (default: 5) | yes | Configuration via JS7 - Settings | |
JS7 - History Service | 1 | number of connected Controller Instances | no | Any Controllers connected to a JOC Cockpit Instance are counted. | |||
JS7 - Monitor Service | 3 | 3 | no | 2 connections for monitoring JS7 - Monitoring. 1 connection for notifications. JS7 - Notifications. | |||
Total: 20 |
Hibernate allows one out of a number of Connection Pool implementations to be chosen.
- By default the Hikari Connection Pool is used with JS7.
The Hikari setting for the
maximumPoolSize
is included with the Hibernate configuration file as this determines the max. number of open database connections:Code Block language xml title Hikari Connection Pool recommended settings collapse true <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property> <property name="hibernate.hikari.maximumPoolSize">10</property>
- Additional property elements can be used in a Hibernate configuration file to add further settings for Hikari to a Hibernate configuration fileto the Hikari connection pool.
- Alternatively e.g. the C3P0 Connection Pool can be used.
Use of C3P0 suggests a number of additional settings that better match use with JS7 than the default values:
Code Block language xml title C3P0 Connection Pool recommended settings collapse true <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property> <property name="hibernate.c3p0.maxConnectionAge">14400</property> <property name="hibernate.c3p0.maxStatementsPerConnection">0</property> <property name="hibernate.c3p0.maxStatements">0</property> <property name="hibernate.c3p0.checkoutTimeout">0</property> <property name="hibernate.c3p0.maxIdleTime">14400</property> <property name="hibernate.c3p0.acquireRetryAttempts">1</property> <property name="hibernate.c3p0.minPoolSize">5</property> <property name="hibernate.c3p0.acquireIncrement">1</property> <property name="hibernate.c3p0.statementCacheNumDeferredCloseThreads">1</property> <property name="hibernate.c3p0.numHelperThreads">3</property> <property name="hibernate.c3p0.idleConnectionTestPeriod">1800</property> <property name="hibernate.c3p0.maxPoolSize">10</property> <property name="hibernate.c3p0.initialPoolSize">5</property>
- Additional property elements can be used to add further settings. However, close reading of the C3P0 docs is recommended before applying changes.
...
Consider applying changes to your Hibernate configuration file that reflect change of the JDBC Driver. You will find the hibernate.cfg.xm
Hibernate xml
Hibernate configuration file in the JOC Cockpit configuration directory:
...
Restart the JOC Cockpit to apply your changes.
Adding a JDBC Driver to a
...
Container installation
When using the JOC Cockpit Docker® image container image then technically no installer is included that allows to specify the JDBC Driver to be used. Instead, images are preinstalled. In addition, there is no access to the container's file system before running the container which makes it impossible to add or replace a JDBC Driver before JOC Cockpit is started.
...
/var/sos-berlin.com/js7/joc/resources/joc/lib
- The
/var/sos-berlin.com/js7/joc/resources/joc
directory directory is typically mounted to a Docker volume a volume when running the container - for details see the JS7 - JOC Cockpit Installation for Docker Containers article. If thelib
sub-directory does not exist then you can create it to store the JDBC Driver (*.jar) file.
...