在SQL查询的WHERE条件中多次使用相同的参数以在JDBC中使用 [英] Utilise the same parameter multiple times in WHERE conditions of an SQL query for use in JDBC
问题描述
我想通过JDBC和准备好的语句SQL查询发送到Oracle DB，如下所示:
To my Oracle DB I want to send via JDBC and a prepared statement SQL query like this:
SELECT * FROM mytable WHERE col1 = <ONEVAL> col2 = <ONEVAL> AND col3 = <ONEVAL>;
I know from SO answer How to use the same value multiple times in jdbc postgresql prepared statement that JDBC doesn't support named parameters, so a direct prepared statement
SELECT * FROM mytable WHERE col1 = ? AND col2 = ? AND col3 = ?;
仅在提供3次< ONEVAL> 的情况下有效，但是我不想这样做，我想调整已准备好的语句的SQL，以便只使用一个?，但将其应用于所有 WHERE 条件.像
only works if I provide <ONEVAL> 3 times, but I do not want to do that, I rather would like to adjust the SQL of the prepared statement, so that it takes only one ? but applies it to all WHERE conditions. Something like
WITH VAl = ? SELECT * FROM mytable WHERE col1 = VAL AND col2 = VAL AND col3 = VAL;
但是这种尝试似乎无效.有没有办法在SQL字符串中多次使用一个准备好的语句参数?
But this try doesn't seem to work. Is there way to do this kind of multi-use of one prepared statement parameter in the SQL string?
在@Selvin的善意建议之后，我将以下SQL字符串用于准备好的语句:
After the kind suggestion by @Selvin I used the following SQL string for the prepared statement:
DECLARE val varchar(30) :=? ; SELECT * FROM mytable WHERE col1=&val AND col2=&val AND col3=&val
然后我得到错误消息" ORA-06550:第1行，第33列:PLS-00103:遇到符号"SELECT"，预期以下情况之一时: [...]"
Then I get the error "ORA-06550: line 1, column 33: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: [...]"
推荐答案
如果我做对了，那么您需要同时将所有三列都等于相同的值，所以我猜这些SQL可以为您提供帮助
If I got it right, you need all three columns to be equal to the same value at the same time, so my guess is that these SQL will help you
SELECT * FROM mytable WHERE col1 = ? AND col1 = col2 AND col1 = col3;
UPD:有一个更好的主意，或者我会说一个更紧凑的主意
UPD: There is a better or I'd say more compact idea
SELECT * FROM mytable WHERE ? = all(col1, col2, col3)
这篇关于在SQL查询的WHERE条件中多次使用相同的参数以在JDBC中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
