Java: How To Use SQL LIKE Clasue with PreparedStatement?

One may find that using SQL’s LIKE clause in Java PreparedStatement is not straight forward. There is no documentation on how the LIKE clause should be used when it is used with the PreparedStatement object. Thus, most of the programmer would rather use the full SQL to execute the statement.

A programmer may also try the following SQL and put it through the PreparedStatement and get a rejection complain while preparing the SQL statement.

SELECT * FROM USER WHERE NAME LIKE '%?%'

The above statement is incorrect. You cannot put the question mark inside the quotes. You must treat the question mark as the whole value as the input. The correct way to define the SQL is to put the question mark without the quotes. For example:

SELECT * FROM USER WHERE NAME LIKE ?

When you need to query a specific patter using LIKE clause, the “pattern” option should be set outside of the SQL itself. For example:

String query = "SELECT * FROM USER WHERE NAME LIKE ?"
PreparedStatement stmt = con.prepareStatement(query);
String value = "John";
stmt.setString(1, "%"+value+"%");
ResultSet rst = stmt.executeQuery();