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
