CODEWITHSUNDEEP
javamysql
Ajay Karthik
Ajay Karthik

Reputation: 65

Wildcard on empty string

I am creating a Java program that interfaces with a mysql database for a school project: I am using java mysql connector to execute the following query when the user hits the search button through a GUI:

public ResultSet getBooks(String book_id,String title,String author)
{
    ResultSet rs = null;
    try{
        Statement stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT book_id,title,author_name,branch_id,no_of_copies   FROM "
                + "(temp_author NATURAL JOIN book_copies) "
                + "WHERE book_id like '%"+book_id+"%' OR title like '%"+title+"%' OR author_name like '%"+author+"%';");

    }
    catch(SQLException ex) {
        System.out.println("Error in connection: " + ex.getMessage());
    }
    return rs;
}

The problem with this query is that if any of the fields (book ID , title or author) are empty, the query returns true as result. So my question is how do I use the wildcard to search for a matching string but return a null set when the strings (boook_id,title or author) are empty.

Thank you.

PS: I wasn't able to post an image of the GUI due to my low reputation.

Upvotes: 0

Views: 1952

Answers (2)

Bart Friederichs
Bart Friederichs

Reputation: 33521

Assuming your book_id String is not null, you could this:

WHERE ('"+book_id+"' <> '' AND book_id like '%"+book_id+"%') OR 
      ('"+title+"' <> '' AND title like '%"+title+"%') OR 
      ('"+author_name+"' <> '' AND author_name like '%"+author+"%')

I recommend you to use prepared statements also (for safety!).

Upvotes: 1

fclinton
fclinton

Reputation: 25

Just do something like this and include and statements with the rest of your arguments

If (isNull(title)&&isNull(author)&&isNull(book_id)) return null;

Upvotes: 0

Related Questions