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

Heterotrophic

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.

Alvardo

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

Sarsenet

Answer 3

Just in case that someone stumbled upon this old question.

Using GeneratedKeyHolder and PreparedStatementCreator there is no limit to the number of columns and you get the ID directly (without needing MySequence.nextval).

long createRecord(JdbcTemplate jdbcTemplate, String data1, String data2) {
    String sql = "INSERT INTO MyTable (ID, DATA1, DATA2) VALUES (MySequence.nextval, ?, ?)";

    KeyHolder keyHolder = new GeneratedKeyHolder();

    PreparedStatementCreator preparedStatementCreator = con -> {
        PreparedStatement ps = con.prepareStatement(sql, new String[] {"ID"});
        ps.setString(1, data1);
        ps.setString(2, data2);
        return ps;
    };

    jdbcTemplate.update(preparedStatementCreator, keyHolder);
    return keyHolder.getKey().longValue();
};

Loisloise