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.

lc. · Accepted Answer · 2013-07-03 01:26:12Z

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.

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 You can of course try it, but it would be parameter 1 because it's the first parameter in the query.
Doesn't work for me. Get java.sql.BatchUpdateException: Field 'id' doesn't have a default value

smart-developer · Accepted Answer · 2019-03-09 14:34:34Z

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.

Navit Antil · Accepted Answer · 2018-05-31 07:42:16Z

 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.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Linked

Related