Need help to write where clause

Hi I am developing a program to search certain fields from database table using SQL query.
The problem is that he number of search field being entered from front end JSP is not fixed. The
user can decide to enter any number of fields in search criteria varying from 1 to 6 fields.
How should i write the logic to form the where condition of my query in best possible way.
I am using JDBCHelper.

I do not understand the structure of your sql table, so it will be hard for me to tell which field to search for and what result to be displayed. As it stands at the moment, any advice on the 'where clause' will be hard to give.

I need to extract order history from table ORDERS and ADDRESS in websphere commerce using search criteria such as orders_id,emailaddress,firstname,lastname,address_ id,phonenumber,orderDate , search fields can be any one or more of the above. User enters these fields through JSP form front end

Quote:

---

Originally Posted by SAR

Hi I am developing a program to search certain fields from database table using SQL query.
The problem is that he number of search field being entered from front end JSP is not fixed. The
user can decide to enter any number of fields in search criteria varying from 1 to 6 fields.
How should i write the logic to form the where condition of my query in best possible way.
I am using JDBCHelper.

---

You have to synthesize your SQL query by fiddling with its String representation; you have to be extremely careful for SQL injection ...

kind regards,

Jos

Yep, build your where clause a bit at a time.

Code:

---

query = "SELECT * FROM table WHERE 1=1"
if (some column has search criteria) then
append " AND column = ?"
do the same for all other columns.

---

Create a PreparedStatement, and then go through the search criteria again and set<datatype>().

Now, this is a lot easier if you have the search criteria in an array, and each criteria consists of the column name, the value, and the datatype.