Introduction

• JOC Cockpit makes use of a database to store inventory information, status information and workflow related logs. The database is required for 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 its 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

JDBC Drivers

The JDBC Drivers for a number of DBMSs are included with JS7. However, due to restrictions for bundling of JDBC Drivers with Open Source software for some DBMS products, users have to download the JDBC Driver from the vendor's web site themselves:

Hibernate Configuration Files

• Hibernate configuration files are used from an XML format. During installation the respective hibernate configuration file is created and can be manually updated later on.
• After installation of 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 JOC Cockpit.
• By default the Hikari Connection Pool is used.

Hibernate Configuration for H2®

<?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>

Explanation:

• 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).

Hibernate Configuration for MariaDB®

<?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>

Explanation:

• The example makes use of the MariaDB® database server running for hostname mariadbsrv and port 3306.

Hibernate Configuration for MySQL®

<?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>

Explanation:

• The example makes use of the MySQL® database server running for hostname mysqlsrv and port 3306.

Hibernate Configuration for Oracle®

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">oracle.jdbc.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.Oracle12cDialect</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>

Explanation:

• The example makes use of the Oracle® database listener running for hostname oraclesrv and port 1521. The database Service Name is xe.
• 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.

  
  

• The JDBC Driver URL for Oracle^® RAC databases can be added like this:

  jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON) 
  (ADDRESS=(PROTOCOL=TCP)(HOST=tst-db1.myco.com)(PORT=1604)) 
  (ADDRESS=(PROTOCOL=TCP)(HOST=tst-db2.myco.com)(PORT=1604))) 
  (CONNECT_DATA=(SERVICE_NAME=mydb1.myco.com)(SERVER=DEDICATED)))

Hibernate Configuration for PostgreSQL®

<?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>

Explanation:

• The example makes use of the PostgreSQL® database server running for hostname postgresqlsrv and port 5432.

Hibernate Configuration SQL Server®

<?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>

Explanation:

• Note that the JDBC Driver for SQL Server® has to be downloaded separately as it does not ship with JS7.
• The example makes use of the SQL Server® database server running for hostname sqlserversrv and port 1433.
• 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 or  mssql-jdbc_auth-9.2.1.x64.dll or similar. This library should be added to a location that is specified with the Windows PATH environment variable for the JOC Cockpit Windows Service or simply stored to the C:\Windows\System32 directory or to the bin directory of the Java JDK/JRE in use.

Connection Pool

JS7 makes use of a connection pool to allow a larger number of JOC Cockpit user sessions to share database connections from the pool.

• 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 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:

    Hikari Connection Pool recommended settings Expand source

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

    C3P0 Connection Pool recommended settings Expand source

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

Individual JDBC Driver Versions

If you have to use an individual version of a JDBC Driver that is not included with JS7, for example for use with SQL Server® or if you have to use a specific version of the Oracle JDBC Driver®, then you can add the JDBC Driver to JOC Cockpit.

• Consider that conflicting JDBC Drivers could be included with JS7. Such conflicts can originate from the fact that two JDBC Drivers use the same class name as specified with the hibernate.connection.driver_class attribute value from a Hibernate configuration file.
• In case of conflicts remove existing JDBC Driver (*.jar) files when adding your version of a JDBC Driver.

When installing or updating JOC Cockpit then you can specify the JDBC Driver that should be used by the installer, for details see

• JS7 - JOC Cockpit - Use of Linux/Windows Headless Installation
• JS7 - JOC Cockpit - Use of Graphical Installer

The following chapters explain how to add a JDBC Driver to an existing installation without updating JOC Cockpit from the installer.

Adding a JDBC Driver to an existing Installation on premises

Find JDBC Drivers from the following location of the JOC Cockpit configuration directory:

• Unix
  • /var/sos-berlin.com/js7/joc/jetty_base/lib/ext/joc
• Windows
  • C:\ProgramData\sos-berlin.com\js7\joc\jetty_base\lib\ext\joc
• A different location for the configuration directory can be specified during installation of JOC Cockpit.

From this location you will find the JDBC Driver files indicated with the above table. 

• Add your JDBC Driver (*.jar) file to this location
• Optionally remove existing JDBC Driver (*.jar) files from this location, for example to replace the Oracle® JDBC Driver that ships with JS7 by a newer version.

Consider to apply changes to your Hibernate configuration file that reflect change of the JDBC Driver. Find the hibernate.cfg.xm Hibernate configuration file from the JOC Cockpit configuration directory:

• 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 JOC Cockpit.

Restart JOC Cockpit to apply your changes.

Adding a JDBC Driver to a Docker® installation

When using the JOC Cockpit Docker® 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.

For use with a JOC Cockpit container you can copy the JDBC Driver to the following location:

• /var/sos-berlin.com/js7/joc/resources/joc/lib
• The directory /var/sos-berlin.com/js7/joc/resources/joc typically is mounted to a Docker volume when running the container, for details see JS7 - JOC Cockpit Installation for Docker Containers. If the sub-directory lib does not exist then you can create it to store the JDBC Driver (*.jar) file.

Consider to apply changes to your Hibernate configuration file as explained from the previous chapter. Then restart the JOC Cockpit container.

On startup JOC Cockpit checks the above directory for existence of *.jar files. If a JDBC Driver is found then

• the *.jar file will be copied to ./jetty_base/lib/ext/joc 
• any existing JDBC Driver *.jar files in this directory are removed.