how to use prepareStatement
Hi ,
I am trying to use prepareStatement to boost up my database queries. To provide maximum flexibility to user, i need 4 queries like this:
1/SELECT * FROM mydb.mytable WHERE coumn1 < 123;
2/SELECT * FROM mydb.mytable WHERE column2 = 'ABC';
3/SELECT * FROM mydb.mytable WHERE column1 >234 AND column1 < 500;
4/SELECT * FROM mydb.mytable WHERE column1 < 234 OR column1 > 500;
Thus, i want to simplify all these 4 into 1 prepareStatement:
String sql = "SELECT * FROM mydb.mytable WHERE ? ? ? ? ? ? ? ";
and then depend on what the user pick from the interface, i can bind it to the prepareStatement to construct the above statement as:
1/ sql.setString(1, "column1");
sql.setString(2, " < ");
sql.setInt(1, var1);
sql.setString(4,"");//the rest of ? is bind to empty string
.....
results = sql.executeQuery();
2/sql.setString(1, "column2");
sql.setString(2, " = ");
sql.setString(1, var2);
sql.setString(4,"");//the rest of ? is bind to empty string
.....
results = sql.executeQuery();
3/sql.setString(1, "column1");
sql.setString(2, " > ");
sql.setInt(3, var3);
sql.setString(4," AND ");
sql.setString(5, " column1 ");
sql.setString(6, " < ");
sql.setInt(7, var4);
.....
results = sql.executeQuery();
4/ is similar to 3
Now, the question is why none of these work?
If i use 4 prepareStatement just to bind the values only (not with sql keywords and column name) then does it increase memory footprint and cause other effects?
Hope to get some help
Thai
