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

Question

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!

Answer 1

@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.