Spring MVC getJdbcTemplate Insert in Oracle return column value

Question

I have a project in Spring MVC, I’m using the getJdbcTemplate to make Insert’s.

Only I do not insert the key Primary in Oracle from a sequence and need that value to re-enter another table where this value is foreign key.

For example:

insert into tableS(SEQ, dateX, ab, flag) values(SEQ_table.nextval, ?, ?, 'N')

And I’m wearing the getJdbcTemplate to insert from Spring, only I need the java-side SEQ value to insert into the following table with the foreign key value.

getJdbcTemplate().batchUpdate(INSERT_TABLE, new BatchPreparedStatementSetter() {

  public void setValues(PreparedStatement ps, int i) throws SQLException {
                String valor= info.getvalues().get(i);
                ps.setString(1, valor.geta());
                ps.setLong(2, valor.getb());
            }

            public int getBatchSize() {
                return  info.getvalues().size();
            }
});

Any idea how to solve this problem?

Answer 1

Using the Jdbctemplate class you should do so:

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";

KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps =
                connection.prepareStatement(INSERT_SQL, new String[] {"id"}); // Esse informa ao Driver JDBC as chaves que serão geradas
            ps.setString(1, name);
            return ps;
        }
    },
    keyHolder);

// keyHolder.getKey() - Aqui você obtem a chave ("id") gerada

Another way to obtain during an insertion is by using the class: Simplejdbcinsert

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor =
                new SimpleJdbcInsert(dataSource)
                        .withTableName("t_actor")
                        .usingColumns("first_name", "last_name")
                        .usingGeneratedKeyColumns("id"); // Informa quais a chaves serão geradas.
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue()); // Obtem a chave gerada.
    }
}

You do not have an option to do this in batch as in your example.