PreparedStatement on Java and probability to set p

问题:

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?

回答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. Use an additional condition 1=1.
2. Use a counter variable.

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

回答2:

You can't. The whole point of a prepared statement is that it is "prepared" - which means it is parsed (either by the driver, or by the server) and all you do with each run is supply the parameters.

The code you are trying to write is also going to be a nightmare for anyone else to follow. It would be much cleaner if you just sucked it up and wrote the queries. (Or used an ORM tool.)

回答3:

Another solution is to use "more object oriented approach" using Hibernate Criteria API that allows you to create queries dynamically ( typical use is search query with many parameters), but i dont know what technology stack you use.