Java and MySQL: duplicate value for 'primary key' exception

Question

I do some insert in a MySQL DB with a for cycle.

I have a problem with the auto-increment, 'cause after the first insert I obtain an com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException.

Here the code:

for (int i=0; i<numKeys; i++) {
try {
   stm1 = this.db_connection.prepareStatement("INSERT INTO mytable (Cod, Prop, Field, Value) VALUES (?,?,?,?)");
   stm1.setInt(1, Statement.RETURN_GENERATED_KEYS);
   stm1.setInt(2, 0);
   stm1.setString(3, aname);  
   stm1.setString(4, avalue);
       stm1.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Cod is an auto-increment-field.

Jon Taylor · Accepted Answer · 2012-07-05 09:04:36Z

2

If it is a auto incremented field then there is no reason to pass it in as an argument, just don't include it in the Insert statement.

stm1 = this.db_connection.prepareStatement("INSERT INTO mytable (Prop, Field, Value) VALUES (?,?,?)");

And remove the appropriate setInt method below.

answered Jul 5, 2012 at 9:04

Jon Taylor

7,9255 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jigar Joshi · Accepted Answer · 2012-07-05 09:04:40Z

1

Statement.RETURN_GENERATED_KEYS is a int constants,

If you try adding it multiple time with same primary key it will fail to add

answered Jul 5, 2012 at 9:04

Jigar Joshi

241k42 gold badges409 silver badges446 bronze badges

1 Comment

Jon Taylor Over a year ago

Well that and the fact its an auto increment field so he really shouldn't be setting its value himself.

sergiofbsilva · Accepted Answer · 2012-07-05 09:10:50Z

0

If Cod is autoincrement you don't need to insert it. You should try the following code.

for (int i=0; i<numKeys; i++) {
try {
   stm1 = this.db_connection.prepareStatement("INSERT INTO mytable (Prop, Field, Value) VALUES (?,?,?)");
   stm1.setInt(1, 0);
   stm1.setString(2, aname);  
   stm1.setString(3, avalue);
   stm1.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}

}

edited Jul 5, 2012 at 9:10

answered Jul 5, 2012 at 9:04

sergiofbsilva

1,59615 silver badges20 bronze badges

2 Comments

Jon Taylor Over a year ago

I don't think this would work, you are passing in a different number of paramaters to values in the sql statement. See my answer below. To clarify, you need to remove one of the ? in the values part of your sql statement so that there are only 3 of them.

sergiofbsilva Over a year ago

@JonTaylor yes my mistake! Thnks!

Collectives™ on Stack Overflow

Java and MySQL: duplicate value for 'primary key' exception

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related