Java上的PreparedStatement和设置参数的可能性 [英] PreparedStatement on Java and probability to set parameters

问题描述

有一种在Java中使用预处理语句的好方法，在这种情况下，是否有可能设置或不设置某些参数的可能性?

There is a good way to use a prepared statement in java where there is the possibility to have a probability that some parameter can be or not be set?

假设我有一个完整的查询，例如:

Let's assume I have a complete query like this one:

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

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

Then in my program I would like to do something like that (I know it's not possible the way I write)

// 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);   

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

How can I achieve it without write many different queries?

推荐答案

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)

Or you can write a "NamedPreparedStatement" like I did. (https://gist.github.com/1978317)

这篇关于Java上的PreparedStatement和设置参数的可能性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！