Using Hibernate identifiers with Teiid

[michael.walker]

I've created a post in the Hibernate Users forum, but I'd like some opinions from the Teiid community as well.

Here's the original post:

https://forum.hibernate.org/viewtopic.php?f=1&t=1005168&p=2431726#p2431726

Basically, I'm trying to figure out the best strategies for identifier generation and usage in Hibernate, when working with federated, legacy databases.

Teiid can solve the problem of overlapping identifiers in different legacy systems by adding a transformation that appends a unique identifier (e.g. a source system id) to each record/PK.

One complication is how to generate new IDs. The Hibernate UUID generator would work, though it would require significant change to the backend systems. The hilo strategy might also work, with some caveats.

Any ideas or recommendations?

[michael.walker]

FWIW, we chose to use Hibernate's UUID generator, as it was still practical to change the backend data sources.

I think the ideal approach would extend Hibernate's ID generator 'select' strategy to support composite keys. Surrogate keys would be generated by the database system. The 'select' strategy could lookup the newly-generated SK by way of the composite key. The composite key would contain a source-system identifier Teiid could use to properly route the request, along with a unique identifier the backend database could use to lookup the surrogate key. This results in two outcalls, but would require zero backend configuration change, and have no impact to legacy systems. Unfortunately, Hibernate doesn't support it, so it would need to be implemented.