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.
You will need to have the following libraries in your classpath:
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
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:
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
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; private String name; private int age; ............. } |
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
@Id private int id; private String name; private int age; ............. } |
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
@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; ............. } |
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:
You need to create the JDBC data source and the base views that correspond with your Java entities, using the Virtual DataPort Administration Tool.
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).
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:
HibernateSequenceControllerStoredProcedure
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.
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.
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:
CREATE sequence "foo_sequence" START WITH 1 INCREMENT BY 1 |
CALL hibernate_sequence_controller('create','oracle_ds:foo_sequence',1,1) |
This means that establishing the property hibernate.hbm2ddl.auto to any of its possible values will have no effect.