The correct way to bind variables with a LIKE clause in SQL and Java

I was a bit stumped by this one not long ago so here is an example of correctly binding a value to a LIKE clause inside a SQL statement with Java.

First, however, lets see the incorrect way that does not work...If you define your SQL statement to have the bind value and a LIKE wildcard, the JDBC engine will not be able to process it.

Incorrect - Java SQL String

String sql = "select * from table where my_column like ':1%'";

PreparedStatement stmt = conn.prepareStatement(sql);

stmt.setString(1, "My Value");

The above code throws this kind of exception...

SQL Exception

java.sql.SQLException: Invalid column index

at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5331)

at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5319)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:282)

at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:910)

The correct way to do it is to treat the bind variable for the LIKE statement just as you would any other bind variable. The code then looks like this...

Correct - Java SQL String

String sql = "select * from table where my_column like :1";

PreparedStatement stmt = conn.prepareStatement(sql);

stmt.setString(1, "My Value%");

Note that the SQL string itself does not have the LIKE wildcard. The wildcard is defined when you bind the variable using the setString() method instead.

-i