CODEWITHSUNDEEP
javaspring-bootsequenceh2
Praveen kumar
Praveen kumar

Reputation: 21

H2 database insert null to primary key

I have created the table in the H2 database as follows:

CREATE OR REPLACE SEQUENCE IF NOT EXISTS SCMSA_HIST.KEY_GEN_SEQ
    START WITH 0
   INCREMENT BY 1
    NOCYCLE
    NOCACHE;

CREATE TABLE IF NOT EXISTS SCMSA_HIST.SCMSA_POS_TRANS_ROLLUP
(
 POS_TRANS_ID INTEGER DEFAULT 
    (NEXT VALUE FOR SCMSA_HIST.KEY_GEN_SEQ) 
    NOT NULL IDENTITY ,
 JOB_LOG_ID INTEGER,
 DEALER_CODE VARCHAR(255),
 STORE_ID VARCHAR(255),
 TRANSACTION_DT TIMESTAMP,
 QUANTITY INTEGER,
 ROLLUP_TYPE VARCHAR(255),
 CREATE_DT TIMESTAMP,
 MAX_TRANSACTION_DT TIMESTAMP,
 PROCESSED_FLAG VARCHAR(255),
 CREATE_MONTH INTEGER,
 CREATE_YEAR INTEGER
);

The Model class for the above table is as follows:

@Entity
@Table(schema = "SCMSA_HIST", name = "SCMSA_POS_TRANS_ROLLUP")
public class ScmsaPosTransRollup {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "SEQ")
    @SequenceGenerator(name = "SEQ", sequenceName = "SCMSA_HIST.KEY_GEN_SEQ")
    @Column(name = "POS_TRANS_ID")
    private Long posTransId;

    @Column(name = "JOB_LOG_ID")
    private Long jobLogId;

    @Column(name = "DEALER_CODE")
    private String dealerCode;

    @Column(name = "STORE_ID")
    private String storeId;

    @Column(name = "TRANSACTION_DT")
    private Timestamp transactionDate;

    @Column(name = "ROLLUP_TYPE")
    private String rollupType;

    @Column(name = "QUANTITY")
    private Integer quantity;

    @Column(name = "CREATE_DT")
    private Timestamp createDate;

    @Column(name = "MAX_TRANSACTION_DT")
    private Timestamp maxTransactionDate;

    @Column(name = "PROCESSED_FLAG")
    private String processedFlag;

    @Column(name = "CREATE_MONTH", insertable = false, updatable = false)
    private Integer createMonth;

    @Column(name = "CREATE_YEAR", insertable = false, updatable = false)
    private Integer createYear;


    public ScmsaPosTransRollup() {
    }
//getter and setter
}

But, when I am trying to insert , the value for the "POS_TRANS_ID" is inserted as null. Can anyone please suggest me what am doing wrong here.

Upvotes: 2

Views: 1641

Answers (1)

Danila Zharenkov
Danila Zharenkov

Reputation: 1863

I tried to reproduce your problem. What helps to me: 1. Scripts

CREATE OR REPLACE SEQUENCE IF NOT EXISTS KEY_GEN_SEQ
   MINVALUE 1 
   MAXVALUE 999999999999999999
   START WITH 1
   INCREMENT BY 500
NOCYCLE
NOCACHE;

In create table DDL I replaced ID creation

 POS_TRANS_ID INTEGER DEFAULT  KEY_GEN_SEQ.NEXTVAL 
    NOT NULL IDENTITY ,

In Java class added initialValue and allocationSize to @SequenceGenerator, and changed strategy to SEQUENCE

@GeneratedValue(strategy=SEQUENCE, generator="SEQ")
@SequenceGenerator(name = "SEQ", sequenceName = "KEY_GEN_SEQ",initialValue = 1,allocationSize = 500)

And don't forget to define dialect in Hibernate props

hibernate.dialect=org.hibernate.dialect.H2Dialect

Upvotes: 1

Related Questions