I got a method that adds/modifies to the db. I want it to update if a certain value for a colmun (a primary key) is present, and add otherwise... Do I have to do two seperate queries, and if so, what is the query that will allow me to check?
posted 13 years ago
There're a couple of ways that I've successfully used to approach this. . . 1) Assume that it exists and do an UPDATE. Then check to see the number of rows updated by executeUpdate. If 0, then do an INSERT. (This assumes that you'll generally do more Updates than inserts). 2) Depending upon your design/architecture, if you know that your object would only have knowledge of its primary key if its already been inserted, and would always have knowledge of its primary key if its been inserted, check your object's state to determine which method to execute. 3) Push the decision to a stored procedure (use CallableStatement) and let it decide within itself. Advantage: one Java statement, using compiled procedural code, rather than two Java statements (at least) with their corresponding counts or result sets.