Wildcard on empty string

Question

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.

I'm not sure what you mean. You may add an example or post some more code. — sailingthoms, Jul 06 '14 at 20:20
so if I understand it correctly, you just have to check whether the strings are empty and only a new `OR` part if there is content in the string. Is this what you want to achieve? — sailingthoms, Jul 06 '14 at 20:27
yes that's what I want to check. i could do a if(book_id.equals("")) in Java but that would be a lot of combinations ( there are other similar queries I need to write that have even more columns) . So I'm trying to ask if there's a way I could do this in mySQL code (or lesser java code) — Ajay Karthik, Jul 06 '14 at 20:31

Bart Friederichs · Accepted Answer · 2014-07-06T20:37:03.063

1

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!).

edited Jul 06 '14 at 20:37

answered Jul 06 '14 at 20:31

Bart Friederichs

33,050
15
95
195

Only 1 of (book_id, title, author_name) has to be non-empty. It could be any one, two being non-empty or all three being non-empty – Ajay Karthik Jul 06 '14 at 20:35
@AjayKarthik I updated my answer to show all of them. – Bart Friederichs Jul 06 '14 at 20:37
@AjayKarthik .. and another small update; let SQL check your variables. – Bart Friederichs Jul 06 '14 at 20:42
I'm sorry I don't understand how do I let "SQL check my variables" ? – Ajay Karthik Jul 06 '14 at 20:51
@AjayKarthik that's what my answer does. The part where is it says `'"+book_id+"' <> ''` for example. – Bart Friederichs Jul 06 '14 at 20:52

score 0 · Answer 2 · answered Jul 06 '14 at 20:28

0

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;

answered Jul 06 '14 at 20:28

fclinton

25
7

I doubt they are `null`, as that would generate a runtime error in the string concatenation. You could would write `if ("".equals(title)) ...`. But that doesn't fix it, as this needs all of them to be not-empty, which is not the case. – Bart Friederichs Jul 06 '14 at 20:33
The strings aren't null, they could by empty of the form ("") or not empty – Ajay Karthik Jul 06 '14 at 20:37

Wildcard on empty string

2 Answers2