0

I have an Oracle query:

select *
from part
where part_number like 'PDM%'

This will result in every part with a part_number that starts with "PDM" (Read: https://stackoverflow.com/a/11586236/963076).

I want to execute this query with a Java PreparedStatement so that I can replace "PDM" with any text. So, I created this method:

String sql = "select * \n" +
    "from part \n" +
    "where part_number like '?%' \n";

try(PreparedStatement p_stmt = conn.prepareStatement(sql)) {

    p_stmt.setString(1, "PDM");

    ResultSet results = p_stmt.executeQuery();

    while(results.next()) {
        System.out.println(results.getString("part_number"));
    }
}

However, this results in an error:

java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5321)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5309)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:280)
at [my stack trace]

The problem, I think, has to do with the fact that the PreparedStatement is looking for the question mark ? to be by itself. But I need the % to be present to give me the 'starts with' query logic. So because the % is there it's not finding the ?.

How can I accomplish this?

ryvantage
  • 13,064
  • 15
  • 63
  • 112
  • 1
    Check the duplicate link for your answer. You need to use `LIKE ?` with `?` serving as just a placeholder. Then, bind whatever text you want to it. – Tim Biegeleisen Jul 07 '18 at 15:21

1 Answers1

1

? characters inside string literals are not placeholders, and cannot be bound to. You could, instead, have the ? stand alone and concatenate the % character to it using the || operator:

String sql = "select * \n" +
    "from part \n" +
    "where part_number like ? || '%' \n";
// Here --------------------^
Mureinik
  • 297,002
  • 52
  • 306
  • 350