53

I have a column in DB with default value as sysdate. I'm looking for a way to get that default value inserted while I'm not giving anything to corresponding property on app side. By the way, I'm using annotation-based configuration.

Any advice?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
BruceCui
  • 731
  • 1
  • 6
  • 11

4 Answers4

118

The reason why the date column gets a null value when inserting, even though it is defined as default SYSDATE dbms-side, is that the default value for a column is only used if the column is not given a value in the query. That means it must not appear in the INSERT INTO sentence, even if it has been given null.

If you want to use the default SYSDATE on the DBMS side, you should configure the @Column with insertable=false in order to get the column out of your SQL INSERTs.

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "myDate", insertable=false)
private Date myDate;

Take into account that this approach will always ignore the value you provide to the property in your app when creating the entity. If you really want to be able to provide the date sometimes, maybe you should consider using a DB trigger to set the value instead of a default value.

There's an alternative to using the default SYSDATE definition of the DBMS. You could use the @PrePersist and @PreUpdate annotations to assign the current date to the property, prior to save/update, if and only if it has not been assigned yet: 

@PrePersist
protected void onCreate() {
    if (myDate == null) { myDate = new Date(); }
}

This closely related question provides different approaches: Creation timestamp and last update timestamp with Hibernate and MySQL.

Community
  • 1
  • 1
Xavi López
  • 27,550
  • 11
  • 97
  • 161
  • 1
    Thanks for the very detailed explanation Xavi. This is more than my expectation and it does work! – BruceCui Feb 05 '13 at 11:15
  • insertable=false didn't work for me. I'm using Postgres and I have a default value set on the column, but it's coming as null. – James Watkins Jan 31 '16 at 19:43
  • Check http://stackoverflow.com/questions/14703697/how-can-i-use-db-side-default-value-while-use-hibernate-save/43494148#43494148 for simplified answer – rajadilipkolli Apr 19 '17 at 11:15
  • @rajadilipkolli Keep in mind `@UpdateTimestamp` and `@CreationTimestamp` are Hibernate specific and won't be available with another JPA provider. Plus, it is already covered in [another answer](http://stackoverflow.com/a/39427923/851811) in the linked question. – Xavi López Apr 19 '17 at 11:58
  • In my case, I have a column `modified_at` and default value is current datetime. The only thing that worked for me was [@UpdateTimestamp](https://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/annotations/UpdateTimestamp.html). I did not need to call `.flush()`. Neither [@Generated](http://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/annotations/Generated.html) or [@GeneratedValue](http://docs.oracle.com/javaee/5/api/javax/persistence/GeneratedValue.html) annotations worked, even after using `.flush()` the field would still come back as null after a `.save()`. – Doug Oct 04 '17 at 13:24
  • 1
    @Doug great to know you sorted it out, don't see how `@Generated` would help here apart from [avoiding a backtrip to the DB to retrieve the value](https://stackoverflow.com/a/3224800/851811) once it was assigned DB side. Did you try `insertable=false` in the `@Column` definition? `@UpdateTimestamp` is also a very valid approach as mentioned in the comments above, as long as you keep using Hibernate. – Xavi López Oct 04 '17 at 14:52
  • Yes, I tried `insertable=false` as well and it did not work. The modified_at column was still coming back as null after a `.save()`, not sure why... – Doug Oct 04 '17 at 16:54
  • @Doug Well if it was effectively an `UPDATE` instead of an `INSERT` then `updatable=false` could have helped. Anyway `@UpdateTimestamp` is a neat solution already, I prefer defaulting in my own code not DBMS side if possible. – Xavi López Oct 04 '17 at 17:02
  • Why do you think it is better to default the value in the code and not in the DBMS? – Doug Oct 05 '17 at 08:21
  • 1
    @Doug Multiple reasons - considering default values most of the time obey to application logic (i.e. you can default a `creation_date` dbms-side but not a `creation_user`) I'd rather have them together in one place (whenever possible of course), specially one where I know I have control, and won't cause any side effects instead of scattered through different layers. – Xavi López Oct 05 '17 at 08:35
  • @XaviLópez I use `@CreatedDate private LocalDateTime created;` and it give me error `ERROR: column "date" is of type timestamp without time zone but expression is of type bytea` – ankit Jul 19 '18 at 12:09
21

If you are using Hibernate then you can just use @CreationTimestamp to insert default date.

@CreationTimestamp
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "create_date")
private Date createDate;

and @UpdateTimestamp to update the value if necessary

@UpdateTimestamp
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "modify_date")
private Date modifyDate;
rajadilipkolli
  • 3,475
  • 2
  • 26
  • 49
  • This did it for me - been looking for this answer for a while! Thank you, @rajadilipkolli :) – Custard May 12 '17 at 10:00
  • This is what worked for me, first answer did not work. Thanks @rajadilipkolli – Doug Oct 04 '17 at 13:25
  • This works but uses the system time, any way to use the db time? Like HQL's CURRENT_TIMESTAMP()? Edit: Without changing the db schema? – gagarwa Jun 25 '20 at 06:38
0

Or you can initialize the property of the POJO directly e.g:

//java code property declaration

private String surname = "default";
lloiacono
  • 4,714
  • 2
  • 30
  • 46
Sparticles
  • 608
  • 5
  • 7
0

Annotate your entity with @DynamicInsert then null values will be omitted and default values will be taken. Source: Hibernate 5.5 documentation

Falco Preiseni
  • 387
  • 4
  • 12