aspose file tools*
The moose likes JDBC and the fly likes Search Query Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Search Query Problem" Watch "Search Query Problem" New topic
Author

Search Query Problem

Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 732



you can see that 2 records exists with lastName "Hello" but it displayed all existing records.

when am running this statement then it displays correct record



but this statement become wrong when I ran with firstName


This query returns two record whereas that should be one record

Now I tried this but throwing exception my java code.


EXCEPTION IS:
==========


Thanks & best regards
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1777
    
  16

What are you trying to achieve here? Your SQL is doing exactly what you are asking it to do.

Your first SELECT is looking for records which match ANY of the individual OR ... clauses, which is why you get all the records back, because they all match at least one of these OR clauses.

The second SELECT basically looks for records which have LASTNAME = 'Hello' OR [one of the other fields is empty]. You have two records with last name 'Hello', and no records where the other fields are empty, so you get 2 records back.

The third SELECT is looking for records with LASTNAME = 'Hello' OR FIRSTNAME = 'Test' OR [one of the other fields is empty]. You have two records with last name 'Hello', and one of these also has first name 'Test', and you still have no records where the other fields are empty, so you get the same 2 records back.

As for your final example, I can't tell what you're doing here, but your "if (type.equals("3"))" statement means you could have 5 or 6 bind variables in the SQL, so is it going to fall over if the type is "3" and you try to set 6 bind variables?

Here are some very simple rules to follow every time you want to run some SQL from Java:

  • Write the SQL separately.
  • Test it against your database directly via the SQL interpreter e.g. SQL*Plus in Oracle.
  • When you are sure it is working correctly, write the Java to build the corresponding query string.
  • Take the query string you just built in Java and run that via your SQL interpreter to make sure it still does what you want.


  • But of course, the first thing to do is figure out what you want your query to do.


    No more Blub for me, thank you, Vicar.
    Martin Vanyavchich
    Ranch Hand

    Joined: Sep 16, 2008
    Posts: 241
    You have some logical mistakes in your SQL statements



    I think you should substitute OR with AND to get the desired effect or even remove the lines with wildcards alltogether.



    You can do the same for your other problematic statement.



    There is no need to list all parameters, just the ones you need to narrow the results:



    This will return just one entry, the first one you added to the table with patient_id 1. If you were to alter the statement and use OR in such a way:


    Two entries would be returned.
    (1,1,'Test','Hello','test@test.com',5); and
    (2,1,'Testing','Hello','test@test.com',4)

    the first one matches the firstName equality and the second one the lastName equality.


    As for your PreparedStatement error, you have to be careful of the types that you're setting to replace your placeholders. Title, and nationality are numbers, and you should be using setInt() method. I think it should look something like this:


    Be careful counting the '?' and what values you assign.


    Hope this helped you.


    Martin


    SCJP 6, OCMJD 6, OCPJWSD 6
    I no good English.
    Farakh khan
    Ranch Hand

    Joined: Mar 22, 2008
    Posts: 732
    Thanks webster and Martin.

    The actual problem is:

    My Advance Search Form contains title, first name, last name, email and nationality. Now the code not knows either all the parameters will be received or some of them or just one. In this case the problem arises to handle the SQL query. I used all queries listed above in my first post but none of them is fulfilling my requirement.

    if I am using "OR" in the SQL query then its displaying all the records if I am using "AND" then query displays none. The answer I received from Martin and webster is also not telling me the solution. I am confused a lot as to how I deal with it.

    What I actually want is to get patients Ids by setting query on the basis of search parameters.

    Thanks & best regards
    chris webster
    Bartender

    Joined: Mar 01, 2009
    Posts: 1777
        
      16

    WHERE title LIKE NVL(?,'%')
    AND firstName LIKE lower(NVL(?,'%'))
    AND lastName LIKE lower(NVL(?,'%'))
    AND email LIKE NVL(?,'%')
    AND nationality LIKE NVL(?,'%')

    Make sure you match the correct number of Java bind variables to the "?" in your SQL.
    Farakh khan
    Ranch Hand

    Joined: Mar 22, 2008
    Posts: 732
    Hello,

    Sorry for the late reply as I was still working to fix the problem.


    The above statement is displaying correct results because one field "firstName" retrieved. When I check two fields then record exists but it showing no results



    In the following query I put nothing as firstName and correct lastName. It must show correct results but displaying no results


    The actual problem remains

    Thanks & best regards
    chris webster
    Bartender

    Joined: Mar 01, 2009
    Posts: 1777
        
      16

    You are doing a LOWER(...) on your column value, but comparing it to a mixed-case literal value such as "Hello".
    Farakh khan
    Ranch Hand

    Joined: Mar 22, 2008
    Posts: 732
    Thanks Chris Webster from the bottom of my heart. Now its working fine.

    Best regards
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Search Query Problem