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?

Coreligionist

excuse the offtopic question please, but why should anybody want to do this? – Cornie

i work in a big organisation, and we have some database guidelines written from someone from somewhere that says we are required to use views, for security, and management purposes. – Coreligionist

Your application clearly needs write access to the database. I don't see what security, or other, benefit this provides if your views contain the same data as your tables. – Unstep

I had to give your question +1 for your comment. "Someone somewhere. [..] For security." – Atory

https://mcmap.net/q/389328/-does-jpa-support-mapping-to-sql-views – Benfield

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.

Slacker

Should I retype all fields manually? Is it possible to allow JPA to infer them atutomatically? – Hurlburt

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.

Linnealinnean

as long as the JPA implementation supports views too (which some do, DataNucleus JPA certainly does) – Akilahakili

All JPA implementations I know are based on JDBC. The SQL statements is going to be the same (insert into T ...) regardless if T is a table or a view. This is why I think that it only depends on the RDBMS to support an updatable view, while the JPA implementation doesn't need to know about it. Unless I am missing something. – Linnealinnean