java - Java上的PreparedStatement以及设置参数的概率

Question

有一个在java中使用准备好的语句的好方法，其中有可能有可能设置或不设置某些参数？

假设我有一个像这样的完整查询:

SELECT * FROM Table1 WHERE Field1='....' AND Field2='....' AND Field3='....'

然后在我的程序中我想做类似的事情(我知道我写的方式是不可能的)

// part of code where I have 3 variable set by some logic and the query itself
//.........

String Query = "SELECT * FROM Table1 WHERE Field1=? ";
PreparedStatement s = conn.prepareStatement();          
s.setString(1, Field1Var);

if (Field2Var != Value)
{
    Query += " AND Field2=? ";
    s.setString(2, Field2Var);
}

if (Field3Var != Value3)
{
    Query += " AND Field3=? ";
    s.setString(3, Field3Var);
}
s = conn.prepareStatement(query);   

如何在不编写许多不同查询的情况下实现它？

Answer 1

String query = "SELECT * FROM Table1 WHERE 1=1";

if (Field1Var != Value1) {
    query += " AND Field1 = ? ";
}

if (Field2Var != Value2) {
    query += " AND Field2 = ? ";
}

if (Field3Var != Value3) {
    query += " AND Field3 = ? ";
}

PreparedStatement s = conn.prepareStatement(query); 

int i = 0;

if (Field1Var != Value1) {
    s.setString(++i, Field1Var);
}

if (Field2Var != Value2) {
    s.setString(++i, Field2Var);
}

if (Field3Var != Value3) {
    s.setString(++i, Field3Var);
}

1. 使用附加条件 1=1。
2. 使用计数器变量。

或者你可以像我一样写一个“NamedPreparedStatement”。 (https://gist.github.com/1978317)