Specify Oracle Sequence in SimpleJdbcInsert object to generate key from Oracle Sequence

Question

I am using SimpleJdbcInsert as,

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource).withTableName(TABLE_NAME).withSchemaName(SCHEMA_NAME);

Map<String, Object> namedParameterMap = new HashMap<String, Object>();
namedParameterMap.put("ID", "MYSEQ.NEXTVAL");
namedParameterMap.put("COLUMN1NAME",obj1.getColumn1Value());
namedParameterMap.put("COLUMN2NAME", obj1.getColumn2Value());

simpleJdbcInsert.withoutTableColumnMetaDataAccess().execute(namedParameters);

But it throws error for ID column only , other columns work fine which I confirmed by commenting out the ID column.

java.lang.NumberFormatException: null

I checked my Oracle Sequence which is working correctly fine. I checked many blogs but could not find a proper blog which is using Oracle sequence with SimpleJdbcInsert.

knutesten · Accepted Answer · 2020-03-01 12:39:26Z

2

It is because jdbcTemplate tries to parse "MYSEQ.NEXTVAL" into a long, which is the datatype of ID. You can't send SQL statements as named parameters.

edited Mar 1, 2020 at 12:39

answered Sep 22, 2016 at 12:38

knutesten

5963 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shaikh Aejaz · Accepted Answer · 2021-07-01 04:19:06Z

we cannot use Oracle sequence in SimpleJdbcInsert directly. to do so we have to used triggers and insert sequence value dynamically.

my table name is

PRODUCT(ID,NAME,QTY,PRICE)

create a database sequence (in my case its PROD_ID)
create a Database Trigger for getting PK value

CREATE OR REPLACE TRIGGER PROD_PK_GEN_TRIGGER
BEFORE INSERT ON PRODUCT
FOR EACH ROW
BEGIN
SELECT PROD_SEQ.NEXTVAL
INTO :new.ID
FROM DUAL;
END;

use id as autogenerated key in java code

SimpleJdbcInsert simpleJdbcInsert = 
     new SimpleJdbcInsert(dataSource)
          .withTableName("PRODUCT")
          .usingGeneratedKeyColumns("ID");

call executeAndReturnKey() method to execute and get the auto-generated key

      int  id  =  
         simpleJdbcInsert .executeAndReturnKey(
             new BeanPropertySqlParameterSource(product))
             .intValue();

Note :: here by default executeAndReturnKey() method will return java.lang.Number type and to get pk in int type we must use intValue() method.

Collectives™ on Stack Overflow

Specify Oracle Sequence in SimpleJdbcInsert object to generate key from Oracle Sequence

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related