Sumit Srivastava Sumit Srivastava - 1 month ago 10
SQL Question

Java Prepared Statement with CONTAINS query

I have a search query which must search a column in a table using contains search. There is ctxsys.context type index on the column. While fetching data on the table using prepared statement, the search query is not able to process special characters like -,/,_ etc.

Here is my code -

String query = "select * from parties where contains (party_name ,'%' || ? || '%')>0";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, searchName);


The code works fine for text which doesn't have special characters.

When I run the below query in sqlDeveloper it runs fine .

select * from parties where contains(party_name,'c/o')>0;


Please suggest what changes should I make in the prepared statement to make it work for special characters too.

Answer

Please refer to this question on how to use contains in prepared statement. PreparedStatement with CONTAINS query

You have to use escape in your queries if the above didint work like

SELECT * FROM BIRDS WHERE SPECIES='Williamson's Sapsucker 
statement.executeQuery("SELECT * FROM BIRDS  WHERE SPECIES='Williamson/'s Sapsucker' {escape '/'}");

reference from http://www.jguru.com/faq/view.jsp?EID=8881