trying to get a query to use the LIKE function with a variable in JSP

Question

I have a query that is being used to pull usernames and info about the user. In Access I had the LIKE function so that the user didn't have to type in a specific name. I am now transferring it over to JSP. Here is the line in the query that I am having troubles with in JSP:

WHERE ObjectName Like '" + "%"+ VariableName + "%" +"';

The query runs fine but does not show any information even if I put in the entire name. If I change it to:

WHERE ObjectName = '" + VariableName +"';

it works, but I would like to give the user a chance to have to ability to put in partial names in case they do not know how to spell the name or typ eit incorrectly. Any help would be apprecited.

Thanks

Answer 1

1. Avoid writing SQL queries in JSP
2. "SELECT * FROM something WHERE ObectName LIKE '%" + VariableName + "%'" should work

Answer 2

The line you've shown is a bit odd, but syntactically valid. So the problem lies somewhere else. What does variableName actually contain?

That said, you shouldn't be writing raw Java code in JSP files. Do that in a Java class. You can use a Servlet class to preprocess or postprocess requests. Also grab PreparedStatement to avoid SQL injections. Here's a kickoff example:

public List<User> search(String username) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<User> users = new ArrayList<User>();

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, username, age, email FROM user WHERE username LIKE ?");
        statement.setString(1, "%" + username + "%");
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            users.add(mapUser(resultSet));
        }
    } finally {
        close(connection, statement, resultSet);
    }

    return users;
}