Chris Mar's Weblog Blog Different

When doing straight JDBC coding, wouldn't it be nice if you could .merge() functionality even though you are not using ejb3? Recently, I discovered a way to do this using the Oracle statement "MERGE" to perform an "UPSERT" for me. UPSERT meaning please either update the existing record or create a new one for me.

The trick is using Oracle's MERGE command which is designed to merge records from 2 different tables. Since, I'm only inserting new data, I used a SELECT from dual to simulate selecting from a table. Then Oracle tries to see if an existing record is available based on the conditions specified in the "ON" for the "USING" statement.

This works perfect and is going into production on a system using Oracle 10g.

Enjoy, I've hand written a lot of UPSERT code in the past and it is never quite this elegant:

PreparedStatement statement = null;
try {
     statement = connection.prepareStatement("MERGE INTO ARTICLE a " +
                              "USING (SELECT ? title, ? author, ? body FROM dual) incoming " +
                              "ON (a.title = incoming.title AND a.author = incoming.author) " +
                              "WHEN MATCHED THEN " +
                              "UPDATE SET a.body = incoming.body " +
                              "WHEN NOT MATCHED THEN " +
                              "INSERT (a.title, a.author, a.body) " +
                              "VALUES (incoming.title, incoming.author, incoming.body)");

     statement.setString(1, "Title of Article");
     statement.setString(2, "Author Name");
     statement.setString(3, "A Cool Story from Last Night");
     statement.execute();
}

We just fixed a bug in our code that was a result of Serialization and subclassing. We created an AbstractBase class that contained a few attributes. Our child classes all extended the AbstractBase and implemented Serializable. Well, the values in the AbstractBase did not get serialized when we sent our objects over JMS.

When the objects where inflated on the other side, the values in AbstractBase where reinitialized via the AbstractBase constructor. Because the default values were almost always the expected values, we missed this in testing. When we did our Junit tests the error did not show because the objects hadn't been sent over the wire via JMS.

Moral is the attributes of your parent don't get serialized if your parent doesn't explictly declare they are serializable.