HIbernate Column with @ColumnDefault cannot be null

Question

My application uses hibernate with Spring Boot.

I have created a table like this:

public class TopicSubscriberMap implements Serializable
{   
    @ColumnDefault(value="'pending'")
    @Column(nullable=false)
    private String status;
    ...
}

The table is getting created as follows in mysql:

mysql> desc topic_subscriber_map;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| status        | varchar(255) | NO   |     | pending |       |
+---------------+--------------+------+-----+---------+-------+

On saving the table like this:

TopicSubscriberMap tsm = new TopicSubscriberMap();
tsm = mapRepository.save(tsm);

I am getting an error:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'status' cannot be null

If I make the field nullable, then although the table is getting saved, but in the db,

+--------+
| status | 
+--------+
| NULL   | 
+--------+

Why is the @ColumnDefault not working? Where is my mistake here?

Edit: when i remove the @ColumnDefault and declare the column as

private String status = "pending"

then although the default value does work, but the table schema shows:

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| status        | varchar(255) | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

How can I make the default value work and also show it in the table schema?

[set default value in hibernate](https://stackoverflow.com/questions/3110266/how-to-set-default-value-in-hibernate) This link will be helpful to solve your Issue — Ravat Tailor, Apr 02 '18 at 11:42

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

So you're using 2 different approaches of setting defaults here: via DB and via Java. Both the approaches don't coordinate with each other and one remains unaffected if other is configured. I'll explain.

The 1st method you used with @ColumnDefault is the DB approach. The same behaviour can be achieved using the columnDefinition attribute of @Column. Here, JPA simply adds this value in the field's column definition in DB. However, Hibernate does not account for DB definitions while preparing queries and transcribes the null value from Java object to its prepared SQL Query, resulting in MySQLIntegrityConstraintViolationException on query execution. More on this here: Hibernate Guide

The 2nd method you used relies on Java Initialisation. Your entity object got the default value when you called new TopicSubscriberMap();. But your DB does not know or care about it as its column definition was not impacted.

Solution

If you want to set defaults on the DB and be able to use them in the application as well, you can use Hibernate's @DynamicInsert annotation

@DynamicInsert
public class TopicSubscriberMap implements Serializable
{   
    @ColumnDefault(value = "pending")
    // alternately @Column(columnDefinition = "VARCHAR(255) DEFAULT 'pending'")
    private String status;
    ...
}

This will solve both of your problems, although it comes with its own set of performance issues in certain cases, talked about in this thread: Why does Hibernate set dynamic insert=false by default

score 0 · Answer 2 · answered Jul 28 '22 at 10:03

Instead of using @DynamicInsert annonation before entity, you can also use @Generated(GenerationTime.INSERT) before each field.

public class TopicSubscriberMap implements Serializable
{   
    @Generated(GenerationTime.INSERT) 
    @ColumnDefault(value = "pending")
    private String status;

    @Generated(GenerationTime.INSERT) 
    @ColumnDefault(value = "anotherDefaultValue")
    private String anotherField;
    ...
}

I don't know why, but case with @DynamicInsert don't work for me.

HIbernate Column with @ColumnDefault cannot be null

2 Answers2

Solution