Is it possible to use prepared statement placeholders for LIKE?

Question

This fails:
db.prepareStatement("SELECT * FROM " + table + " WHERE " + column + " LIKE '?%'");

Because the ? is not recognized as a placeholder for a value. How should I work around this?

femtoRgon · Accepted Answer · 2012-12-28T20:32:18.690

7

Put the wildcard in the variable, rather than in the statement, like:

stmt = db.prepareStatement("SELECT * FROM " + table + " WHERE " + column + " LIKE ?");
stmt.setString(1, "myterm%");

edited Dec 28 '12 at 20:32

answered Dec 28 '12 at 20:25

femtoRgon

32,893
7
60
87

That would only work if the code around it knew it was going to be used for a LIKE, which would be a poor design decision as it binds the variable to only those statements that use like – Bohemian Dec 28 '12 at 21:04

Bohemian · Answer 2 · 2012-12-28T21:20:55.190

1

Pass your value into the CONCAT() function:

db.prepareStatement("SELECT * FROM " + table 
    + " WHERE " + column 
  + " LIKE CONCAT(?, '%')");

The advantage of doing it this way is the code making the call doesn't need to know that the parameter is being used with a LIKE, so you could use the same parameter value with other non-LIKE queries.

edited Dec 28 '12 at 21:20

answered Dec 28 '12 at 20:47

Bohemian

412,405
93
575
722

score 0 · Answer 3 · answered Dec 28 '12 at 20:27

Try including the percent sign as part of the LIKE parameter:

db.prepareStatement("SELECT * FROM " + table + " WHERE " + column + " LIKE ?");

I don't like the query much. I don't think you're saving much by concatenating table and column in this way.

Is it possible to use prepared statement placeholders for LIKE?

3 Answers3