Insert row into database with PreparedStatement

Question

I want to insert a row into a table using PreparedStatement. The table got 3 columns(int, String, String). The thing is that the int column is AUTO_INCREMENT, so I want to leave that variable empty and let the database do that job (It's an id). Haven't found out how to do this though!

It's an MySQL database.

Reimeus · Answer 1 · 2012-08-04T00:41:26.037

19

Just provide the String entries in your sql query string and the database will populate the auto_increment field:

String insertSQL = "INSERT INTO MyTable (StrCol1, StrCol2) VALUES (?, ?)";
PreparedStatement preparedStatement = dbConnection.prepareStatement(insertSQL);
preparedStatement.setString(1, "Val 1");
preparedStatement.setString(2, "Val 2");
preparedStatement.executeUpdate();

edited Aug 04 '12 at 00:41

answered Aug 04 '12 at 00:29

Reimeus

158,255
15
216
276

score 18 · Accepted Answer · answered Aug 04 '12 at 00:30

18

That's relatively simple, just leave out the autoincrement column from the column list:

insert into mytbl(str1, str2) values (?, ?)

Prepare the statement, set the two parameters, and execute in non query mode.

answered Aug 04 '12 at 00:30

Sergey Kalinichenko

714,442
84
1,110
1,523

Thanks, everyone pretty much said the same but your were first so u'll get the right :) – Lucas Arrefelt Aug 04 '12 at 01:00
4

In MySQL, pass NULL to the ID column also make it AUTO_INCREMENT `insert into t (id, col1, col2) values (NULL, ?, ?)` or `insert into t (id, col1, col2) values (?, ?, ?)` with java code `stmt.setNull(1, java.sql.Types.INTEGER)` – LiuYan 刘研 Aug 04 '12 at 02:57
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:37
1

@DeepSidhu1313 Since there's only one parameter, you call `setString(1,...)`, not 2. – Sergey Kalinichenko Oct 17 '14 at 17:07

score 5 · Answer 3 · edited Aug 31 '17 at 14:08

If the field is already autoincrement, it is enough to populate just the other two fields. Here an example

String sql = "INSERT INTO mytableName("
    + "fistStringColumnName,"
    + "secondStringColumnName) "
    +  "VALUES(?,?)";

try (PreparedStatement pstmt = connection.prepareStatement(sql)) {

    // Set the values
    pstmt.setString(1, "firstString");
    pstmt.setString(2, "secondString");

    // Insert 
    pstmt.executeUpdate();

} catch (SQLException e) {

} catch (FileNotFoundException e) {

}

Insert row into database with PreparedStatement

3 Answers3

Linked