CODEWITHSUNDEEP
javasqlpostgresql
maggotypeach
maggotypeach

Reputation: 49

OR sql query doesnt work in java system-searching for data in column

I have a java class that is supposed to display a table. So i want to search FROM table called Film the filmtitle and the film genre but the OR query doesn't work? any idea how to fix this?

heres my code btw

public void searchbyTitle()
{
    String sql = "SELECT filmtitle, filmyear, filmgenre, companyname FROM production, film, 
company  WHERE LOWER(filmtitle) like LOWER
 (?) OR LOWER(filmgenre) like LOWER (?) and production.companyid = company.companyid AND production.filmid = film.filmid";
    con = DBConnect.getConnection();

    try
    {
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1, "%" +txtSearch.getText()+"%");
        ResultSet rs = pst.executeQuery();
        table.setModel(DbUtils.resultSetToTableModel(rs));

    }catch(SQLException e)
    {
        e.printStackTrace();
    }


}

it only works if i remove the or,but i also want to be able to search for film genre LOWER is also what i used so that it will be case insensitive also used postgresql..any help is appreciated

Upvotes: 1

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

The logic is doing exactly what you are telling MySQL to do. It is just not what you intend.

If you are having trouble with conditional expressions, then use parentheses to explicitly do what you want:

WHERE (LOWER(filmtitle) like LOWER (?) OR
       LOWER(filmgenre) like LOWER (?)
      ) and
      production.companyid = company.companyid AND production.filmid = film.filmid;

Ouch! I notice that you have commas in the FROM clause. If you had used explicit JOIN syntax, your query would have been correct to begin with. The correct way to write the query is:

SELECT filmtitle, filmyear, filmgenre, companyname
FROM production p JOIN
     film f
     ON p.filmid = f.filmid JOIN
     company c
     ON p.companyid = c.companyid
WHERE LOWER(filmtitle) like LOWER(?) OR LOWER(filmgenre) like LOWER(?);

Notice the parentheses are no longer needed in the WHERE, because there is no AND competing with the OR.

Simple rule: Never use commas in the FROM clause. Always use proper, explicit JOIN syntax. It would have saved you a lot of time today.

Upvotes: 1

Related Questions