Using Spring Data for database views without an ID

Thomas Uhrig · November 13, 2015

A database view is a great way to prepare complicated data structures and present it in a more convenient way. We can 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.

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 can use Spring Data:

@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 will 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 no unique identifier.

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

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:

@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.