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

Question

I have a PreparedStatement such as:

 PreparedStatement preparedStatement = connect.prepareStatement("INSERT into employee (id, time, name" + "(?,?,?)",Statement.RETURN_GENERATED_KEYS);
 ResultSet tableKeys = preparedStatement.getGeneratedKeys();
 preparedStatement.executeUpdate();
 tableKeys.next();
 int autoGeneratedID = tableKeys.getInt(1);
 preparedStatement.setInt(1,autoGeneratedID);
 preparedStatement.setTimestamp(2, new java.sql.Timestamp(new java.util.Date().getTime()));                           
 preparedStatement.setString(3, "Test");
 preparedStatement.executeUpdate();

As you can see, the Employee table has an auto-incremented ID. I need to basically add it in automatically using preparedStatement as well. Can someone tell me where I am going wrong and correct me? Right now it just gives me an error related to Statement.

Answer 1

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.