13
  • I have views in a SQL database with no obvious primary key (composite or otherwise)
  • I would like to access them through JPA

I've read that I should be able to treat views in JPA like I treat tables (using the @Table annotation etc.). However without a primary key I have to effectively make a composite key out of EVERY COLUMN (in fact, this in what Hibernate's reverse-engineering tool seems to do by default).

However if I do this there are undesirable side effects. E.g.

  • Having to write all you code pointing to the primary key's attributes rather than the views:

    myViewObject.getPrimaryKey().getFirstName()

  • Not being able to use the "findBy..." methods on the spring Repository (since that attribute is part of the view's "identifier" and not actually one of it's attributes).

My question is: How do I map views in such a was as I can easily access their attributes using JPA?

Note: I'm quite happy to be told I'm using completely the wrong approach. This seems like such a common problem there's bound to be a better solution.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
Andy N
  • 1,238
  • 1
  • 12
  • 30
  • One trick I employ is to analyze possible queries on the view to see how many have the potential of returning multiple records and seeing if there is a field that will remain unique for all records in all those situations. If you can find such a scenario, you can designate that field as the primary key, even if it is not really the primary key since the ORM only cares about uniqueness among results of a single query. But note that you will not be able to use the second-level cache. In cases where this trick is not applicable, I modify the query for the view to include a primary key. – manish Jul 08 '15 at 12:14
  • There's nothing to stop you writing delegating methods such as `getFirstName()` which does `return this.getPrimaryKey().getFirstName()`. Then you only have to write the long version once. As for being the wrong approach, we have over 400 Entities/Tables in our application and we do not have a single View-Entity. I tried using one once because I thought it would help, but it started causing issues, so I created the same 'view' in JPQL which did the same job, and gave me more flexibility. – DuncanKinnear Jul 08 '15 at 21:08

1 Answers1

18

You can add a UUID column to every row of the Views so then you can use the UUID column as an @Id.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • Yes, that worked, thanks. I've altered my (Oracle) views to include an extra column: `sys_guid() AS uuid` Then added the following property to the top of my view @Entity: `@Id @Column(name="UUID") @JsonIgnore protected String uuidHex;` – Andy N Jul 08 '15 at 15:14
  • 4
    Andy, I'd assume the implementation of the solution is faulty. The UUID will be different on every query. Basically it is a valid id, but I wouldn't expect to be able to select Entity A by a given UUID more than one time, ie. you won't be able to navigate from a list of entities to a single entity. So, that UUID should be part of one of the base tables and actually stored as a column and not computed in the view. An alternative would be using materialized views. – Michael Simons Jul 08 '15 at 15:55
  • On the contrary, it's exactly what I needed to solve the problem. My issue was that there was no obvious primary key (see above). Imagine a collection of pure data columns, some or all of which may be null (typical enough in SQL databases). I had to ARTIFICIALLY give them a PK to keep hibernate happy. Adding a generated UUID to the query satisfied this criteria. Navigation was never an option in the first place. – Andy N Jul 09 '15 at 09:44
  • adding `@Immutable` to the Entity will work too? just imagine you don't have access to the view and you need to map it to an entity, then adding `@Immutable` will work? – Mohamad Eghlima May 07 '19 at 17:41
  • 3
    @MohammadEghlima: Nope, you would get a `org.hibernate.AnnotationException: No identifier specified for entity: ` error. – Jacob van Lingen Feb 20 '20 at 10:13