CODEWITHSUNDEEP
javadatabasepostgresqljdbcprepared-statement
Eli Golin
Eli Golin

Reputation: 393

How do I deal with optional columns when constructing a prepared statement

I would like to leverage a prepared statement in order to insert/update postgres db. The thing is that the table contains nullable columns I might or might not have a value for in runtime.
In case I do not have anything to insert/update , I need the old value to remain.
Is there a syntax I can use for that case?
What are the best practices in cases like that, it seems like a very common problem?

P.S I use java/scala with plain jdbc.

Upvotes: 1

Views: 574

Answers (1)

Mureinik
Mureinik

Reputation: 311393

You could, of course, just construct a statement without the columns you don't want to update.

If this isn't an option in your usecase, you could construct a case expression with a "flag" to indicate if the column should be bound or not.

E.g.:

String sql = "UPDATE mytable " +
             "SET " +
             "col1 = CASE(? WHEN 1 THEN ? ELSE col1 END), " +
             "col2 = CASE(? WHEN 1 THEN ? ELSE col2 END) " +
             "WHERE id = ?";

PreparedStatement ps = con.prepareStatement(sql);

// col1 should be updated
ps.setInt(1, 1);
ps.setString(2, newValueCol1);

// col2 should not be updated
ps.setInt(3, 0);
ps.setString(4, null); // or any other value...

// bind the where clause
ps.setInt(5, someId);

ps.executeUpdate();

// close resources and clean up, omitted for brevity's sake

Upvotes: 2

Related Questions