CODEWITHSUNDEEP
javamysqlsql
user1861967
user1861967

Reputation: 121

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

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:

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?

Upvotes: 0

Views: 1610

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

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.

Upvotes: 1

Related Questions