How can I get the autoincremented id when I insert a record in a table via jdbctemplate

Question

private void insertIntoMyTable (Myclass m) {
    String query = "INSERT INTO MYTABLE (NAME) VALUES (?)";
    jdbcTemplate.update(query, m.getName());
}

When the above query inserts a record, the ID column in the table autoincrements.

Is there a way to get this auto incremented ID back at the time of the insertion. So in this example the return value of my method would be int

I think you may need a select just after the insert in a transaction. — PbxMan, Oct 14 '12 at 13:56

score 22 · Accepted Answer · edited Mar 09 '23 at 14:53

Check the Chapter 11. Data access using JDBC reference. You can use jdbcTemplate.update as:

EDIT Added imports as asked

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

following is the code usage:

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"});
            ps.setString(1, name);
            return ps;
        }
    },
    keyHolder);
// keyHolder.getKey() now contains the generated key

We also now need to pass the Statement.RETURN_GENERATED_KEYS as another argument to prepareStatement — Amritansh Singhal, Oct 07 '19 at 13:33

score 2 · Answer 2 · answered May 04 '15 at 13:00

I get id generated by database (MSSQL) after insert like below, imports:

  import org.springframework.jdbc.core.BeanPropertyRowMapper;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.jdbc.core.RowMapper;
  import org.springframework.jdbc.core.SqlParameter;
  import org.springframework.jdbc.core.SqlReturnResultSet;
  import org.springframework.jdbc.core.simple.SimpleJdbcCall;

and the code snippet:

    final String INSERT_SQL = "INSERT INTO [table]\n"
            + " ([column_1]\n"
            + " ,[column_2])\n"
            + " VALUES\n" +
            " (?, ?)";

    Connection connection = jdbcTemplate.getDataSource().getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement(INSERT_INVOICE_SQL, Statement.RETURN_GENERATED_KEYS);
    preparedStatement.setString(1, "test 1");
    preparedStatement.setString(2, "test 2");

    preparedStatement.executeUpdate();
    ResultSet keys = preparedStatement.getGeneratedKeys();

    if (keys.next()) {
        Integer generatedId = keys.getInt(1); //id returned after insert execution
    }

Don't forget the line with `Statement.RETURN_GENERATED_KEYS`! — Maicon Mauricio, Aug 15 '22 at 19:54

score 1 · Answer 3 · answered May 15 '18 at 14:31

JdbcTemplate is at the core of Spring. Another option is to use SimpleJdbcInsert.

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
simpleJdbcInsert
    .withTableName("TABLENAME")
    .usingGeneratedKeyColumns("ID");
SqlParameterSource params = new MapSqlParameterSource()
    .addValue("COL1", model.getCol1())
    .addValue("COL2", model.getCol2());
Number number = simpleJdbcInsert.executeAndReturnKey(params);

You can still @Autowire jdbcTemplate. To me, this is more convenient than working with jdbcTemplate.update() method and KeyHolder to get the actual id.

Example code snippet is tested with Apache Derby and should work with the usual databases.

Use of Spring JPA is another option - if ORM is for you.

score 1 · Answer 4 · edited Feb 02 '21 at 09:26

@Component
public class PersonDao {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public PersonDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<Person> index() {
        return jdbcTemplate.query("SELECT * FROM person", new BeanPropertyRowMapper<>(Person.class));
    }

    public Person show(int id){
        return jdbcTemplate.query("SELECT * FROM person WHERE id=?", new Object[]{id}, new BeanPropertyRowMapper<>(Person.class))
                .stream().findAny().orElse(null);
    }

    public void save(Person person){
        jdbcTemplate.update("INSERT INTO person (name, age, email) VALUES (?, ?, ?)", person.getName(), person.getAge(), person.getEmail());
    }

    public void edit(Integer id, Person person) {
        jdbcTemplate.update("UPDATE person SET name = ?, age = ?, email = ? WHERE id = ?", person.getName(), person.getAge(), person.getEmail(), id);
    }

    public void delete(Integer id) {
        jdbcTemplate.update("DELETE FROM person WHERE id = ?", id);
    }
}

How can I get the autoincremented id when I insert a record in a table via jdbctemplate

4 Answers4

Linked

Related