Denodo Hibernate Dialect - User Manual

Last modified on: 05 Aug 2021

Download original document

Overview

Hibernate is a framework that provides object/relational mapping (ORM) services for relational databases.

Hibernate is database agnostic. This means that it can work with different databases. However, as databases implement slight variations of the SQL standard, Hibernate includes vendor specific dialects to handle these differences and accomplishes tasks like obtaining a primary key identifier or structuring a SELECT query.

The Denodo Hibernate Dialect is the dialect provided by Denodo to generate the appropriate VQL so Hibernate can deal with Denodo databases.

This dialect supports Hibernate versions 5.2 to 5.4.

   

                                  Hibernate - Denodo integration

Disclaimer: This dialect has some limitations due to the conceptual mismatch between an ORM and a data virtualization platform. Therefore, Denodo does not recommend the use of Hibernate, or any ORM, on top of Denodo.

JAVA Configuration

Dependencies

You will need to have the following libraries in your classpath:

• Hibernate

• Denodo VDP JDBC driver

• Denodo Hibernate Dialect jar: denodo-hibernate-dialect-<version>.jar in the /dist directory of the Denodo Hibernate Dialect distribution

Properties

You need to add the following property in your Hibernate configuration file:

dialect=com.denodo.connect.hibernate.dialect.DenodoDialect

besides the JDBC connection properties:

connection.driver_class=com.denodo.vdp.jdbc.Driver

connection.url=jdbc:vdb://localhost:9999/database

connection.username=…

connection.password=…

If yours is a Spring Boot application, configuration will look similar to this:

spring.datasource.driver-class-name=com.denodo.vdp.jdbc.Driver

spring.datasource.url=jdbc:vdb://localhost:9999/database

spring.datasource.username=…

spring.datasource.password=…

spring.jpa.properties.hibernate.dialect=com.denodo.connect.hibernate.dialect.DenodoDialect

#Avoids the appearance of HHH000424 in log during startup

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

Entity Java class with auto-increment keys

If you need to use primary key values that are automatically generated by the underlying database by means of auto-increment columns, you can do so by using the identity sequence generation strategy in the Denodo Hibernate Dialect. This will only work for JDBC base views created on top of tables with an auto-increment column for their primary key.

Note this feature will only work from:

• Denodo 7.0 update 20201116.
• Denodo 8.0 update 20210209.

Your entity class will look similar to this:

package …; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.SequenceGenerator; @Entity public class Person {              @Id     @GeneratedValue(strategy = GenerationType.IDENTITY)     private int id;     private String name;     private int age;     ............. }

Entity Java class with assigned keys

If you do not need the primary key value to be generated automatically you only have to use the @Id annotation that marks which entity field is the primary key and initialize its value in your application.

package …; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.SequenceGenerator; @Entity public class Person {              @Id     private int id;     private String name;     private int age;     ............. }

Entity Java class with sequence-based keys (deprecated)

If you want the primary key value to be generated automatically from a sequence you will need to define your entities with Hibernate’s Sequence generator and use the sequence generator stored procedure in your VDP database (explained later).

Note this method is intrusive in your VDP database (and might be so to your source database too) as it forces you to create additional supporting structures.

That the name of the sequence in your Hibernate mappings needs to follow this convention:

denodo_data_source_name:sequence_name,

...being denodo_data_source_name the name given in Denodo to the data source that contains the entity tables and the sequence-support structures.

package …; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.SequenceGenerator; @Entity public class Person {              @Id     @GeneratedValue(strategy = GenerationType.SEQUENCE,              generator = "sequence-generator")     @SequenceGenerator(name = "sequence-generator",              sequenceName = "denodo_dataSource_name:foo_sequence",                         initialValue = 1, allocationSize = 1)         private int id;     private String name;     private int age;     ............. }

Datetime mappings in Denodo 7

To persist the new datetime types introduced in Denodo 7 you have to use the following Java types in you entity Java classes:

VDP Type	Java Type
localdate	java.time.LocalDate java.sql.Date
time	java.time.LocalTime java.sql.Time
timestamp	java.time.LocalDateTime
timestamptz	java.time.OffsetDateTime java.sql.Timestamp
intervalyearmonth	java.time.Period
intervaldaysecond	java.time.Duration

For  more information on mappings between VDP datetime types and Java types, you can visit the Denodo 7 documentation:

https://community.denodo.com/docs/html/browse/7.0/vdp/developer/access_through_jdbc/details_of_the_jdbc_interface/details_of_the_jdbc_interface#working-with-datetime-values-with-the-denodo-jdbc-driver

Denodo Configuration

General Denodo configuration

You need to create the JDBC data source and the base views that correspond with your Java entities, using the  Virtual DataPort Administration Tool.

Additional configuration for sequence-based key generation (deprecated)

In order to use the Sequence generator to generate primary key values, you will need to perform additional configuration steps in your Denodo database, as well as your data sources (only MySQL and Oracle are supported).

Denodo configuration for sequence-based key generation

---

You will need to import the stored procedure Hibernate Sequence Controller in Denodo, (denodo-hibernate-sequence-controller-<version>.jar in the /dist directory of this distribution).

For this, in the Virtual DataPort Administration, click Stored procedure on the menu File > New and provide the following values:

• Name: hibernate_sequence_controller

• Class name: com.denodo.connect.hibernate.sequence.

HibernateSequenceControllerStoredProcedure

• Select Jars: denodo-hibernate-sequence-controller-<version>

If you do not need the primary key value to be generated from a sequence you do not need to import the stored procedure Hibernate Sequence Controller in Denodo.

MySQL configuration for sequence-based key generation

You need to import the script mysql_hibernate_sequence_definition.sql,(in the /scripts directory of this distribution), into your MySQL server in order for the Sequence generator strategy to work properly.

The mysql_hibernate_sequence_definition.sql script defines several procedures and a table for generating unique values when invoked by the Denodo Hibernate Dialect.

Oracle configuration for sequence-based key generation

If your data source is Oracle, the Denodo Hibernate Dialect will use sequence objects provided by Oracle.

You have to create these sequences manually, otherwise your Java application will throw the error:

ORA-02289: sequence does not exist

For a configuration of the Sequence Generator at the Entity Java class like this:

@SequenceGenerator(name = "sequence-generator",              sequenceName = "oracle_ds:foo_sequence",                         initialValue = 1, allocationSize = 1)

...you have two options for creating the sequence:

1. In the Oracle database 

CREATE sequence "foo_sequence" START WITH 1 INCREMENT BY 1

2. In Denodo Administration Tool > VQL Shell > selecting the proper database

CALL hibernate_sequence_controller('create','oracle_ds:foo_sequence',1,1)

Limitations

• Note that Denodo Hibernate Dialect does not support DDL operations.

This means that establishing the property hibernate.hbm2ddl.auto to any of its possible values will have no effect.

• Only MySQL and Oracle data sources are supported for sequence-based key generation. As Denodo does not provide a method to generate unique integers on its own, the support of this dialect for generating primary keys depends highly on the underlying database and requires an intrusive setup on the data source. Therefore this strategy for the generation of primary key values is not recommended, and deprecated at the current version of the dialect.

• Only JDBC-backed base views can be used with the “identity” primary key generation strategy, and only as long as the corresponding JDBC driver supports the getGeneratedKeys() functionality.

• This dialect only supports mapping entities in read+write mode to derived views that are considered “updateable” by Denodo (though using only base views is recommended); and in every case only “assigned” primary key values can be used as a key generation strategy for derived views.

• This dialect does not allow the call of stored procedures, functions or any other structures or functionality in the underlying JDBC datasource, other than the set of functions allowed by VQL which may have a mapping to the underlying database’s SQL dialect.