HIbernate not inserting due to not-nullconstraint with composite FK

Question

I'm trying to save a complex entity with Hibernate where we have multiple entities with composite keys. When I try tosave it seems that Hibernate it is not retrieving correctly the values from some columns on child entities which have composite key hence postgre is returning a not-null violation error.

@Entity
@Table(name = "activities", schema = "ptw")
@Data
@TypeDefs({
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class),
        @TypeDef(name = "pg-id-uuid", typeClass = PostgresIdUUIDType.class)
})
public class Activity extends AuditAtBy implements Serializable {

@EmbeddedId
private CommonId commonId;

@MapsId("siteId")
@ManyToOne
@OnDelete(action = OnDeleteAction.CASCADE)
@Type(type = "pg-id-uuid")
@JoinColumn(name="site_id",referencedColumnName = "id", columnDefinition = "uuid", updatable = false)
private Site site;

@ManyToOne()
@JoinColumnsOrFormulas(value = {
        @JoinColumnOrFormula(formula = @JoinFormula(value="location_id", referencedColumnName = "id")),
        @JoinColumnOrFormula(formula = @JoinFormula(value="site_id", referencedColumnName = "site_id"))})
@Type(type = "pg-id-uuid")
@OnDelete(action = OnDeleteAction.CASCADE)
private Location location;

@ManyToOne
@JoinColumns({
        @JoinColumn(name = "job_pack_id", referencedColumnName = "id", columnDefinition = "uuid", insertable = false, updatable = false),
        @JoinColumn( name = "site_id", referencedColumnName="site_id", columnDefinition = "uuid", insertable = false, updatable = false)
})
@Type(type = "pg-id-uuid")
@OnDelete(action = OnDeleteAction.CASCADE)
private JobPack jobPack;

@Column(name = "permit_number", nullable = false, length = 10)
private String permitNumber;

@Column(name = "order_number", nullable = false)
private short orderNumber;


@Column(name = "location_name", length = 200)
private String locationName;

**@ManyToOne
@JoinColumns({
        @JoinColumn(name = "state_id", referencedColumnName="id", columnDefinition = "uuid", insertable = false, updatable = false),
        @JoinColumn(name = "site_id", referencedColumnName="site_id", columnDefinition = "uuid", insertable = false, updatable = false)
})
@Type(type = "pg-id-uuid")
private ActivityState state;**

@ManyToOne
@JoinColumns({
        @JoinColumn(name = "activity_type_id", referencedColumnName="id", columnDefinition = "uuid", insertable = false, updatable = false),
        @JoinColumn(name = "site_id", referencedColumnName="site_id", columnDefinition = "uuid", insertable = false, updatable = false)
})
@Type(type = "pg-id-uuid")
@OnDelete(action = OnDeleteAction.CASCADE)
private ActivityType activityType;




public UUID getId(){
    return this.getCommonId().getId();
}

public void setId(UUID id){
    if (this.getCommonId() == null) {
        this.setCommonId(new CommonId());
    }
    this.getCommonId().setId(id);
}

public void setSite(Site site){
    this.site = site;
    if (this.getCommonId() == null) {
        this.setCommonId(new CommonId());
    }
    this.getCommonId().setSiteId(site.getId());
}
}

The setId/getId/ setSite are overriden in order to update the entity when using a mapper to convert from the DTO to the Entity

The ActivityState is as follows:

@Entity
@Table(name = "activity_states", schema = "ptw")
@Data
@TypeDefs({
        @TypeDef(name = "pg-id-uuid", typeClass = PostgresIdUUIDType.class)
})
public class ActivityState extends AuditAtBy implements Serializable {

    @EmbeddedId
    private CommonId commonId;

    @MapsId("siteId")
    @ManyToOne
    @OnDelete(action = OnDeleteAction.CASCADE)
    private Site site;

    @Column(nullable = false, length = 50)
    private String name;

    @Column(name = "icon_id", nullable = false)
    private short iconId;

    @Column(name = "is_initial", nullable = false)
    private boolean isInitial;

    @Column(name = "order_number", nullable = false)
    private short orderNumber;

    public UUID getId(){
        return this.getCommonId().getId();
    }

    public void setId(UUID id){
        if (this.getCommonId() == null) {
            this.setCommonId(new CommonId());
        }
        this.getCommonId().setId(id);
    }

    public void setSite(Site site){
        this.site = site;
        if (this.getCommonId() == null) {
            this.setCommonId(new CommonId());
        }
        this.getCommonId().setSiteId(site.getId());
    }
}

When I try to save the exception is:

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "state_id" violates not-null constraint
  Detail: Failing row contains (c821ff72-de93-4c03-abf5-e18347c29955, null, 0, 5081790f-19ed-44e0-be17-94f94aed878b, null, null, test, test, null, 1, 1, f, N/A, 1, null, null, null, null, null, null, null, null, null, null, null, f, {"title": "test", "DateTable": {"timeTo": "", "timeFrom": "", "v..., 2021-12-09 13:46:02.829157+01, 2021-12-09 13:46:02.829157+01, 7b0702c7-9f11-4a92-bfdf-7f98eb8ac94d, 7b0702c7-9f11-4a92-bfdf-7f98eb8ac94d, null, null, f).

I have no idea about how to solve although I have tried multiple things changing the mappings and relationships. I know that composites keys are not the best approach but we have a multitenancy system where the best approach to keep data isolated was this one.

What do you expect from `insertable = false, updatable = false`? You have forced HBN to not populate values of state_id in DB, and HBD is not doing that. — Andrey B. Panfilov, Dec 09 '21 at 13:44
Without that, I can not map the entity. When I try removing that value I et this: Repeated column in mapping for entity: com.basf.swift.ptw.model.entity.Activity column: site_id (should be mapped with insert="false" update="false") Because I need to map more than one entity to same column site_id. — arkhadi, Dec 09 '21 at 13:58
Fixed with this: https://stackoverflow.com/questions/4231452/should-hibernate-be-able-to-handle-overlapping-foreign-keys Using @JoinColumnsOrFormulas allowed us to remove the insertable= false and hance it is working. — arkhadi, Dec 09 '21 at 14:15
Why not just mark that one state_id join column as insertable=true? You can leave the other site_id join column as insertable/updatable=false to avoid it being changed and affecting the entity's identity. — Chris, Dec 09 '21 at 20:25
Actually you can not do that. HIbernate will fail when starting the app, all of the columns in a JoinColumns annotation need to have the same flags, so either both or them are insertable or not. In order to avoid that problem we found that this approach would work. @JoinColumnsOrFormulas(value = { @JoinColumnOrFormula(column = @JoinColumn(name="state_id", referencedColumnName = "id")), @JoinColumnOrFormula(formula = @JoinFormula(value="site_id", referencedColumnName = "site_id")) }) — arkhadi, Dec 10 '21 at 07:51

score 0 · Answer 1 · answered Dec 10 '21 at 07:54

The answer was basically the first answer to this post: Should Hibernate be able to handle overlapping foreign keys?

Basically changing the mapping to be with the actual column as column and the one repeated in other entities as formula it is working now:

@JoinColumnsOrFormulas(value = { 
@JoinColumnOrFormula(column = @JoinColumn(name="state_id", referencedColumnName = "id")), 
@JoinColumnOrFormula(formula = @JoinFormula(value="site_id", referencedColumnName = "site_id")) })

HIbernate not inserting due to not-nullconstraint with composite FK

1 Answers1