Fire Sequence.nextval query using jdbctemplate in spring

Question

Database : Oracle

I have table in which there are 10 columns and i want sequence next value when insert row and also use that sequence number which inserted.

Now i have searched and find that KeyHolder of spring is useful but restrict for only less than 8 field so i can't use that.

How can i fire "select MySequence.nextval from dual" query and get sequence using jdbctemplate(NamedParameterJDBCTemplate) ?

Is other way to achieve for get inserted sequence value ?.

Answer 1

Using a jdbctemplate you can just mention the sequence generator as a value, e.g.

jdbcTemplate.update("INSERT INTO TABLE (id, data) VALUES (MySequence.nextval, ?)", new Object[] { data });

A note regarding the sequence generation: for versions before Oracle 12c you should have a trigger which will increment the sequence for you. From 12c you can use the auto-increment feature.

Answer 2

You can achieve this by using JdbcTemplate like this :

final SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(NEXT_VALUE_QUERY);
  sqlRowSet.next();// mandatory to move the cursor 
  sqlRowSet.getLong(1);// the value of the nextval