Sqlite: How to insert variable into string in prepared statement

Question

I'm using a Java wrapper for accessing Sqlite but I assume this is a general Sqlite question.

String stmt = "SELECT foo FROM bah WHERE foo='%/?/%';
PreparedStatement a = myConn.prepareStatement(stmt);

a.setString(1, "hello");
a.executeQuery();

... throws an exception - it doesn't like the ? being inside quotes. Everything is fine if I do

...WHERE foo=?

but this isn't the statement I want.

How can I insert a variable into such a prepared statement? If you forget about the fact I'm using Sqlite, how is this is done using other database technologies?

Answer 1

String stmt = "SELECT foo FROM bah WHERE foo= ?"

and then

a.setString(1, "%hello%");

Answer 2

No database I've encountered will let you put a parameter inside a string literal. If this was supported, then you'd have to escape every ? character in a string that wasn't a parameter.

You can, however, use string concatenation to support what you are after:

SELECT foo FROM bah WHERE foo = '%/' || ? || '/%'