JS7 JobScheduler

Space shortcuts

Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 8 Next »

Introduction

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

JDBC Drivers

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

DBMS
Product		DBMS
Version		JDBC Driver
Version		JDBC Driver
File		JDBC Driver
License		Included with JS7Comment
H2®1.4.2001.4.200h2-1.4.200.jarMPL 2.0, EPL 1.0yesDBMS and JDBC Drivers are Open Source
MariaDB®102.7.2mariadb-java-client-2.7.2.jarLGPLyesDBMS and JDBC Drivers are Open Source
MySQL®5.7, 8.08.xmysql-connector-java-8.x.jarProprietary Licenseno

DBMS is Open Source, JDBC Drivers are not Open Source

The MariaDB® JDBC Driver can be used for MySQL®

Oracle®12c, 18c, 19c19.7ojdbc8.jarFUTC LicenseyesDBMS and JDBC Drivers are not Open Source

PostgreSQL®10, 11, 1242.2.19postgresql-42.2.19.jarBSD 2-clauseyesDBMS and JDBC Drivers are Open Source
SQL Server®2012, 2014, 2017, 20199.xmssql-jdbc-9.x.jre8.jar
mssql-jdbc-9.x.jre11.jar
mssql-jdbc-9.x.jre15.jar		Proprietary LicensenoDBMS and JDBC Drivers are not Open Source

Hibernate Configuration Files

  • 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.
  • By default the Hikari Connection Pool is used.

Hibernate Configuration for H2®

Hibernate configuration file for H2® database 
<?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.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

Explanations:

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

Hibernate configuration file for MariaDB® database 
<?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:mysql://mariadbsrv: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.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

Explanations:

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

Hibernate Configuration for MySQL®

Hibernate configuration file for MySQL® database 
<?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.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

Explanations:

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

Hibernate Configuration for Oracle®

Hibernate configuration file for Oracle® database 
<?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.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

Explanations:

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

Hibernate Configuration for PostgreSQL®

Hibernate configuration file for PostgreSQL® database 
<?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.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

Explanations:

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

Hibernate Configuration SQL Server®

Hibernate configuration file for SQL Server® database 
<?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.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

Explanations:

  • The example makes use of the SQL Server® database server running for hostname sqlserversrv and port 1433.

Connection Pool

Hibernate offers a number of Connection Pool implementations.

  • By default the Hikari Connection Pool is used.
    • The Hikari setting for the maximumPoolSize should be included with a Hibernate configuration file as this determines the number of open database connections. Adjust this setting to the number of connections required for sharing between parallel users of JOC Cockpit. As a rule of thumb 10 connections should work for approx .30 parallel users.
    • Any additional settings for Hikari can be added to a Hibernate configuration file.



  • No labels