CODEWITHSUNDEEP
javasql-server-2008prepared-statement
iandisme
iandisme

Reputation: 6406

Best way to indicate no change on row update with Java PreparedStatement

I am creating a method to update a row in a SQL Server 2008 database. The SQL String looks something like this:

private static final String UPDATE_ROW = 
    "UPDATE MyTable SET FieldOne = ?, FieldTwo = ? " +
    "WHERE IDField = ?";

It's simplified, of course. But the rub is that not all of the fields will necessarily change. I know that with straight SQL you can just put the field name in for the value and nothing will change; however, I don't know how to do this with the Java PreparedStatement.

I could work around it by calling one update for each field to be changed (there are up to ten) for each row, but that is just fugly and I would really like to avoid it. Can anyone tell me how to put the field name in as a parameter value, or at least give me a clean solution?

Upvotes: 2

Views: 860

Answers (3)

iandisme
iandisme

Reputation: 6406

I couldn't find a way to do what I described, so I ended up reading the values of the things I was updating and passing in those values.

Upvotes: 1

Dilum Ranatunga
Dilum Ranatunga

Reputation: 13374

Its good that you are trying to avoid generating a specialized statement for each update.

Are the fields not inter-related? Because if they are inter-related, the update had better maintain inter-field consistency. So you need to first read the values, and then write all of them back -- both the changed and unchanged ones.

If they really are completely unrelated to one another, have a series of updates all getting committed at the same time.

Usually, one ends up somewhere in-between -- there are clusters of fields that are inter-related. For example, a Person record that contains several fields related to BillingAddress. In such cases, have a prepared statement for each group of related fields.

If you are trying to avoid the cost of a read (to get the current values), then consider a stored procedure, where unchanged field values are encoded with NULLs.

If you are not using an ORM, you can also consider using a cursored ResultSet, and update the fields one by one, then commit the changes using updateRow(). See java.sql.ResultSet. To do the same thing as the Javadoc using a PreparedStatement, you will need to use a variant of Connection.prepareStatement(...).

Upvotes: 0

user330315
user330315

Reputation:

It will be a lot more efficient if you do create specialized UPDATE statements that only state the columns that have changed.

If you always update all columns you'll generate a lot of overhead by e.g. updating indexed columns which will cause the corresponding index to be updated as well (and without the actual need for this).

This will happen even if you specify UPDATE foo SET bar = bar if I'm not mistaken. I don't think SQL Server optimizes such updates away.

Upvotes: 0

Related Questions