Java.sql.SQLException doing a SELECT

Question

When I try to execute this code:

    try {

        pstm = connection.prepareStatement("SELECT * FROM Menu WHERE (menuName LIKE '?%')");
        pstm.setString(1,searchedMenu.getMenuName());
        rs = pstm.executeQuery();
        while (rs.next()) {
            Menu menu=new Menu();
            menu.setMenuId(rs.getInt("menuId"));
            menu.setMenuName(rs.getString("menuName"));
            temp.add(menu);
        }

I get the error in the subject, why? My purpose is to search a string or a part of it. Thank you.

Edit: the error is: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). And it is referred to this line: pstm.setString(1,searchedMenu.getMenuName());

Answer 1

The error is likely from the fact that you don't have any parameter markers to set, because you put the question mark into the string:

menuName LIKE '?%'

So that when you do this:

pstm.setString(1,searchedMenu.getMenuName());

There is nothing to set, and it will throw an error. If you check the stack trace, it's likely that you're seeing a message about the number of parameter markers.

You should concatenate the percent into the string in Java. Concatenating it in the SQL will provide less consistent results, varying by RDBMS.

So I would recommend writing your SQL with a parameter like this:

SELECT * FROM Menu WHERE (menuName LIKE ?)

And then setting the parameter in the java code like this:

pstm.setString(1, searchedMenu.getMenuName() + "%");

Also consider trimming the menu name, if you know there will be spaces. Your results may not be as you expect if you have a percent sign after a bunch of spaces.