carbolymer carbolymer - 1 month ago 23
Java Question

JPA secondary table as read only view - hibernate still tries to insert rows

I've got the following entity:

@Entity
@Table(name = "ONE")
@SecondaryTable(name = "VIEW_TWO", pkJoinColumns = @PrimaryKeyJoinColumn(name="ONE_ID"))
public class CpBracket {

@Id
private Long id;

@Column(name="progress", table="VIEW_TWO", updatable = false, insertable = false)
private int progress = 0;

(...)
}


As you see, this entity uses table ONE and (read only) view VIEW_TWO. When I'm persisting the entity, hibernate is performing insert into view:

insert into VIEW_TWO (ONE_ID) values (?)


It is ignoring the non-updatable and non-insertable column progress (that's good) and it is still trying to insert value of ONE_ID column. As far as I know, the annotation @PrimaryKeyJoinColumn marks selected column as insertable=false and updatable=false.

How can I prevent hibernate from inserting rows into secondary table (view)?

Answer

As far as I know, the annotation @PrimaryKeyJoinColumn marks selected column as insertable=false and updatable=false.

I do not believe this can be the case: how then do we get records inserted into the @SecondaryTable when it is an actual table rather than a view?

As neither @SecondaryTable or @PrimarykeyJoinColumn have a means to prevent insert then it would appear that your original solution is not going to work and an alternative is required.

One option is to map VIEW_TWO as an @Entity and link to your class CPBracket as a @OneToOne relationship with cascade options set to none.

@Entity
@Table(name ="VIEW_TWO")
private CpBracketSummaryData(){

}


@Entity
@Table(name = "ONE")
public class CpBracket {

    @OneToOne
    @PrimaryKeyJoinColumn
    private CPBracketSummaryData summaryData;

    public int getSomeValue(){
        return summaryData.getSomeValue();
    }
}

The second option would be to use the non JPA compliant, Hibernate specific @Formula annotation.

@Entity
@Table(name = "ONE")
public class CpBracket {

       @Formula("native sql query")
       private int someValue;
}

Update October 2016

I have revisited this in both Hibernate 4.3.10.Final and 5.1.0.Final and it is possible to have the view as a @SecondaryTable without the insert: if you have the correct mappings.

Scenario 1

Load an entity for edit and do not touch any fields mapped to the secondary table. No update is issued to the secondary table

Scenario 2

Create and save a new entity and do not set any fields mapped to the secondary table. No insert is issued for the secondary table

Scenario 3

Create or update an entity including a field mapped to a secondary table and where this field is marked as insertable = false and updateable = false. An insert is made to the secondary table only for the ID field -the behaviour reported in the original question.

The issue with the mapping in the original question is the fact that the secondary table field is a primitive type and therefore Hibernate does think a record has to be written to the secondary table with a value of zero.

@Column(name="progress", table="VIEW_TWO", updatable = false, insertable = false)
private int progress = 0;

The solution then is to change use the wrapper types for primitives and leave them as null. Therefore on saving a new record there is nothing to write to the secondary table and no insert will be made:

@Column(name="progress", table="VIEW_TWO", updatable = false, insertable = false)
private Integer progress = 0;