PreparedStatement on Java and probability to set p

Question

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?

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. 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)

Answer 2

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.

Answer 3

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.)