Statement.RETURN_GENERATED_KEYS returns null with ON DUPLICATE KEY UPDATE

Question

Here is my sample code lines

String query = "INSERT INTO tb_product_group values(?,?,?,?,?) ON DUPLICATE KEY UPDATE prg_name = values(prg_name)";
stmtGroups = con.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
stmtGroups.setInt(index++, java.sql.Types.NULL);
stmtGroups.setString(index++, "");
stmtGroups.setString(index++, str[1]);
stmtGroups.setString(index++, str[2]);
stmtGroups.setInt(index++, oprDto.getId());
System.out.println(stmtGroups);
ResultSet rs = stmtGroups.getGeneratedKeys();
if (rs.next()){
    groupID = rs.getLong(1);
}

first time when I insert a data like (null,"1","1","1",1); I get a groupID with newly generated ID. but on the second time I try to add same data which fires UPDATE statement because of unique key constraint. and that time I get groupID as a null.

I try REPLACE INTO tb_product_group values(?,?,?,?,?) but also get null as a generatedKeys.

It's considered bad programming style to leave out the column names in the `insert` statement. Explicitely stating them is much better: `insert into (col1, col2, col3) values (?,?,?)` (with the additional benefit that someone reading your code actually understands what each parameter is for) — , Mar 26 '13 at 12:38
@a_horse_with_no_name Thanks for your kind suggestion. I will add column name sure. — Mayank Pandya, Mar 26 '13 at 12:43

score 6 · Accepted Answer · answered Mar 26 '13 at 12:27

Try

INSERT INTO tb_product_group values(?,?,?,?,?) ON DUPLICATE KEY UPDATE
prg_name = values(prg_name), id = last_insert_id(id)

replace id with the name of your auto_increment column.

Edit: I haven't tried this with the java api, so there might be another problem.

Statement.RETURN_GENERATED_KEYS returns null with ON DUPLICATE KEY UPDATE

1 Answers1