JDBC Insert with autoincrement

Question

I'm executing a query using MySQL library in java. In my db structure there's a column called cod_nfs (primary key, not null and auto_increment). Usually in every insert query I set the value to null and after query execution it increment last id but doing this in java give me exception.

That's the code:

String SQL = "INSERT INTO infosetdata(cod_nfs,savingDate_nfs,attributeValue_nfs,codInfoSet_nfs,codColumn_nfs,codRow_nfs) VALUES (null,'2019-01-08',?,?,?,?)";


for( String value : values ) {
                ps = conn.prepareStatement(SQL);
                ps.setString(1, value);
                ps.setInt(2, dataId);
                ps.setInt(3, y);
                ps.setInt(4, x);
                ps.executeUpdate();
                y++;
            }

How can I execute the query with auto increment id?

Answer 1

Do not include the primary key column in the insert.

Also, do not prepare the statement every time. Just do it once.

String SQL = "INSERT INTO infosetdata (savingDate_nfs,attributeValue_nfs,codInfoSet_nfs,codColumn_nfs,codRow_nfs) " 
           + "VALUES ('2019-01-08',?,?,?,?)";
ps = conn.prepareStatement(SQL);
for( String value : values ) {
   ps.setString(1, value);
   ps.setInt(2, dataId);
   ps.setInt(3, y);
   ps.setInt(4, x);
   ps.executeUpdate();
   ResultSet rs = ps.getGeneratedKeys();
   if (rs.next()) {
     int pk = rs.getInt(1);
     System.out.println("Generated PK = " + pk);
   }
   y++;
}

As you see, you can recover the generated PK value using PreparedStatement.getGeneratedKeys().

Answer 2

You shouldn't set a value for cod_nfs because it is already set as auto incremental. Try below:

String SQL = "INSERT INTO infosetdata(savingDate_nfs,attributeValue_nfs,codInfoSet_nfs,codColumn_nfs,codRow_nfs) VALUES ('2019-01-08',?,?,?,?)

If it gives error, make sure your cod_nfs column is set as auto incremental. Try running:

ALTER TABLE infosetdata MODIFY cod_nfs INT(11) NOT NULL AUTO_INCREMENT

Thanks @Mark Rotteveel to help on that issue in the comments.