20

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.

Tushar
  • 3,527
  • 9
  • 27
  • 49
gran_profaci
  • 8,087
  • 15
  • 66
  • 99

3 Answers3

37

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.

lc.
  • 113,939
  • 20
  • 158
  • 187
  • can we use this ? `insert into mytbl(str1, str2) values ('Example', ?)` and then `ps.setString(1,"String2")` or we have to use `ps.setString(2,"String2")` ? :) – DeepSidhu1313 Oct 17 '14 at 16:46
  • @DeepSidhu1313 You can of course try it, but it would be parameter 1 because it's the first parameter in the query. – lc. Oct 17 '14 at 17:01
  • Doesn't work for me. Get java.sql.BatchUpdateException: Field 'id' doesn't have a default value – user3217883 Sep 10 '20 at 17:30
2

You should perform some modification(define default statement) to prepared statement string like this:

"INSERT into employee VALUES(default,?,?)"

That modification is because of occurring this problem : Column count doesn't match value count at row 1 JAVA mysql

After that you're code is something like below:

PreparedStatement preparedStatement = 
connect.prepareStatement("INSERT into employee VALUES (default,?,?)", 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();

Thanks to Ic. for his answer.

smart-developer
  • 195
  • 1
  • 3
  • 14
0
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","password");

        PreparedStatement ps = con.prepareStatement("insert into imgslider(id,cmnt,date1,img,status) values(seq.nextval,?,?,?,?)");
        ResultSet rs = null;

        String s1 = "I’ve Come and I’m Gone: A Tribute to Istanbul’s Street";
        ps.setString(1,s1);

        Calendar calendar = Calendar.getInstance();
        java.sql.Date dd = new java.sql.Date(calendar.getTime().getTime());
        ps.setDate(2,dd);

        FileInputStream f1 = new FileInputStream("F:\\java\\slide-9.jpg");
        ps.setBinaryStream(3,f1,f1.available());

        ps.setInt(4,0);

        int i = ps.executeUpdate();
        System.out.println(i+" rows affected");

        con.close();
    }

here is my code for auto-increment column in table by PreparedStatement.

Navit Antil
  • 1