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

JDBC Drivers

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

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