3

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

ufk
  • 30,912
  • 70
  • 235
  • 386

2 Answers2

1

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

Vasiliy Dronov
  • 11
  • 1
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,.

simaremare
  • 401
  • 2
  • 10
  • thanks. i use traditional JDBC. i just that maybe the simplejdbcinsert can check for itself if the row exists and if so to replace. but that wouldn't be so simple after all :) k thanks i'll do it manually – ufk Mar 06 '12 at 12:16
  • i don't really know your exact case, but if the key is not a generated, we have no choice to do it automatically,. – simaremare Mar 06 '12 at 13:29
  • 2. do you know if Hibernate does this using two queries or using INSERT ... ON DUPLICATE KEY? – Sergi0 Aug 15 '13 at 10:51