replace on duplicate with simpleJdbcInsert

Question

I use tomcat for my server side with Mysql Server 5.5.

i use Spring framework for the database connectivity.

I would like to be able to insert a row to a table using simpleJdbcInsert. if the insert fails because of a duplicate i want it to replace the duplicated row.

is there a way to do that simpleJdbcInsert or should I just use jdbcTemplate and create my on query with "ON DUPLICATE" statement?

thanks

Answer 1

logically, duplication occurs when there is a double primary key.

so, if you are using the traditional JDBC or even Hibernate, then you should check if the same primary key value already exists, before you insert the new one.

but if the primary key is not set yet, or will be set by the DBMS, there will be different problem.

1. in JDBC, again you need to do the old style manual checking by querying before inserting the new one, but

2. in Hibernate, you just need to update it. Hibernate will create the new if there is no duplicate, but will replace if there is a duplicate,.

Answer 2

in JDBC, again you need to do the old style manual checking by querying before inserting the new one, but

That's incorrect - because there is a time gap between the checking of existing row and the actual insert, so somebody in parallel thread can insert the row with the same key simultaneously and you'll obtain the DuplicateKeyException (if we talk about spring). So, you have to handle this exception or just use the honest SQL insert into ... on duplicate key ....