Passing a blank / non-null VARIABLE in MYSQL Query (using Java)

Question

So I am working on a small program in Java. Basically I want the user to search a database with one or 1-3 search query and they can use any combination.

A search query example would be: Book Name, ISBN, Author Name

The user can search with any combination (Search by book name only, or book name and author name, or all 3 etc...).

I want to basically write one master SQL statement (MYSQL), that would pull the results from the database. The problem is, I don't know what to do with a blank input.

So say user enters:

Book name = Harry Potter and the Chamber of Secrets
ISBN = << left blank>>
Author Name = JK Rowling

An example SQL query would be:

SELECT *
FROM booklist
WHERE book_name = "Harry Potter and the Chamber of Secrets"
AND ISBN = ""
AND Author = "JK Rowling";

In Java code I am using PreparedStatement, and the String would be:

String temp =  " SELECT * " + 
               " FROM booklist " + 
               " WHERE Title = " + title + 
               " AND Author = \"" + author + "\"" + 
               " AND ISBN = \"" + isbn + "\"";

Because the user did not enter ISBN, the SQL query fails (hence code fails). If I use null instead of "", then the query will still fail.

By fail meaning that it will not find the correct book in database (even though its present), as it is still looking for "" or null in the column.

Is there any way to pass kind of like a 'ghost' or invisible variable to get the SQL query to work like I am intending it to?

Or do I have to go the long way and make a SQL query for each possible combination?

You need to build your WHERE clause on-the-fly excluding columns that users leave blank. — PM 77-1
– PM 77-1, Commented Apr 8, 2014 at 2:25

Ravinder Reddy · Accepted Answer · 2014-04-08 02:55:45Z

1

Use OR instead of AND

Change:

String temp =  " SELECT * " + 
               " FROM booklist " + 
               " WHERE Title = " + title + 
               " AND Author = \"" + author + "\"" + 
               " AND ISBN = \"" + isbn + "\"";

To:

String temp =  " SELECT * FROM booklist " + 
               " WHERE Title = ? OR Author = ?" + 
               " OR ISBN = ?";

And then set the parameters for prepared statement.

pst.setString( 1, title );  
pst.setString( 2, author );
pst.setString( 3, isbn );

With the OR if any of the matching records are found then they are fetched.

And, if you still want to use AND for comparison and want to not include empty inputs then you have to dynamically prepare the statement in JAVA.

StringBuilder sql = new StringBuilder( 1024 );
sql.append( " SELECT * FROM booklist " );
String whereCondition = "";
if( title != null && title.trim().length() > 0 ) {
    whereCondition += " title = ?";
}
if( isbn != null && isbn.trim().length() > 0 ) {
    whereCondition += (whereCondition.length() > 0 ? " AND " : "" );
    whereCondition += " isbn = ?";
}
if( author != null && author.trim().length() > 0 ) {
    whereCondition += (whereCondition.length() > 0 ? " AND " : "" );
    whereCondition += " author = ?";
}

sql.append( " where " ).append( whereCondition );
pst = con.prepareStatement( sql.toString() );

Now set the prepared parameters like this:
And then set the parameters for prepared statement.

int paramIndex = 1;

if( title != null && title.trim().length() > 0 ) {
    pst.setString( paramIndex++, title );  
}
if( isbn != null && isbn.trim().length() > 0 ) {
    pst.setString( paramIndex++, isbn );  
}
if( author != null && author.trim().length() > 0 ) {
    pst.setString( paramIndex++, author );  
}

Now, you can execute the statement and fetch the resultset.

edited Apr 8, 2014 at 2:55

answered Apr 8, 2014 at 2:40

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1861967 Over a year ago

Thanks this is great! I totally understand what your doing. My only question is I dont understand your last block of code and what your doing with the prepared statements? What is the purpose of doing pst.setString( paramIndex++, title )?

Ravinder Reddy Over a year ago

@user1861967: Using PreparedStatement, we can set query parameter values. It internally escapes any special characters in the values, that are not allowed, unless are escaped. And it also prevents SQL injection. And unless you set... values, they are not passed to database and execution would through an SQLException.

Collectives™ on Stack Overflow

Passing a blank / non-null VARIABLE in MYSQL Query (using Java)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related