CODEWITHSUNDEEP
javamysqljdbi
Robbie
Robbie

Reputation: 53

Java JBDI how to get the affected rows and generated id with a query on duplicate key update mysql

I'm trying to find out how to return affected rows on an insert and also get the generated key id from the same query;

I have this query

@SqlUpdate("""
            INSERT INTO table_name (
                account, 
                state,
                lock,
                external_id
)
            VALUES (
                :account,
                :state,
                :lock,
                :external_id)
            """)
    @GetGeneratedKeys("id")
    Long insert(@BindBean ExampleBean exampleBean);

That I want to update to something like this for an upsert so when I find a duplicate entry I would like to update it to something like this

  @SqlUpdate("""
            INSERT INTO table_name (
                account, 
                state,
                lock,
                external_id
)
            VALUES (
                :account,
                :state,
                :lock,
                :external_id)
                ON DUPLICATE KEY
                UPDATE
                external_id = :external_id
                lock = :lock
            """)
    @GetGeneratedKeys("id")
    idRows insert(@BindBean ExampleBean exampleBean);  

 public record idRows(Long id, int rowsAffected) {}

called by doing

try (Handle handle = jdbi.open()) {
        return handle.attach(ExampleBean.class).upsert(exampleBean));
        } catch (Exception e) {
            log(example);
            return null;
        }

on my Hikari Datasource I have set "useAffectedRows", true

where I can map both the affected rows and the generated ID at the same time to my record, any ideas would be appreciated!

Upvotes: 0

Views: 172

Answers (1)

4EACH
4EACH

Reputation: 2197

@SqlUpdate("""
    INSERT INTO table_name (
        account, 
        state,
        lock,
        external_id
    )
    VALUES (
        :account,
        :state,
        :lock,
        :external_id)
    ON DUPLICATE KEY UPDATE
        external_id = :external_id,
        lock = :lock
""")
@GetGeneratedKeys({"id", "rowsAffected"})
idRows upsert(@BindBean ExampleBean exampleBean);
Your idRows record should look like this:
public record idRows(Long id, int rowsAffected) {}

This way, when you call the upsert method, it should return an instance of idRows with both the generated ID and the number of affected rows.

Upvotes: 0

Related Questions