Avoid insert 'null' values to database table via JPA

Question

im starting with JPA2 and feel quite comfortbale so far. But I have a problem when persisting Entities with null property values for NON NULL database fields with default value.

I would like to be able to leave the entity property null and let the database insert the default value.

My current setup is openJPA with PostgreSQL.

I have this VERSION database table (Vorgabewert = Default value):


     Spalte     |             Typ             |         Attribute
----------------+-----------------------------+----------------------------
 status_        | smallint                    | not null Vorgabewert 0
 time_          | timestamp without time zone | not null
 system_time    | timestamp without time zone | not null Vorgabewert now()
 version        | character varying(20)       | not null
 activationtime | timestamp without time zone |
 importtime     | timestamp without time zone |

I have an entity (Java DTO) which maps the database fields (except 'status') by xml configuration.

I hoped I could insert an entity without the system_time set and expected that the database will fill the current time as default value.

JPA constructs the following SQL-Query:

INSERT INTO public.version (version, activationtime, importtime, system_time, time_) VALUES (?, ?, ?, ?, ?) [params=?, ?, ?, ?, ?]

and Postgres reacts with:

FEHLER: NULL-Wert in Spalte »system_time« verletzt Not-Null-Constraint (sorry for German language but this message means the Not-Null-Constraint violation on 'system_time').

So what can I do? Is this a JPA or Database Problem. Can I configure JPA to exclude null properties from the INSERT SQL Statement.

I want to have the ability to set the 'system_time' in my entity or to let it be 'null' and let the database put the default value.

Any help is welcome!

Regads Klaus

score 29 · Answer 1 · answered Dec 23 '10 at 10:58

29

Using the annotation

@Column(insertable = false)

will prevent the value being generated in the sql.

answered Dec 23 '10 at 10:58

OrangeDog

36,653
12
122
207

4

+1 for the tip - but this also prevents the value being generated if NOT null. In this case I would like to have the value in the SQL statement. Seems to be no way to achieve this behaviour.. – FrVaBe Dec 23 '10 at 11:12
3

Ah, I see. There is no way to do this. You have to decide whether the Java or the DBMS is going to be responsible for times, you can't have a mixture of both. – OrangeDog Dec 23 '10 at 11:15
Could you have two entities mapped to the same table, one of which defined the column as `insertable=false`, and the other didn't, the control autogeneration by using one or the other? I ask this only out of theoretical interest - this would obviously be a fundamentally insane thing to do. – Tom Anderson Dec 23 '10 at 21:00
I would hope Hibernate has sanity checks for that sort of thing, though I've never tried it. Perhaps if you used two entirely separate Hibernate instances at the same time... – OrangeDog Dec 23 '10 at 21:29
1

There's also `updatable = false` which removes the column from your update statements. Not what the OP was asking about but related – xbakesx Feb 21 '17 at 21:10

score 27 · Accepted Answer · answered Dec 23 '10 at 09:40

27

I would not rely on default values in the database in conjunction with JPA. You would have to read the entity back after the insert otherwise you have a mismatch between the entity state and the db state.

Choose the pragmatic approach here and initialise all values in java. Never heard of a way to tell JPA/Hibernate to leave out null values in an insert/update.

answered Dec 23 '10 at 09:40

bert

7,566
3
31
47

2

Good point - I hoped persisting the object and bringing it to the 'managed' state would include a kind of read back operation. – FrVaBe Dec 23 '10 at 11:17
2

Accepted this answer because of the important "state mismatch" note. The answer also points out, that there is no way to do the thing I would linke to do :-(. – FrVaBe Jan 03 '11 at 08:00

score 9 · Answer 3 · edited Apr 15 '13 at 21:46

9

In the Annotation @Column put the atribute insertable to false like this:

`@Column(name="system_time", insertable = false)`

Or if you need check when the value is NULL then make a Trigger BEFORE INSERT on the table.

edited Apr 15 '13 at 21:46

answered Apr 15 '13 at 21:30

Jorge Mario Samayoa

111
1
2

score 2 · Answer 4 · edited Jul 18 '19 at 07:41

2

From documentation : columnDefinition : The SQL fragment that is used when generating the DDL for the column.

By using columnDefinition, you can specify constraints as required.

   @Column(name="COLUMN_NAME", 
   columnDefinition="DATE DEFAULT CURRENT_DATE",table="TABLE_NAME")

Else you can try to initialize field in entity itself to get rid of this.

    @Column(name = "somedate", nullable = false)
    private Date someDate = new Date();

So by default current date will be inserted if you do not set it.

edited Jul 18 '19 at 07:41

Piszu

443
1
8
24

answered Dec 22 '10 at 12:20

Nayan Wadekar

11,444
4
50
73

2

As you quoted this definition is only used when generating the DDL for the column (for table creation). Thus it has no effect for null values in SQL Statements when running the application (I still get the violation when trying to insert a NULL in this column). This happends also with plain SQL so I am looking for a configuration way in JPA to exclude columns from generated (INSERT) SQL Statements when they are null. – FrVaBe Dec 22 '10 at 12:45
A default value will still not help because that is only used when the column is not explicitely listed in the INSERT INTO clause – Dec 22 '10 at 13:57
Initializing a default value will definitly help - but in the case some process sets the value back to NULL the insert will fail. – FrVaBe Dec 23 '10 at 11:14
you can configure that in setter-method in entity, setting value only if it is not null. – Nayan Wadekar Dec 23 '10 at 11:22
Is it possible to specify constraint name in column definition or some other way. if yes please specify. Thanks – rahul Feb 04 '16 at 10:53

score 1 · Answer 5 · answered Aug 30 '18 at 11:08

1

I found out a simple solution: define a default value in the pojo.

@Column(name="system_time")
private Date systemTime = new Date();

answered Aug 30 '18 at 11:08

xeon_pan

51
4

score 1 · Answer 6 · answered Oct 02 '19 at 21:08

1

To exclude null property values in the hibernate insert sql stmt use attribute dynamic-insert=true.

1.@org.hibernate.annotations.Entity(dynamicInsert = true) add this on the class 2.In xml mapping , use dynamic-insert=true attribute in class tag.

answered Oct 02 '19 at 21:08

kranti

89
1
6

score 1 · Answer 7 · answered Jan 10 '21 at 05:28

For those who use Spring Framework with JPA please have an look at section 4 of this article for an elegant solution. ALL CREDITS TO BAELDUNG

https://www.baeldung.com/database-auditing-jpa

NB: Excerpt from article is pasted to preserve the content

Spring Data JPA is a framework that extends JPA by adding an extra layer of abstraction on the top of the JPA provider. This layer allows for support for creating JPA repositories by extending Spring JPA repository interfaces.

For our purposes, you can extend CrudRepository<T, ID extends Serializable>, the interface for generic CRUD operations. As soon as you've created and injected your repository to another component, Spring Data will provide the implementation automatically and you're ready to add auditing functionality.

4.1. Enabling JPA Auditing To start, we want to enable auditing via annotation configuration. In order to do that, just add @EnableJpaAuditing on your @Configuration class:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories
@EnableJpaAuditing
public class PersistenceConfig { ... }

4.2. Adding Spring's Entity Callback Listener As we already know, JPA provides the @EntityListeners annotation to specify callback listener classes. Spring Data provides its own JPA entity listener class: AuditingEntityListener. So let's specify the listener for the Bar entity:

@Entity
@EntityListeners(AuditingEntityListener.class)
public class Bar { ... }

Now auditing information will be captured by the listener on persisting and updating the Bar entity.

4.3. Tracking Created and Last Modified Dates Next, we will add two new properties for storing the created and last modified dates to our Bar entity. The properties are annotated by the @CreatedDate and @LastModifiedDate annotations accordingly, and their values are set automatically:

@Entity
@EntityListeners(AuditingEntityListener.class)
public class Bar {
    
    //...
    
    @Column(name = "created_date", nullable = false, updatable = false)
    @CreatedDate
    private long createdDate;

    @Column(name = "modified_date")
    @LastModifiedDate
    private long modifiedDate;
    
    //...
    
}

Generally, you would move the properties to a base class (annotated by @MappedSuperClass) which would be extended by all your audited entities. In our example, we add them directly to Bar for the sake of simplicity.

4.4. Auditing the Author of Changes With Spring Security If your app uses Spring Security, you can not only track when changes were made but also who made them:

@Entity
@EntityListeners(AuditingEntityListener.class)
public class Bar {
    
    //...
    
    @Column(name = "created_by")
    @CreatedBy
    private String createdBy;

    @Column(name = "modified_by")
    @LastModifiedBy
    private String modifiedBy;
    
    //...
    
}

The columns annotated with @CreatedBy and @LastModifiedBy are populated with the name of the principal that created or last modified the entity. The information is pulled from SecurityContext‘s Authentication instance. If you want to customize values that are set to the annotated fields, you can implement AuditorAware interface:

public class AuditorAwareImpl implements AuditorAware<String> {
 
    @Override
    public String getCurrentAuditor() {
        // your custom logic
    }

}

In order to configure the app to use AuditorAwareImpl to look up the current principal, declare a bean of AuditorAware type initialized with an instance of AuditorAwareImpl and specify the bean's name as the auditorAwareRef parameter's value in @EnableJpaAuditing:

@EnableJpaAuditing(auditorAwareRef="auditorProvider")
public class PersistenceConfig {
    
    //...
    
    @Bean
    AuditorAware<String> auditorProvider() {
        return new AuditorAwareImpl();
    }
    
    //...
    
}

score 0 · Answer 8 · answered Feb 16 '17 at 20:13

I use this (against an Oracle database):

@Temporal(TemporalType.TIMESTAMP)
@Column(name="CREATION_TIME", 
        columnDefinition="TIMESTAMP DEFAULT SYSTIMESTAMP",
        nullable=false,
        insertable=false,
        updatable=false)
private Date creationTime;

score 0 · Answer 9 · answered Jan 21 '18 at 15:49

0

you can avoid the null property by add nullable=false to the @column annotation. like this

@Column(name = "muColumn", nullable = false)

how to avoid the null property in insert or update

answered Jan 21 '18 at 15:49

user9247837

1

score -1 · Answer 10 · answered Dec 23 '10 at 22:09

I don't know of any way to do this in JPA.

But you could add a trigger to the database which catches attempts to insert NULL into the column in question, and rewrites the insert to insert the default? Essentially, you move the default-generation behaviour entirely down into the database, rather than splitting it between JPA and the database.

However, as bert points out in his answer, this would leave the object and the database inconsistent, which is almost certainly a bad think.

score -1 · Answer 11 · edited Nov 16 '12 at 22:49

-1

you can use this way:

@JoinColumn(name = "team_id", nullable = false)

Using this way, JPA will check NULL value.

edited Nov 16 '12 at 22:49

vgoff

10,980
3
38
56

answered Jul 17 '11 at 16:16

sushil

1

Avoid insert 'null' values to database table via JPA

11 Answers11

Linked