JDBC - INSERT and return generated id, or if DUPLICATE KEY return old id

The title is pretty clear... I want to be able to add an item to the database, and in case there's already an item with a matching unique column, return that item's id...

The expression below is as close as I found to a solution

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

I was able to fetch the new id (when inserting a new row) using statement.generatedKeys(). will "id" appear in statement.generatedKeys() in case of duplicate? if not, how can I fetch it?

UPDATE : using stored procedure, the following is exactly what I need:

INSERT INTO applications (path, displayName) VALUES (?, ?) 
ON DUPLICATE KEY UPDATE 
   id = LAST_INSERT_ID(id), path = ?, displayName = ?;
SELECT LAST_INSERT_ID() INTO ?;
开发者_开发知识库

It works on different project i'm working on which uses MySQL Connector in .NET, but unfortunately Stored Procedure is not an option for me in this Java project.

Is there any other way I can send 2 statements in the same network trip?

---

This seems legit

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;

but this seems crazy:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

Would you update the ID field of an existing record? What will happen to those records referencing to this one?

Anyway, guess in case of a normal update (the forst one) statement.generatedKeys() wont return the already existing ID. In this case you'll probably have to issue an additional select statement (with the unique key columns in the where clause: select id from table where a=1 and b=2) to find out ID of the record.

Or alternatively you could write a stored procedure which does the insert/update returns the ID in both case.

---

So I just had the same requirement as the OP. The way Mysql handles INSERT...ON DUPLICATE KEY UPDATE is just asinine, but unfortunately we have to live with it. When you use ON DUPLICATE on Mysql 5.5 or above, if the insert happens without duplicate, JDBC returns count of 1 and the ID of the newly inserted row. If the action taken is an update due to duplicate, JDBC returns count of 2 and both the ID of the original row AND the newly generated ID, even though the new ID is never actually inserted into the table.

You can get the correct key by calling PreparedStatement.getGeneratedKeys(). The first key is pretty much always the one you are interested in. So with the above example:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;

You can get the inserted or updated ID by calling:

Long key;
ResultSet keys = preparedStatement.getGeneratedKeys();
if (keys.next())
    key = keys.getLong("GENERATED_KEY");

---

If your goal is to prevent duplicate records, you can set up your table with a primary key/UNIQUE constraint. You could then take one of the following paths in your code:

1. Programmatically check for the uniqueness of the row you're about to add and handle the case where that row already exists in the table
2. Try to insert the new row. If it's a duplicate, the attempt will fail with an exception and you can find the old row using the same criterion. I think this is a poor approach.