0

I have three database views that are mapped in Hibernate as entities. The entities are in a parent-child relationship (1 parent (A), 2 children(B & C)).

One of the children views (B) uses Oracle's dbms_utility.get_hash_value() to calculate its ID. This is because it does a UNION over several tables that use different ID sequences and thus the IDs from there may not be unique.

I now have the very puzzling effect that a simple entityManager.find(B.class, id) cannot find the appropriate row.

When I look at the children through a loaded parent (A) entity, I can see that the ID shown in B is completely different from the one in the database. If I use this ID with entityManager.find(B.class, hibernateId), Hibernate finds the appropriate entity.

The database, on the other hand, only returns a value when using the ID shown in the ID column there (and not with the ID Hibernate shows).

Child entity C does not use the hash function and does not show this peculiar behaviour - which means the hash must be responsible.

Does anyone have an idea why?

Kirinya
  • 245
  • 3
  • 12
  • Enable [logging](https://stackoverflow.com/questions/1710476/how-to-print-a-query-string-with-parameter-values-when-using-hibernate) to see what is Hibernate sending to DB. Additionally, rething twice your current design. To avoid collisions in the IDs in the view you may use two column key (source table, id) - this would preserve effective index access. Accessing with hash code you allway **full scan** all the tables in the view, right? – Marmite Bomber Feb 22 '18 at 12:10
  • Logging shows that Hibernate uses the ID used in the em.find() statement. A two column key is not possible in B's case because the underlying query is too complex (we did a simple two column key like this for C). Performance is not a (very relevant) issue as the views will be mostly queried in bulk anyway. – Kirinya Feb 22 '18 at 14:22

1 Answers1

0

We found the reason: Child view B used all of its (content containing) columns as a string concatenation for the hash function. This included date fields, which were not explicitly formatted when creating the string.

So, when Hibernate selected from the view, it obviously used another format than SQL Developer and thus produced completely different (but consistent) IDs.

Explicitly formatting the used date fields removed the problem.

Kirinya
  • 245
  • 3
  • 12