Using Teiid with Hibernate

Configuration

For the most part, interacting with Teiid VDBs (Virtual Databases) through Hibernate is no different from working with any other type of data source.  First, depending on where your Hibernate application will reside, either in the same VM as the Teiid Runtime or on a separate VM, will determine which jar’s are used.

  • Running in same VM in the WildFly server, then the teiid-client-{version}.jar and teiid-hibernate-dialect-{version}.jar already reside in <jboss-install>/modules/org/jboss/teiid/client

  • Running separate VM’s,you need the Teiid JDBC Driver JAR and Teiid’s Hibernate Dialect JAR in the Hibernate’s classpath.  The Hibernate JAR can be found in <jboss-install>/modules/org/jboss/teiid/client, teiid-hibernate-dialect-{version}.jar and the Teiid JDBC Driver JAR needs to be downloaded.

These JAR files have the org.teiid.dialect.TeiidDialect and org.teiid.jdbc.TeiidDriver and org.teiid.jdbc.TeiidDataSource classes.

You configure Hibernate (via hibernate.cfg.xml) as follows:

  • Specify the Teiid driver class in the connection.driver_class property:

<property name="connection.driver_class">
     org.teiid.jdbc.TeiidDriver
</property>

  • Specify the URL for the VDB in the connection.url property (replacing terms in angle brackets with the appropriate values):

<property name="connection.url">
    jdbc:teiid:<vdb-name>@mm://<host>:<port>;user=<user-name>;password=<password>
</property>
Tip
 Be sure to use a Local JDBC Connection if Hibernate is in the same VM as the application server.

  • Specify the Teiid dialect class in the dialect property:

<property name="dialect">
    org.teiid.dialect.TeiidDialect
</property>

Alternatively, if you put your connection properties in hibernate.properties instead of hibernate.cfg.xml, they would look like this:

hibernate.connection.driver_class=org.teiid.jdbc.TeiidDriver
hibernate.connection.url=jdbc:teiid:<vdb-name>@mm://<host>:<port>
hibernate.connection.username=<user-name>
hibernate.connection.password=<password>
hibernate.dialect=org.teiid.dialect.TeiidDialect

Note also that since your VDBs will likely contain multiple source and view models with identical table names, you will need to fully qualify table names specified in Hibernate mapping files:

<class name="<Class name>" table="<Source/view model name>.[<schema name>.]<Table name>">
    ...
</class>
Example Mapping
<class name="org.teiid.example.Publisher" table="BOOKS.BOOKS.PUBLISHERS">
    ...
</class>

Identifier Generation

SEQUENCE Based Identity Generation

If you want use SEQUENCE based Identity generation with Teiid, this is supported through the TeiidDialect. When you define a JPA Entity

public class Customer {
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "customer_generator")
	@SequenceGenerator(name="customer_generator", sequenceName = "customer_seq")
	@Id
	Long id;
}

In the Teiid VDB, define a virtual function as below example. Note, "_nextval" appended to the sequence name on the name of the function.

CREATE VIRTUAL FUNCTION customer_seq_nextval() RETURNS long
AS
BEGIN
    -- Your code to retrieve the sequence from source database
    -- or generate one in Teiid.
END;

Given the above template, if for example you are working with Oracle would like to use the Oracle sequence you already defined as "customer_seq" in your Oracle database, then create View procedure in Teiid as

CREATE VIRTUAL FUNCTION customer_seq_nextval() RETURNS long
AS
BEGIN
    SELECT OracleDB.mySequence_nextval();
END;

Stating with Teiid 10.0 some sources, including DB2, Oracle, H2, PostgreSQL, DB2, support automatic import of sequence information. For other sources you need to add source functions to represent the sequence calls. For example assuming you wanted to do this manually for Oracle, then in your OracleDB source model, create a source function:

CREATE FOREIGN FUNCTION mySequence_nextval() RETURNS long
OPTIONS ("teiid_rel:native-query" 'SELECT customer_seq.NEXTVAL FROM dual', DETERMINISM 'NONDETERMINISTIC');

Then when the Customer entity is inserted, the sequence is used.

TABLE Based Idenity Generation

If you want use TABLE based Identity generation with Teiid, this is supported through the TeiidDialect. When you define a JPA Entity like

public class Customer {
	@TableGenerator(name = "customer",
			table = "id_generator",
			pkColumnName = "idkey",
			valueColumnName = "idvalue",
			pkColumnValue = "customer",
			allocationSize = 1)
	@GeneratedValue(strategy = GenerationType.TABLE, generator = "customer")
	@Id
	Long id;

	...
}

Then create a virtual table in Teiid’s view model as

CREATE VIEW id_generator (
	idkey string(255) NOT NULL,
	idvalue long,
	CONSTRAINT id_generatorPK PRIMARY KEY(idkey)
) OPTIONS (UPDATABLE TRUE)
AS
SELECT IDKEY, IDVALUE FROM OracleDB.IDGENERATOR;

Where in OracleDB, you have a physical Table called "IDGENERATOR" and with above shown columns. When you use this technique, please make sure you have seed content like below to begin with

INSERT INTO IDGENERATOR(IDKEY, IDVALUE) VALUES ('customer', 100);

such that the IDKEY matches and IDVALUE has a initializer value.

IDENTITY Based identity generation

  • GUID and Identity (using generated key retrieval) identifier generation strategy are directly supported.

Limitations

  • Many Hibernate use cases assume a data source has the ability (with proper user permissions) to process Data Definition Language (DDL) statements like CREATE TABLE and DROP TABLE as well as Data Manipulation Language (DML) statements like SELECT, UPDATE, INSERT and DELETE. Teiid can handle a broad range of DML, but does not directly support DDL against a particular source.

  • Sequence generation is not directly supported.

results matching ""

    No results matching ""