This week's book giveaway is in the OCMJEA forum. We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line! See this thread for details.
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.
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.
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.
SCJP 6, OCMJD 6, OCPJWSD 6
I no good English.
Joined: Mar 22, 2008
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.