Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

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

Use of any information contained in this blog post/article is subject to this disclaimer.
comments powered by Disqus
Other posts you may like...