my method has to check if a row already exists in db and in case it does - update it, or in case it does not - insert it.
the db table is unique indexed .
the mission sounds very simple, but i thought about 3 possible ways of doing this, and i'm not sure wich one will be the fastest + the best architecture.
way 1: to run "select..." request, and then check if resultset.next(). if true - update, else - insert.
way 2: to run "insert...". if the unique index does not exist, it will work fine. if it exists, the sqlexception will be thrown. in the catch block i check if exception.getMessage() includes a string "duplicate entry" (i don't remember the message text right now...) if it does include it - i run "update..".
way 3: first of all i run "delete.." query, and then "insert..".
You should use the select.. and then insert or update approach. The reason is simple: it's not a good design throwing exceptions to check business rule, and exceptions throwing is expensive. So, the exceptions handling approach is out. The delete approach doesn't seems a good design too, there is a much higher cost for the database to delete and than create a new record than to select and update.
Frederico Melo<br />--------------<br />Software Architect<br />Sun Certified Enterprise Architect for J2EE<br />IBM Rational Unified Process Specialist
A few things to think about: * since generally you're going to do more than one db operation, you need to think about locking, unless you know from the way the application is written that there won't be any concurrency problems * the most efficient approach might depend on whether you expect mostly inserts, mostly updates, or about the same of each * consider doing this in a stored procedure; that will insure you never do more than one database round-trip (and the cost of each round-trip will be a big part of the overall cost).