JPA: Read from view update table

Question

Is it possible to make JPA read an entity from a view and then update a table.

To give an example:

We have an entity that reads from SuppliersInvoice_VW. This view and the associated entity will contain InvoiceNo, SupplierCode, SupplierName and value.

The user sees something along these lines:

Invoice No: 1234
Supplier: Joes' Hardware
Value: 50

Which makes him realise that the value should be 509, so he updates accordingly

Invoice No: 1234
Supplier: Joes' Hardware
Value: 509

This then triggers an update on our SuppliersInvoice entity.

I've mapped @Table("SuppliersInvoice_vw") which means it reads the data from SuppliersInvoice_vw, but this also means that its going to update through that view.

Is there a way to make JPA read from a view( SuppliersInvoice_vw ) and update a table(SuppliersInvoice)?

The alternate strategy is to just update the view, however I've always been advised not too do this. Does this advice have a performance foundation, or is it more along the lines of 'be aware of what can go wrong when updating through views'.

Answer 1

I don't think it's possible to do exactly what you want with JPA transparently.

The JPA spec only allows a single @Table per @Entity. Probably the easiest (and most portable) way to deal with this is to have two separate @Entity classes, one for the view and one for the table, with similar properties, and pick which one to use based on context of whether you're reading or writing.

If the properties line up you can use commons BeanUtils.copyProperties to initialize the writable object from the view one.

Essentially, whatever read-only entity is mapped to the database view is like a DTO, and you would treat it as such.

Answer 2

I've run into this same issue with views/tables and Entities before. I don't know what backend database you're using, but in my case I was running PostgreSQL 9.x. To address this issue, I created triggers in my database that would allow me to INSERT or UPDATE into my view, and then via the triggers update the underlying table(s) behind the view appropriately. That way, you can still update your SuppliersInvoice entity object and persist the changes using JPA as your normally would, but the backend database would update the real tables appropriately based on the updates you're providing.

Here's a link to the INSERT/UPDATE/DELETE rules documentation from PostgreSQL 9.1 so you can see for yourself.