Spring MVC getJdbcTemplate insert in Oracle return column value

Question

Spring MVC getJdbcTemplate insert in Oracle return column value

Navigation

#1 by (1 votes)

5

I have a project in Spring MVC, I'm using getJdbcTemplate to do insert's.

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

For example:

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

And I'm using getJdbcTemplate to insert from Spring, but I need the value of the java-side SEQ to insert into the following table with the value in foreign key.

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 ideas how to solve this problem?

sql java jdbc spring-mvc

asked by anonymous 21.02.2016 / 13:15

1 answer

How to save a base64 image in SQL and save it locally as PNG List custom post type of several taxonomies

score 1 · Accepted Answer

Using the JdbcTemplate class you should do this:

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 get during an insertion is to use 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.