Java, JDBC INSERT ON DUPLICATE KEY UPDATE wild cards

Question

I Have a next SQL Query:

const val INSERT_OR_UPDATE_ITEM_SQL = "" +
            "INSERT INTO `items` (owner_id, object_id, item_id, count, enchant_level, item_location, item_location_data, custom_type1, custom_type2, duration_left, create_time)" +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?) " +
            "ON DUPLICATE KEY UPDATE owner_id=?,object_id=?,item_id=?,count=?,enchant_level=?,item_location=?,item_location_data=?,custom_type1=?,custom_type2=?,duration_left=?,create_time=?"

And this is preparation of PreparedStatement:

statement.setString(1, item.ownerId)
statement.setInt(2, item.objectId)
statement.setInt(3, item.template.id)
statement.setInt(4, item.count)
statement.setInt(5, item.enchantLevel)
statement.setString(6, item.itemLocation.name)
statement.setInt(7, item.itemLocationData)
statement.setInt(8, item.customType1)
statement.setInt(9, item.customType2)
statement.setInt(10, item.durationLeft)
statement.setLong(11, item.createTime)

The issue here is when i try to execute query :

Exception in thread "main" java.sql.SQLTransientConnectionException: (conn=352) Parameter at position 12 is not set

This is because I have 22 Questions mark instead of 11 (because of ON CONFLICT UPDATE)

My Simple task is to make INSERT or UPDATE

Answer 1

As your query stands, (some of) the parameters need to be passed twice (once for insert and another for update). MySQL offers the VALUES(...) function to access the insert values by column name in the update clause - which is exactly there to save you that hassle.

Also: the UPDATE clause would be better expressed if it addressed only non-primary key columns, not all columns.

Assuming that your table has columns owner_id, object_id, item_id, cnt, enchant_level where the first three columns are the primary key:

insert into items (owner_id, object_id, item_id, cnt, enchant_level)
values (?, ?, ?, ?, ?)
on duplicate key update 
set cnt = values(cnt), 
    enchant_level = values(enchant_level)

Or, if you are running MySQL 8.0.19 or higher you can use the new alias-based syntax (values() was an unfortunate name choice in MySQL, as it somehow conflicts with the homonym SQL standard row constructor):

insert into items (owner_id, object_id, item_id, cnt, enchant_level)
values (?, ?, ?, ?, ?) v
on duplicate key update 
set cnt = v.cnt,
    enchant_level = v.enchant_level