Java, JDBC INSERT ON DUPLICATE KEY UPDATE wild cards

April 27, 2023

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

>Solution :

As your query stands, (some of) the parameters need to be passed twice (one 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)