更新等效于WHERE'1'='1' [英] update equivalent of WHERE '1' = '1'

问题描述

为我的sql动态生成WHERE子句时，我喜欢将WHERE '1' = '1'硬编码到sql中，因此我不必跟踪是否在每个后续子句前添加AND.在许多地方都有很好的记录，例如 stackoverflow问题.

When dynamically generating WHERE clauses for my sql, I like to hardcode WHERE '1' = '1' into the sql so I don't have to track whether to prepend AND to each following clause. This is well documented in many places, such as this stackoverflow question.

是否存在用于为UPDATE语句动态生成SET子句的等效模式?我宁愿跟踪是否需要加逗号.如果没有通用的解决方案，这将用于通过jdbc与oracle数据库进行交互.

Is there an equivalent pattern for dynamically generating the SET clause for UPDATE statements? I rather not keep track of whether I need to prepend a comma or not. In case there aren't any general solutions, this will be for interacting with an oracle database over jdbc.

编辑 对于我的特定用例，我将需要动态更改要设置的列.因此，任何需要查询包含要设置的所有列的解决方案都是不可行的.我们有一个包含20多个列的表，但是在任何给定时间只有3或4个会发生变化.我们进行了一些负载测试，发现达到性能目标的唯一方法是仅发送需要更新的数据.现在，我只是想编写漂亮的代码来做到这一点.

EDIT For my particular use case, I will need to dynamically change which columns are being set. So any solution which requires the query to contain all columns being set is a no go. We have a table with 20+ columns, but only 3 or 4 will change at any given time. We ran some load tests and found the only way to meet performance goals was to just send in data that needs to be updated. Now I'm just trying to write pretty code to do so.

推荐答案

一种避免跟踪列数以添加逗号为目的的方法是始终分配所有可能的列，并传递一组控制变量来确定是否列是否应该分配:

One way to avoid keeping track of column count for the purpose of appending commas is to always assign all possible columns, and pass a set of control variables to decide if a column should be assigned or not:

UPDATE MyTable
SET
    col1 = CASE ? WHEN 1 THEN ? ELSE col1 END
,   col2 = CASE ? WHEN 1 THEN ? ELSE col2 END
,   col3 = CASE ? WHEN 1 THEN ? ELSE col3 END
WHERE
    ... -- condition goes here

奇数索引处的参数是传递的标志，用于指示必须设置相应的列.它们对应的偶数索引处的参数是您要设置的值，如果您未设置相应的字段，则为NULL.

Parameters at odd indexes are flags that you pass to indicate that the corresponding column must be set. Parameters at their corresponding even indexes are values that you want to set, or NULL if you are not setting the corresponding field.

这种方法使需要传递的JDBC参数数量增加了一倍，但作为回报，您得到一条声明，其中所有列的位置都是固定的，因此您可以准备和重用它，而不必动态地构建它.

This approach doubles the number of JDBC parameters that you need to pass, but in return you get a statement where positions of all columns are fixed, so you can prepare and reuse it instead of building it dynamically.

这篇关于更新等效于WHERE'1'='1'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！