CODEWITHSUNDEEP
javamariadbsql-updatemysql-insert-id
orochi
orochi

Reputation: 1258

How to get the latest id from an upsert Java MariaDB

My table is like that:

users (id, name, email, age)
id is autoincrement
name varchar and unique
email varchar
age decimal

I have a SQL Like this:

INSERT INTO users (name, email, age)
VALUES ('Alice', '[email protected]', 30)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    age = VALUES(age);

With java I can retrieve the last generated id:

ResultSet rs = stmt.getGeneratedKeys();
                int generatedKey = 0;
                if (rs.next()) {
                    generatedKey = rs.getInt(1);

I can retrieve the generatedKey if there was an insert ok good.

But if my sql was about an update, there was no generated key becuase it was already there.

Is there a way to get the id from that specific insert/update?

Upvotes: 2

Views: 93

Answers (2)

nik0x1
nik0x1

Reputation: 1504

I hope the following approach can help you.

Step 1. Execute the insert preparedStatement.

PreparedStatement preparedStatement = connection.prepareStatement(
                "INSERT INTO users (name, email, age) VALUES (?, ?, ?) " +
                "ON DUPLICATE KEY UPDATE " +
                "name = VALUES(name), email = VALUES(email), " +
                "id = LAST_INSERT_ID(id)");
// set the preparedStatement parameters
int returned = preparedStatement.executeUpdate();

Step 2. Execute the select last insert id statement.

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT LAST_INSERT_ID() AS n");
resultSet.next();
int id = resultSet.getInt(1);

P.S. To understand whether a new row has been inserted or an existing one has been updated:

if (returned == 1) {
   System.out.println("Inserted");
} else {
   System.out.println("Updated");
}

Upvotes: 0

Georg Richter
Georg Richter

Reputation: 7516

Just append a RETURNING to your insert statement

INSERT INTO users (name, email, age)
VALUES ('Alice', '[email protected]', 30)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    age = VALUES(age)
RETURNING id;

and fetch the id afterwards.

Upvotes: 0

Related Questions