DDL Default values are overridden by Spring JPA

Question

I have defined a table like this:

CREATE TABLE CONFIGSERVER.PROPERTIES (
    ID NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 1 CACHE 20 ) ,
    APPLICATION VARCHAR2 (4000) ,
    PROFILE     VARCHAR2 (4000) ,
    LABEL       VARCHAR2 (4000) ,
    PROP_KEY    VARCHAR2 (4000) NOT NULL,
    VALUE       VARCHAR2 (4000) NOT NULL,
    CREATED_ON  TIMESTAMP DEFAULT SYSDATE,
    CREATED_BY  VARCHAR2 (100) DEFAULT COALESCE(
        REGEXP_SUBSTR(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'),'^[^:]*'),
        SYS_CONTEXT('USERENV','SESSION_USER')),
    UPDATED_ON  TIMESTAMP ,
    UPDATED_BY  VARCHAR2 (100)
) LOGGING;

And if execute the following statement using SQLCl:

insert into properties (application, profile, label, prop_key, value) values ('nisse', 'bosse', 'bengt', 'olle', 'korv med mos');

The values for CREATED_ON and CREATED_BY are populated correctly by the DDL definition:

SQL> select * from properties where application = 'nisse';

   ID APPLICATION    PROFILE    LABEL    PROP_KEY    VALUE           CREATED_ON                         CREATED_BY      UPDATED_ON    UPDATED_BY    
_____ ______________ __________ ________ ___________ _______________ __________________________________ _______________ _____________ _____________ 
   25 nisse          bosse      bengt    olle        korv med mos    18-NOV-22 07.19.07.000000000 PM    CONFIGSERVER

However if I use Spring Data JPA the DEFAULT values are not populated for some reason?

My Properties class looks like this:

package com.example.configcrud.model;

import javax.persistence.*;
import java.sql.Timestamp;

@Entity
@Table(name = "properties")
public class Properties {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "application")
    private String application;

    @Column(name = "profile")
    private String profile;

    @Column(name = "label")
    private String label;

    @Column(name = "prop_key")
    private String propKey;

    @Column(name = "value")
    private String value;

    @Column(name = "created_on", updatable = false)
    private Timestamp createdOn;

    @Column(name = "created_by", updatable = false)
    private String createdBy;

    public Properties(String application, String profile, String label, String propKey, String value) {
         this.application = application;
         this.profile = profile;
         this.label = label;
         this.propKey = propKey;
         this.value = value;
    }

    public Properties() {
    }

    public String getApplication() {
        return application;
    }

    public void setApplication(String application) {
        this.application = application;
    }

    public String getProfile() {
        return profile;
    }

    public void setProfile(String profile) {
        this.profile = profile;
    }

    public String getLabel() {
        return label;
    }

    public void setLabel(String label) {
        this.label = label;
    }

    public String getPropKey() {
        return propKey;
    }

    public void setPropKey(String propKey) {
        this.propKey = propKey;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public Timestamp getCreatedOn() {
        return createdOn;
    }

    public Timestamp setCreatedOn() {
        return createdOn;
    }

    public String getCreatedBy() {
        return createdBy;
    }

    public String setCreatedBy() {
        return createdBy;
    }

    @Override
    public String toString() {
        return "Properties [id=" + id + ", " +
                "application=" + application + ", " +
                "profile=" + profile + ", " +
                "label=" + label +
                "propkey=" + propKey +
                "value=" + value +
                "CreatedOn=" + createdOn +
                "CreatedBy=" + createdBy + "]";
    }
}

I'm using Spring Data JPA:

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

Anyone got an idea on what I can do to make the DEFAULT values populated or not overwritten by JPA?

Thanks Andy

It is likely that JPA is creating the insert statement **including** the two fields on the insert command setting it to `null` which explain why they not being filled with the default. Try setting your application logs to show the sql command used to confirm this. See this thread, it may help you understand your problem better: https://stackoverflow.com/questions/4508622/avoid-insert-null-values-to-database-table-via-jpa — Jorge Campos, Nov 18 '22 at 19:42

score 0 · Accepted Answer · answered Nov 18 '22 at 19:54

By default, JPA inserts all columns explicitly. In order to have one your columns not inserted, but instead read from database at insert time, use the @Generated annotation like this:

@Generated(GenerationTime.INSERT)
@Column(name = "created_on", updatable = false, insertable = false)
private Timestamp createdOn;

DDL Default values are overridden by Spring JPA

1 Answers1