in Coding, Java

Using Spring Data for database views without an ID

A database view is a great way to prepare complicated data structures right in the database and present it in a more convenient way. We usually use database views to map our technical driven database structure (with a lot of tables and foreign keys) to a more business driven structure. This means that we might join tables, concatenate values or just rename the columns, because a particular name suits better to the current business context.

To map those views to Java entities, we simply use Spring data:

However, the example above wouldn’t work. Spring would complain that the @Id annotation is missing and would throw a org.hibernate.AnnotationException: No identifier specified for entity exception.

From a business perspective, this is bad. The id of a company is its company id, the id of a person is its person id. But both are missing in the view, which has not unique identifier.

We could solve this problem by a simple trick: we invent an unique identifier – our current row number!

We can now use the row number in our Spring Data entity:

Best regards,
Thomas

Write a Comment

Comment

  1. Hi, it’s best not to do that. You will get bitten by caching say you have two different queries within the same transaction so if you get id 1 for the first query and try again for the second, regardless of the result you will get the cached copy of the first query.
    On Oracle rowid would be a safer bet