We're using Derby and have a lot of code which goes like this:
try (ResultSet rs = executeQuery(...)) {
if (rs.next()) {
updateRowSet(rs, ...);
rs.updateRow();
} else {
executeUpdate(...);
}
}
In the past, we were searching for a way to do this logic server-side, and found that some databases supported an "upsert" (update or insert) operation. Derby had a feature request for MERGE INTO
which was supposedly the SQL:2003 standard way of doing this, so we sat and watched the ticket, and much time passed.
Derby 10.11 finally added MERGE INTO
. Nobody has had time to go through and update the code to use it yet, but on reading their documentation, all their examples show merging from one table to another. But hang on, our data isn't in a table yet!
I know I could put it in a table, but then it is multiple queries again, which completely defeats the point of using it.
I'm sure it is possible to do it without putting it into a table, but because the documentation does not show a single example, I'm not sure how to proceed.
Here is what I have been trying:
try (PreparedStatement ps = connection.prepareStatement(
"MERGE INTO things AS target " +
// Awkward point 1:
// It wants a "source" table, but I don't have one.
// So I thought I would try to use the same table with
// another name.
" USING things AS source ON target.id = ?" +
" WHEN MATCHED THEN" +
" UPDATE SET data = ?" +
" WHEN NOT MATCHED THEN" +
" INSERT (id, data) VALUES (??, ?)"))
{
ps.setLong(1, id);
ps.setBinaryStream(2, data);
ps.setLong(3, id);
// Awkward point 2:
// Passing an InputStream into a query as two
// parameters.
ps.setBinaryStream(4, data);
ps.execute();
}
This doesn't appear to do any of the inserts, but also doesn't give an error, so I have absolutely nothing to go on.