Dynamic SQL Query w/ Java

Question

I am writing a program that gets records from a database. I would like to create a dynamic query based on 3 variables (studentID, firstName, and/or lastname). Here is my java code which returns records:

result = statement.executeQuery("SELECT * FROM student "
        + "WHERE (studentID = "
        + getStudentId() + " AND " + getStudentId() + " <> 0)"
        + " OR (firstName = '"
        + getFirstName() + "' AND '" + getFirstName() + "' IS NOT NULL)"
        + " OR (lastName = '"
        + getLastName() + "' AND '" + getLastName() + "' IS NOT NULL)");

What I would like is for the search to return results that are vague to specific based on what variables are present. Currently if the studentID is the only field provided, it returns that single record or if studentID is not present but firstName is, it returns all records by firstName (same for lastName if only variable present). What doesn't work is if I provide firstName and lastName, it returns all records with the firstName regardless of the last name of the record, and all records with lastName regardless of what the first name is. Example firstName = "Bill" and lastName = "Jackson":

1  Bill Hader
2  Steve Jackson
3  Bill Jackson
4  Bill Stewart
5  Denise Jackson
6  Wendy Jackson
7  Bill Matthews

What I am trying to figure out is if I provide specific criteria such as firstName and lastName how to get the one specific record that contains both first name and last name that was specified.

Answer 1

A) This should do the trick:

String query = "SELECT * FROM student WHERE (studentID=? OR ?=0) 
                                        AND (firstName=? OR ? IS NULL)
                                        AND (lastName =? OR ? IS NULL)";
ps.setInt(1,getStudentID());
ps.setString(2,getFirstName());
ps.setString(3,getFirstName());
result ps.executeQuery();

Note that the logic is the opposite of what you have posted. In your code, if studentId is 0 and firstName is null, you have:

(studentID=0 AND 0<>0) OR (firstName='null' OR null IS NOT NULL)

which is false. Instead you need to specify

(studentID=0 OR 0=0) AND (firstName='null' OR null IS NULL)

B) If you want the conditional logic in Java:

String query = "SELECT * FROM student WHERE (";
if (getStudentId()!=0) query+="studentID=? AND";
if (getFirstName()!=null) query+="firstName=? AND";
if (getLastName ()!=null) query+="lastName=?  AND";
query+=" 1=1)";
PreparedStatement ps = connection.prepare(query);
if (getStudentId()!=0)    ps.setInt(1,getStudentID());
if (getFirstName()!=null) ps.setString(2,getFirstName());
if (getLastName ()!=null) ps.setString(3,getFirstName());
result ps.executeQuery();

Answer 2

So, assuming your criteria are 0, 'Bill', and 'Jackson', this is the query you will be executing:

select *
from student
where 
   (studentID = 0 and 0 <> 0)
or (firstName = 'Bill' and 'Bill' is not null)
or (lastName = 'Jackson' and 'Jackson' is not null)

There are three conditions in your where clause. The first (again, assuming you've entered 0) will match nothing. The second will match all records whose firstName is 'Bill'. The third will match all records whose lastName is 'Jackson'. Because your conditions are joined by or operators, the resulting set (the query results) will be the union of all matched sets, so any record whose firstName is 'Bill' or whose lastName is 'Jackson', hence all your sample records. If you want to restrict this to just one first name and last name, change your or to an and and simplify the query:

select *
from student
where 
    firstName = 'Bill' 
and lastName = 'Jackson'

You seem to be trying to build too much logic into a single where clause. Now, how can this be done? Let's leave the studentID field alone for a minute, so we only want to match the exact first name (if one is supplied) and the exact last name if one is supplied. We can do that using this:

select *
from student
where
    (firstName = ? or ? is null)
and (lastName =  ? or ? is null)

Finally, the other condition you appear to want is that if a student ID is supplied we restrict the results to only that ID. We can do that by using this:

select *
from student
where
    (studentID = ? and ? <> 0)
or (
      (firstName = ? or ? is null)
  and (lastName =  ? or ? is null)
)

For simplicities sake, this assumes that we always return the row matching a given student ID, even if the first name or last name does not match. (So running with parameters 1, 'Bill', 'Jackson' will result in two records). If you instead want to use the studentID to remove any records that do not match all three criteria, change the query to this:

select *
from student
where
    (studentID = ? or ? = 0)
and  (
      (firstName = ? or ? is null)
  and (lastName = ? or ? is null)
)

Here's a SQLFiddle to show how it works.

As others have mentioned, please learn how to use parameterized queries in Java. It's not hard.

Answer 3

Using a query builder API as illustrated in this question might be the best way to accomplish complex dynamic SQL. Popular APIs for constructing dynamic SQL are:

• jOOQ
• QueryDSL

Popular APIs for constructing dynamic JPQL are:

• JPA Criteria API

An example using jOOQ for your question would be:

Condition condition = DSL.trueCondition();
if (getStudentId() != 0)
    condition = condition.and(STUDENT.STUDENTID.eq(getStudentId()));
if (getFirstName() != null)
    condition = condition.and(STUDENT.FIRSTNAME.eq(getFirstName()));
if (getLastName() != null)
    condition = condition.and(STUDENT.LASTNAME.eq(getLastName()));

Result<StudentRecord> result =
DSL.using(configuration)
   .selectFrom(STUDENT)
   .where(condition);

(Disclaimer: I work for the company behind jOOQ)