How to Get the Autoincremented Id When I Insert a Record in a Table Via Jdbctemplate

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

Check this 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

identity from sql insert via jdbctemplate

The JDBCTemplate.update method is overloaded to take an object called a GeneratedKeyHolder which you can use to retrieve the autogenerated key. For example (code taken from here):

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

How to get generated ID after inserting new record in database table using Spring JDBCTemplate?

When you use con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS), the Oracle JDBC driver will not return the value of the id column, but instead it will return the ROW_ID (a pseudo column that identifies a specific row), to allow you to retrieve the value yourself.

Historically the Oracle driver did it this way, because previous Oracle versions didn't have identity columns.

Instead you should use:

con.prepareStatement(query, new String[] { "ID" });

Which instructs Oracle to return the value of the specified column.

Insert using PreparedStatement. How do I auto-increment the ID?

Leave the column out of the INSERT statement entirely. It will be generated by the database engine. Your query should be:

INSERT INTO employee (time, name)
VALUES (?, ?)

Secondly, you have to perform the insert first, then get the keys out of the result.

I believe your code should be:

PreparedStatement preparedStatement = 
    connect.prepareStatement("INSERT into employee (time, name) VALUES (?,?)", 
    Statement.RETURN_GENERATED_KEYS);

preparedStatement.setTimestamp(1, 
    new java.sql.Timestamp(new java.util.Date().getTime()));                           
preparedStatement.setString(2, "Test");

preparedStatement.executeUpdate();

ResultSet tableKeys = preparedStatement.getGeneratedKeys();
tableKeys.next();
int autoGeneratedID = tableKeys.getInt(1);

Note this example does not check the success of the executed statement or the existence of returned keys.

How to get the insert ID in JDBC?

If it is an auto generated key, then you can use Statement#getGeneratedKeys() for this. You need to call it on the same Statement as the one being used for the INSERT. You first need to create the statement using Statement.RETURN_GENERATED_KEYS to notify the JDBC driver to return the keys.

Here's a basic example:

public void create(User user) throws SQLException {
    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
                                      Statement.RETURN_GENERATED_KEYS);
    ) {
        statement.setString(1, user.getName());
        statement.setString(2, user.getPassword());
        statement.setString(3, user.getEmail());
        // ...

        int affectedRows = statement.executeUpdate();

        if (affectedRows == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        }

        try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                user.setId(generatedKeys.getLong(1));
            }
            else {
                throw new SQLException("Creating user failed, no ID obtained.");
            }
        }
    }
}

Note that you're dependent on the JDBC driver as to whether it works. Currently, most of the last versions will work, but if I am correct, Oracle JDBC driver is still somewhat troublesome with this. MySQL and DB2 already supported it for ages. PostgreSQL started to support it not long ago. I can't comment about MSSQL as I've never used it.

For Oracle, you can invoke a CallableStatement with a RETURNING clause or a SELECT CURRVAL(sequencename) (or whatever DB-specific syntax to do so) directly after the INSERT in the same transaction to obtain the last generated key. See also this answer.

MySQL auto_increment 10 instead of 1 when using spring jdbc

I believe Azure is using the same ClearDB as Heroku. So the this answer here is the best explanation Heroku MySQL Auto Increment

In short, it's a ClearDB strategy to prevent collision http://www.cleardb.com/developers/help/faq#general_16

Spring JdbcTemplate - Insert blob and return generated key

I came here looking for the same answer, but wasn't satisfied with what was accepted. So I did a little digging around and came up with this solution that I've tested in Oracle 10g and Spring 3.0

public Long save(final byte[] blob) {
  KeyHolder keyHolder = new GeneratedKeyHolder();
  String sql = "insert into blobtest (myblob) values (?)"; //requires auto increment column based on triggers
  getSimpleJdbcTemplate().getJdbcOperations().update(new AbstractLobPreparedStatementCreator(lobHandler, sql, "ID") {
    @Override
    protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException {
      lobCreator.setBlobAsBytes(ps, 1, blob);
    }
  }, keyHolder);

  Long newId = keyHolder.getKey().longValue();
  return newId;
}

this also requires the following abstract class, based in part on Spring's AbstractLobCreatingPreparedStatementCallback

public abstract class AbstractLobPreparedStatementCreator implements PreparedStatementCreator {
  private final LobHandler lobHandler;
  private final String sql;
  private final String keyColumn;
  public AbstractLobPreparedStatementCreator(LobHandler lobHandler, String sql, String keyColumn) {
    this.lobHandler = lobHandler;
    this.sql = sql;
    this.keyColumn = keyColumn;
  }
  public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
    PreparedStatement ps = con.prepareStatement(sql, new String[] { keyColumn });
    LobCreator lobCreator = this.lobHandler.getLobCreator();
    setValues(ps, lobCreator);
    return ps;
  }
  protected abstract void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException;
}

Also, the table you create in Oracle should have an auto-incremented column for the id using a sequence and trigger. The trigger is necessary because otherwise you'd have to use Spring's NamedParameterJdbcOperations (to do the sequence.nextval in your SQL) which doesn't seem to have support for KeyHolder (which I use to retrieve the auto-gen id). See this blog post (not my blog) for more info: http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/

create table blobtest (
id number primary key,
myblob blob);

create sequence blobseq start with 1 increment by 1;

CREATE OR REPLACE TRIGGER blob_trigger
BEFORE INSERT
ON blobtest
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT blobseq.nextval INTO :NEW.ID FROM dual;
end;
/