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

Question

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

Gordon Linoff · Accepted Answer · 2016-07-10 16:36:38Z

1

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.

edited Jul 10, 2016 at 16:36

answered Jul 10, 2016 at 16:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

5 Comments

Andreas Over a year ago

Yeah, so many people confuse the precedence of OR vs AND, so rule of thumb: Always use parenthesis when mixing OR and AND, even if you understand the precedence rules. --- Even though I just used "always", saying "never use comma" is a bit extreme, but a good rule of thumb. ;-)

maggotypeach Over a year ago

thanks for answering but an error still pops if it helps...it looks like this No value specified for parameter 1. at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:216) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:244) at

maggotypeach Over a year ago

org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at film.FilmIntro.searchbyTitle(FilmIntro.java:111) at film.FilmIntro$4.keyReleased(FilmIntro.java:196)

Rémy Baron Over a year ago

In your request you are using two ? so you have to define two parameters in your java code : pst.setString(...

Rémy Baron Over a year ago

That can be same value : pst.setString(1, "%" +txtSearch.getText()+"%"); pst.setString(2, "%" +txtSearch.getText()+"%");

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related