5

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.

SyParth
  • 211
  • 2
  • 7

2 Answers2

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.

knutesten
  • 594
  • 3
  • 16
0

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)

  1. create a database sequence (in my case its PROD_ID)
  2. 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;

  1. use id as autogenerated key in java code
SimpleJdbcInsert simpleJdbcInsert = 
     new SimpleJdbcInsert(dataSource)
          .withTableName("PRODUCT")
          .usingGeneratedKeyColumns("ID");

  1. 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.

Shaikh Aejaz
  • 1
  • 1