Results 1 to 5 of 5
  1. #1
    SAR
    SAR is offline Member
    Join Date
    Jun 2010
    Posts
    7
    Rep Power
    0

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

  2. #2
    Menre is offline Member
    Join Date
    Feb 2008
    Posts
    79
    Rep Power
    0

    Default

    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.

  3. #3
    SAR
    SAR is offline Member
    Join Date
    Jun 2010
    Posts
    7
    Rep Power
    0

    Default

    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

  4. #4
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,773
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by SAR View Post
    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

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    Yep, build your where clause a bit at a time.
    Java 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.

Similar Threads

  1. finally clause
    By arefeh in forum New To Java
    Replies: 6
    Last Post: 01-30-2010, 10:11 PM
  2. Mysql LIKE clause problem
    By stalkerism in forum JDBC
    Replies: 1
    Last Post: 07-31-2009, 11:01 AM
  3. Replies: 0
    Last Post: 04-17-2008, 12:07 AM
  4. Using escape sequence with like clause (%)
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-14-2008, 10:57 AM
  5. Using escape sequence with like clause (_)
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-14-2008, 10:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •