Preparedstatement with like where clause and the field contain %, how to escape?

[peter tong]

e.g. the filedA contain a value "test%2"

then in preparedstatement,

PrepareStatement ps;
ps = " select * from tableA where fieldA like ? ";
ps.setString(1, 'xxx');

how to pass the 'xxx' so it can retrieve all field value with leading 'test%'? (that is, escape the % after test)

[Martin Vajsar]

According to this document (see the bottom of the page), you can define your own escape character:

PrepareStatement ps;  
ps = " select * from tableA where fieldA like ? {escape '!'}";  
ps.setString(1, 'test!%');  

Apparently the exact syntax for specifying the escape character differs among databases and JDBC provides this mechanism to abstract from these differences. I've never used that myself, though; I don't need database agnostic code and therefore always use the native syntax of my database.

You can use whatever escape character you wish. I didn't go for the obvious backslash, since it has to be escaped in Java String literals themselves, which makes somewhat less obvious what is actually happening.