JS7 JobScheduler

Space shortcuts

Page tree

Versions Compared

Old Version 5

changes.mady.by.user Andreas Püschel

Saved on

compared with

New Version 6

changes.mady.by.user Andreas Püschel

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • 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.
  • 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 Hibernate. This includes to use a JDBC Driver that is specific for the DBMS.

...

For a number of DBMS the JDBC Drivers are included with JS7. However, due to restrictions to bundle JDBC Drivers with Open Source software for some DBMS users have to indiviually individually download

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

Hibernate Configuration for H2®

Code Block
languagexml
titleHibernate configuration file for H2 H2® database
collapsetrue
<?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>

...

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

Code Block
languagexml
titleHibernate configuration file for MariaDB MariaDB® database
collapsetrue
<?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>

...

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

Hibernate Configuration for MySQL®

Code Block
languagexml
titleHibernate configuration file for MySQL MySQL® database
collapsetrue
<?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>

...

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

Hibernate Configuration for Oracle®

Code Block
languagexml
titleHibernate configuration file for MariaDB Oracle® database
collapsetrue
<?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>

...

  • The example makes use of the Oracle® database listener running for hostname oraclesrv and port 1521. The database service ID is xe.

...

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

Code Block
languagexml
titleHibernate configuration file for PostgreSQL PostgreSQL® database
collapsetrue
<?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>

...

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

Hibernate Configuration SQL Server®

Code Block
languagexml
titleHibernate configuration file for SQL Server Server® database
collapsetrue
<?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>

...

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