CODEWITHSUNDEEP
javamysqlhibernatejpa
jo_wil
jo_wil

Reputation: 369

JPA mapping views and tables with inheritance

I have a db design that I cannot change. The db has tables and view which have a one to one relationship. The views hold some extra information that is calculated from the table. The relevant information being the status of the row. The permission set up for this relationship is views are read-only where as the tables have all CRUD operations available. JPA was the selected ORM to map to this setup. The app is a basic CRUD app with some validation by checking statuses and other attributes in the view then inserting/updating the corresponding tables. Here is an example of two approaces I have tried to model this case. I was wondering which one is more efficient, easier to work with, and/or the 'correct way' to do this.

Option 1 - This options is nice because I can use all the JPA provided interfaces to interact with the DB. This options is bad because I have to sometimes load a table and a model which seems very redundant and I have 3 files per table.

    Model.java
    package models;
    // relevant imports
    public abstract class Model {
       // columns that are shared with table and view
    }

    Table.java
    package models;
    // relevant imports
    @entity("table")
    public class Table extends Model {
       // Relationships with other tables 
    }

    View.java
    package models;
    // relevant imports
    @entity("view")
    public class View extends Model {
       // View specific columns ...
       // Relationships with other views
    }

Option 2 - This option is nice because I only ever have to load the view and I have one file per table. This option is bad because for the CUD operations I have to write native SQL.

    Model.java
    package models;
    // relevant imports
    @entity("view")
    public class Model {
       // All VIEW + table columns
       // All relationships with other models
       // custom SQL to insert update or delete
    }

Upvotes: 4

Views: 3972

Answers (1)

Paul Stanley
Paul Stanley

Reputation: 4098

I'd specify your view entity as a one to one relationship inside your model object with read only access, if all of your tables all have a corresponding view object. You could do this just by writing getters with no setters, as firing any sort of set then saving will run a failing query. Using inheritance like this would lock you in to having to specify all your columns in one level, and you won't know which columns belong to which tables or views.

    Table.java
    package models;
    // relevant imports
    @entity("table")
    public class Table{
        @OneToOne(mappedBy = "table")
        private View view;
        public string getVariable();
        public string setVaraible();
    }

    View.java
    package models;
    // relevant imports
    @entity("view")
    public class View{
       @OneToOne
       @JoinColumn(name = "table_id")
       private Table table;

       public string getVariable();
       public string getVariable2();
       public string getVariable3();//etc, No setters.

       //alternatively use insertable//updateable=false on all column annotation
       @Column(name="variable_4", insertable =  false, updateable=false)
       public string getVariable4();
    }

Lumping them all together in the model object sort of defeats the object of having the ORM there in the first place, because now you will have to write alot of mysql code to match the ORM's basic CRUD functionality. This would be redundnacy on your end.

Not using inhertiance here leaves inheritance open as an actual option should you choose to use it later down the line. Joining to the view every time may be bad for performance depending on how well your views are written of course, but not having them all in the same object allows for more flexibility in that sense.

Upvotes: 2

Related Questions