when dynamically generating where clauses sql, hardcode where '1' = '1' sql don't have track whether prepend and each following clause. documented in many places, such stackoverflow question.
is there equivalent pattern dynamically generating set clause update statements? rather not keep track of whether need prepend comma or not. in case there aren't general solutions, interacting oracle database on jdbc.
edit particular use case, need dynamically change columns being set. solution requires query contain columns being set no go. have table 20+ columns, 3 or 4 change @ given time. ran load tests , found way meet performance goals send in data needs updated. i'm trying write pretty code so.
one way avoid keeping track of column count purpose of appending commas assign possible columns, , pass set of control variables decide if column should assigned or not:
update mytable set col1 = case ? when 1 ? else col1 end , col2 = case ? when 1 ? else col2 end , col3 = case ? when 1 ? else col3 end ... -- condition goes here parameters @ odd indexes flags pass indicate corresponding column must set. parameters @ corresponding indexes values want set, or null if not setting corresponding field.
this approach doubles number of jdbc parameters need pass, in return statement positions of columns fixed, can prepare , reuse instead of building dynamically.
