JPA and Table Views. Can it be done? [duplicate]

Question

We currently have a Java EE system where we are mapping to our database using JPA. It is a fairly well developed system with about 20 entities.

We now have been ordered to use Views for everything. Eg: if we have a table called PERMISSION then we also need a view called PERMISSION_VIEW. Basically we need to do this to every table, and our applications can only access the data by querying the view.

Now all our entity beans look like this :

@Entity @Table(name = "PERMISSION") @NamedQueries({         @NamedQuery(name = "Permission.findByPK", query = "SELECT p FROM Permission p WHERE p.dpNum = :dpNumber"),         @NamedQuery(name = "Permission.deleteAll", query = "DELETE FROM Permission") }) public class Permission implements Serializable {  } 

• Firstly, how is it possible to update tables if you are only allowed to use Views. Can Materialised Views work for this?
• Secondly, how much rewriting is going to be needed, if we can only use Views? Eg. For each entiry we will need to write @Table(name = "PERMISSION_VIEW"), to describe the entity, BUT, when doing an update it needs to do that to the PERMISSION table. How on earth do you consolidate this in an entity bean?

Answer 1

For more info on JPA and database views see, http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#Views

In JPA you can map to a VIEW the same as a table, using the @Table annotation. You can then map each column in the view to your object's attributes. Views are normally read-only, so object's mapping to views are normally also read-only. In most databases views can also be updatable depending on how complex to query is that they encapsulate. Even for complex queries database triggers can normally be used to update into the view.

Answer 2

Most modern RDBMSs support insertable and updatable views. If your RDBMS supports it, then you shouldn't have any problem. A view that is identical to a table should be updatable in any RDBMS that supports such views. So you only need to change your mapping and replace the table names with the view names.