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.