Results 1 to 6 of 6
  1. #1
    ismet is offline Member
    Join Date
    Mar 2011
    Posts
    3
    Rep Power
    0

    Default Multi field search query form

    Hi

    Appreciate if anyone can help me to solve the multi field search query from the following search form:

    Field 1 = text field1
    Field 2 = text field2
    Field 3 = text field3

    Field 4 = combobox1 and combobox2 (user can fill either one of this)

    Field 5 = combobox3 and combo box4 and combobox5 (user can fill either one of this)


    Please note that:

    a. user can enter at least one variable from this form.
    b. user can fill all the variables from this form.
    c. Field 1, field 2 and field 3 is 'or' condition.
    d. field 4 and field 5 is 'and' condition.

    Example: if user fill in field 2, field 3 and field 5 combobox4, the sql query should be: select * from myTable where field 2 like '%textfield2%' or field 3 like '%textfield2%' and field 5 = 'combobox4';

    Appreciate if anyone can construct the java code for this so that I can have a single query according to user input. Thank you.

    Ismet

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,371
    Blog Entries
    1
    Rep Power
    20

    Default

    What you have done so far?

    Keep in mind that this is a forum, not a place to get your work done. If you put some effort only we can help you.

  3. #3
    ismet is offline Member
    Join Date
    Mar 2011
    Posts
    3
    Rep Power
    0

    Default

    Hi

    At the moment, what I did was:

    1. read each field whether it is empty or not.
    2. if it is not empty then I will take the value from the text field or combobox and create the query.
    However, this will make the conditions grow bigger as user starts to play around with the form.

    Example:
    String query = "select * from myTable where ";

    if (!Field 1.isEmpty()){
    query += Field 1 + " like '%" + text field1 + "%'";
    }

    if (!Field 1.isEmpty() && !Field 2.isEmpty()){

    query += Field 1 + " like '%" + text field1 + "%' OR " Field 2 + " like '%" + text field2 + "%';

    }

    If considering the below examples, the if statement above will grow and grow further.

    Scenario examples:
    1. user may select Field 1 only
    2. user may select Field 2 only
    3. user may select Field 3 only
    4. user may select Field 4, combobox1 only
    5. user may select Field 4, combobox2 only
    6. user may select Field 5, combobox3 only ... and so on.
    * This is just if user select one variable only.

    That is not counting the combination of user to select from the form. Example:
    1. user may select Field 1 and Field 2 only
    2. user may select Field 1 and Field 3 only
    * this is for condition of selecting of two variables. If the user selecting 3 or 4 or 5 variables, then the if statement will further grow bigger and bigger.

    I've been thinking about this for quite some time and still unable to resolve this. Therefore, if anybody had gone through this problem before, I hope you can share. Thank you.

    Ismet

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

    Default

    Loop through the attributes in the request, building a Map of column_name to attribute value. You'll want a Map of attribute name to column name if they don't match.

    Pass this column_name to attribute value Map into the query method.

    That method will loop round the keys of the Map, appending to the query string.

    And if you're clever you can even use a PreparedStatement. And maybe even expand it to include datatypes other than String.

  5. #5
    ismet is offline Member
    Join Date
    Mar 2011
    Posts
    3
    Rep Power
    0

    Default

    Hi,

    Sorry, I am not quite get what you are trying to explain. Can you illustrate using an example?. Thanks.

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

    Default

    In the servlet:
    Java Code:
    private static final Map<String,String> parameterToColumnMap;
    static {
    // Initialise the above in here.
    parameterToColumnMap= new HashMap<String, String>();
    parameterToColumnMap.add("Attribute_Name1", "Column_Name1");
    parameterToColumnMap.add("Attribute_Name2", "Column_Name2");
    parameterToColumnMap.add("Attribute_Name3", "Column_Name3");
    // There are better ways to do this, but I don't fancy attempting to explain them here.
    }
    
    // In your get or post method
    Map<String, String> columnToValueMap = new HashMap<String, String>();
    for each key in the parameterToColumnMap
        columnToValueMap.add(parameterToColumnMap.get(key), request.getParameter(key));
    So that gives you a map between the data supplied from the form and the columns in your db.
    Pass that into your db class and do this:
    Java Code:
    String sql = "select * from my_table"
    if columnToValueMap isn't empty
        sql += " where ";
        for each key in the map
            sql += key + " like \"%" + columnToValueMap.get(key) + "%\"";
    That's the idea, though missing the bit about how to deal with "AND". You shouldn't concatenate and should use a PreparedStatement. But I'll leave that as exercise for you.

Similar Threads

  1. Replies: 0
    Last Post: 01-29-2011, 10:13 PM
  2. Replies: 1
    Last Post: 04-26-2010, 02:02 PM
  3. Get Field value from form
    By johnven in forum Java Servlet
    Replies: 0
    Last Post: 03-10-2010, 08:34 AM
  4. Search text field and combo box
    By Allgorythm in forum New To Java
    Replies: 2
    Last Post: 02-12-2010, 06:15 AM
  5. Search field in the browser
    By sandeeprao.techno in forum Advanced Java
    Replies: 4
    Last Post: 06-07-2008, 08:37 AM

Tags for this Thread

Posting Permissions

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