Skip a column on INSERT with JPA

Question

I have a JPA/EclipseLink model with multiple parameters whose values are set as defaults by the PostgreSQL database. Specifically, I have:

An id column of type SERIAL which auto-increments when new rows are added
A created_at column of type TIMESTAMP which defaults to now()

My model looks like this:

import javax.persistence.*;

@Entity
@Table(name="entity")
@NamedQuery(name="Entity.findAll", query="SELECT e from Entity e")
public class Entity {
    @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;

    @Column(name="created_at")
    private java.sql.Timestamp createdAt;

    // constructor, getters, and setters
}

When I try to insert a row with persist() in javax.persistence.EntityManager, the insertion fails because a NULL value is being set for the created_at column. Rather than inserting NULL, I want to simply not insert anything into that column and allow PostgreSQL to set it to now(). Essentially, I would like to use @GeneratedValue on createdAt, but that annotation is only for primary keys and cannot be used on multiple attributes. Is there another annotation I can use that will accomplish this?

Maybe this is what you are looking for: http://stackoverflow.com/questions/8202154/how-to-create-an-auto-generated-date-timestamp-field-in-a-play-jpa — Spasoje Petronijević, Mar 22 '17 at 01:08

score 11 · Accepted Answer · answered Mar 22 '17 at 02:13

You may want insertable=false in the Column annotation:

@Column(name="created_at", insertable=false)
private java.sql.Timestamp createdAt;

From: http://docs.oracle.com/javaee/5/api/javax/persistence/Column.html

boolean insertable (Optional) Whether the column is included in SQL INSERT statements generated by the persistence provide

score 3 · Answer 2 · answered Apr 29 '20 at 06:33

You can add @DynamicInsert on your entity class. The insert statement will include null fields when without this annotation. Also, you can add @ DynamicUpdate when executing the update statement.

@Entity
@DynamicInsert
@DynamicUpdate
@Table(name="entity")
@NamedQuery(name="Entity.findAll", query="SELECT e from Entity e")
public class Entity {
    @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;

    @Column(name="created_at")
    private java.sql.Timestamp createdAt;

    // constructor, getters, and setters
}

score 1 · Answer 3 · answered Mar 22 '17 at 14:47

As mentioned in another answer, JPA providers will set the value on insert if you have it marked as insertable=true (the default). This occurs with many providers even if null as it allows statement reuse.

Just excluding it from the statement though might not be what you want, as to have the value in your entity (and in the cache) will require refreshing. EclipseLink though has @ReturningInsert which allows EclipseLink to update the entity with the value in the database. Unfortunately support is only for Oracle - other databases require use of stored procedures to return run the insert and return the value.

Skip a column on INSERT with JPA

3 Answers3