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?
Add a comment
|
5 Answers
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.
11 Comments
BruceCui
Thanks for the very detailed explanation Xavi. This is more than my expectation and it does work!
James Watkins
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.
rajadilipkolli
Check stackoverflow.com/questions/14703697/… for simplified answer
Xavi López
@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 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
@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. |
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;
3 Comments
Custard
This did it for me - been looking for this answer for a while! Thank you, @rajadilipkolli :)
Doug
This is what worked for me, first answer did not work. Thanks @rajadilipkolli
gagarwa
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?
Annotate your entity with @DynamicInsert then null values will be omitted and default values will be taken.
Source: Hibernate 5.5 documentation
Comments
Or you can initialize the property of the POJO directly e.g:
//java code property declaration
private String surname = "default";
Comments
Use these annotations for a column like created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP:
@Generated(value = GenerationTime.INSERT)
@Column(name = "created_at", nullable = false, insertable = false, updatable = false)
private Instant createdAt;
@Generated is what Hibernate uses to know that the value should be loaded back from the DB after a change, INSERT in this case. The @Column follows the reasoning of Xavi's answer, but more complete for the specific definition I've given.
For a column like updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, use:
@Generated(value = GenerationTime.ALWAYS)
@Column(name = "updated_at", nullable = false, insertable = false, updatable = false)
private Instant updatedAt;
ALWAYS covers both INSERT and UPDATE.
Comments
