Page History
...
- JOC Cockpit makes use of a database to store inventory information, status information and workflow related logs. The database is required for restart restarting capabilities with the JS7 - Cluster Service.
- JS7 does not include a DBMS, instead, it make use of a DBMS that has been installed separately - with the exception of H2® for which JS7 includes an embedded version of the DBMS for evaluation purposes.
- The supported DBMS products include
- H2®
- MariaDB®
- MySQL®
- Oracle®
- PostgreSQL®
- SQL Server®
- Basically any versions of a DBMS can be used that are actively supported by their vendor.
- Access to the DBMS is provided by the Hibernate database access layer. This includes to use a JDBC Driver that is specific for the DBMS.
- Consider to check and to apply measures for JS7 - Database Maintenance
...
Info | ||
---|---|---|
| ||
Database support currently is limited to MySQL® and H2®. The upcoming prerelease of JS7 can be used with any supported database, see JS7 - Roadmap. |
JDBC Drivers
For The JDBC Drivers for a number of DBMS the JDBC Drivers DBMSs are included with JS7. However, due to restrictions for bundling of JDBC Drivers with Open Source software for some DBMS DBMSs, users have to individually download the JDBC Driver from the vendor's web site themselves:
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 | yes | 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 MySQL® |
Oracle® | 12c, 18c, 19c | 19.7 | ojdbc8.jar | FUTC License | yes | DBMS and JDBC Drivers are not Open Source Use ojdbc10.jar for Java 10 releases and newer |
PostgreSQL® | 10, 11, 12 | 42.2.19 | postgresql-42.2.19.jar | BSD 2-clause | yes | DBMS and JDBC Drivers are Open Source |
SQL Server® | 2012, 2014, 2017, 2019 | 9.x | mssql-jdbc-9.x.jre8.jar mssql-jdbc-9.x.jre11.jar mssql-jdbc-9.x.jre15.jar | Proprietary License | no | DBMS and JDBC Drivers are not Open Source |
...
- Hibernate configuration files are used from an XML format. During installation the respective hibernate configuration file is created and can later on be manually updated later on.
- After installation of JOC Cockpit the Hibernate configuration file is available with in
./jetty_base/resources/joc/hibernate.cfg.xml
- By default the Hikari Connection Pool is used.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">org.h2.Driver</property> <property name="hibernate.connection.password">jobscheduler</property> <property name="hibernate.connection.url">jdbc:h2:/var/h2/jobscheduler</property> <property name="hibernate.connection.username">jobscheduler</property> <property name="hibernate.dialect">org.hibernate.dialect.H2Dialect</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> |
ExplanationsExplanation:
- The JDBC connection URL in this sample makes use of an embedded H2® database server. The database file is located in
/var/h2/jobscheduler
. - For use with a standalone H2® database server use a syntax such as
jdbc:h2:tcp://<host>:<port>/jobscheduler
where<host>
is the hostname and<port>
is the H2® database port (frequently:1521
).
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">org.mariadb.jdbc.Driver</property> <property name="hibernate.connection.password">jobscheduler</property> <property name="hibernate.connection.url">jdbc:mariadb://mariadbsrv:3306/jobscheduler</property> <property name="hibernate.connection.username">jobscheduler</property> <property name="hibernate.dialect">org.hibernate.dialect.MariaDBDialect</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> |
ExplanationsExplanation:
- The example makes use of the MariaDB® database server running for hostname
mariadbsrv
and port3306
.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">org.mysql.jdbc.Driver</property> <property name="hibernate.connection.password">jobscheduler</property> <property name="hibernate.connection.url">jdbc:mysql://mysqlsrv:3306/jobscheduler</property> <property name="hibernate.connection.username">jobscheduler</property> <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</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> |
ExplanationsExplanation:
- The example makes use of the MySQL® database server running for hostname
mysqlsrv
and port3306
.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property> <property name="hibernate.connection.password">jobscheduler</property> <property name="hibernate.connection.url">jdbc:oracle:thin:@//oraclesrv:1521/xe</property> <property name="hibernate.connection.username">jobscheduler</property> <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</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> |
ExplanationsExplanation:
- The example makes use of the Oracle® database listener running for hostname
oraclesrv
and port1521
. The database Service Name isxe
. - Should you want to use a Service ID instead of a Service Name, then use this URL syntax:
jdbc:oracle:thin:@oraclesrv:1521:xe
- Should you want to directly specify additional settings as typically used from
tnsnames.ora
, then use this URL syntax:jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL=TCP)(HOST=oraclesrv)(PORT=1521)))(CONNECT_DATA=(SID=XE)(GLOBAL_NAME=XE.WORLD)(SERVER=DEDICATED)))
- Should you want to use Oracle Wallet®, then specify empty values for the
<property name="hibernate.connection.username">
and<property name="hibernate.connection.password">
elements. Find further information with the How to connect to an Oracle database without using passwords article.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">org.postgresql.Driver</property> <property name="hibernate.connection.password">jobscheduler</property> <property name="hibernate.connection.url">jdbc:postgresql://postgresqlsrv:5432/jobscheduler</property> <property name="hibernate.connection.username">jobscheduler</property> <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</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> |
ExplanationsExplanation:
- The example makes use of the PostgreSQL® database server running for hostname
postgresqlsrv
and port5432
.
...
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=false;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> |
ExplanationsExplanation:
- Consider Note that the JDBC Driver for SQL Server® has to be downloaded individually separately as it does not ship with JS7.
- The example makes use of the SQL Server® database server running for hostname
sqlserversrv
and port1433
. - Consider Note that use of the above parameters with the JDBC URL is required:
sendStringParametersAsUnicode=false;selectMethod=cursor
- For Windows environments, access to SQL Server can be configured to use integrated security, i.e. to use the account that the JOC Cockpit Windows Service is operated for.
- 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 to theC:\Windows\System32
directory or to thebin
directory of the Java JDK/JRE in use.
- Specify empty values for the
...
- By default JOC Cockpit is configured to use up to 10 connections from the pool. As a rule of thumb 1 connection can be shared by approx. 3 parallel users.
- Adjust this setting to the max. number of connections required for sharing between parallel users of JOC Cockpit.
Hibernate offers to choose 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 to add further settings for Hikari can be added by property elements to a Hibernate configuration file.
- 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 settings can be added by corresponding property elements, however, close reading of the C3P0 docs is recommended before applying changes.
...