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.
|
1 2 3 4 5 6 7 8 |
CREATE OR REPLACE VIEW COMPANY_TO_PERSON AS SELECT COMPANY.NAME AS COMPANY_NAME, PERSON.NAME AS PERSON_NAME FROM COMPANY LEFT JOIN PERSON ON COMPANY.COMPANY_ID = PERSON.COMPANY_ID; |
To map those views to Java entities, we simply use Spring data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
@Entity @Table(name = "COMPANY_TO_PERSON") // A view! public class Company2Person { // This won't work as the entity has no @Id! @Column(name = "COMPANY_NAME") private String companyName; @Column(name = "PERSON_NAME") private String personName; // getter and setter... } |
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!
|
1 2 3 4 5 6 7 8 9 |
CREATE OR REPLACE VIEW COMPANY_TO_PERSON AS SELECT rownum AS ID, COMPANY.NAME AS COMPANY_NAME, PERSON.NAME AS PERSON_NAME FROM COMPANY LEFT JOIN PERSON ON COMPANY.COMPANY_ID = PERSON.COMPANY_ID; |
We can now use the row number in our Spring Data entity:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
@Entity @Table(name = "COMPANY_TO_PERSON") // A view! public class Company2Person { @Id @Column(name = "ID") private Long id; // The row number! @Column(name = "COMPANY_NAME") private String companyName; @Column(name = "PERSON_NAME") private String personName; // getter and setter... } |
Best regards,
Thomas
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
As per perfromance concenrned. rownum is very slow at oracle side
Extremely bad…