如何通过可以绑定参数的SQL语句在某些条件下不更改某些列(How I can don't change some column under certain conditi via an SQL statement which can bind parameters)

如何通过可以绑定参数的SQL语句在某些条件下不更改某些列(How I can don't change some column under certain conditi via an SQL statement which can bind parameters) CREATE TABLE "table" ( "id" ITEGER PRIMARY KEY AUTOICREMET OT ULL, "col1" BLOB, "col2" BLOB, "col" BLOB, "coln" BLOB ); id|col1|col2|col|coln 1 | 1-1| 1-2| 1-| 1-n 2 | 2-1| 2-2| 2-| 2-n UPDATE table SET col1=?, col2=?, col=?, coln=? WHERE id=?;

如何通过上面的SQL语句在某些条件下不改变某些列。

sqlite的。

例如，

public static class Bean{ public long id; public byte[] col1; public byte[] col2; public byte[] col; public byte[] coln; } PreparedStatement psUpdate = dsConn.prepareStatement("UPDATE table SET col1=?, col2=?, col=?, coln=? WHERE id=?;"); Bean bean = new Bean(); if(null != ){ psUpdate.setBytes(1, );//col1 }else{ (1);//omit to update } if(null != ){ psUpdate.setBytes(2, );//col2 }else{ (2);//omit to update } if(null != ){ psUpdate.setBytes(, );//col }else{ ();//omit to update } if(null != n){ psUpdate.setBytes(4, n);//coln }else{ (4);//omit to update } psUpdate.setBytes(5, bean.id);//WHERE id=? ();

我已经阅读了有关sqlite或java的文档。 不幸的是，这似乎是不可能的。

CREATE TABLE "table" ( "id" ITEGER PRIMARY KEY AUTOICREMET OT ULL, "col1" BLOB, "col2" BLOB, "col" BLOB, "coln" BLOB ); id|col1|col2|col|coln 1 | 1-1| 1-2| 1-| 1-n 2 | 2-1| 2-2| 2-| 2-n UPDATE table SET col1=?, col2=?, col=?, coln=? WHERE id=?;

How I can don't change some column under certain conditi just via the above SQL statement.

sqlite.

for example,

public static class Bean{ public long id; public byte[] col1; public byte[] col2; public byte[] col; public byte[] coln; } PreparedStatement psUpdate = dsConn.prepareStatement("UPDATE table SET col1=?, col2=?, col=?, coln=? WHERE id=?;"); Bean bean = new Bean(); if(null != ){ psUpdate.setBytes(1, );//col1 }else{ (1);//omit to update } if(null != ){ psUpdate.setBytes(2, );//col2 }else{ (2);//omit to update } if(null != ){ psUpdate.setBytes(, );//col }else{ ();//omit to update } if(null != n){ psUpdate.setBytes(4, n);//coln }else{ (4);//omit to update } psUpdate.setBytes(5, bean.id);//WHERE id=? ();

I haved read the document about sqlite or java. Unfortunately, it seems imposible.

最满意答案

您应该只构造适当的UPDATE语句，其中只包含您实际要更新的列。

但是如果你真的想要使用单个语句，那么如果参数为ULL，你可以使用CASE表达式用旧值更新列：

UPDATE MyTable SET col1 = CASE WHE ?1 IS ULL THE col1 ELSE ?1 ED, col2 = CASE WHE ?2 IS ULL THE col2 ELSE ?2 ED, ... WHERE id = ?5;

You should just ctruct the appropriate UPDATE statement with only the columns that you actually want to update.

But if you really want to use a single statement, you could use CASE expressi to update the columns with their old value if the parameter is ULL:

UPDATE MyTable SET col1 = CASE WHE ?1 IS ULL THE col1 ELSE ?1 ED, col2 = CASE WHE ?2 IS ULL THE col2 ELSE ?2 ED, ... WHERE id = ?5;