unable to execute "like" query in java

Question

In MYSQL database I have a table name search. When I write this query it executed successfully.

"select * from 'search' where path like %overview%"

But when I write it in java:

String query="SELECT path FROM `search` WHERE path like %?% ";
java.sql.PreparedStatement st=con.prepareStatement(query);
st.setString(1,textField.getText() );
ResultSet rs=st.executeQuery();

It's not working and displays an error:

com.mysql.jdbc.exception.jdbc4.MySQLSystaxErrorException: you have an error in sql syntax; check the manual that corresponds to your Mysql server version for the right Syntax to use near '%'overview'%' at line 1

What error? And why the grave accents instead of single quotes? — Mena, Apr 30 '15 at 13:09
i dont have 10 reputation so i cant add picture with that so i need to write that error: — Rakibul Hasan, Apr 30 '15 at 13:12
http://stackoverflow.com/questions/8247970/using-like-wildcard-in-prepared-statement this link will be useful — Vigneshwaran, Apr 30 '15 at 13:14
the error is: com.mysql.jdbc.exception.jdbc4.MySQLSystaxErrorException: you have an error in sql syntax; check the manual that corresponds to your Mysql server version for the right Syntax to use near '%'overview'%' at line 1. — Rakibul Hasan, Apr 30 '15 at 13:15

score 4 · Accepted Answer · edited Apr 30 '15 at 13:18

4

String query="SELECT path FROM `search` WHERE path like ? ";
java.sql.PreparedStatement st=con.prepareStatement(query);
st.setString(1,"%"+ textField.getText() + "%" );
ResultSet rs=st.executeQuery();

You can try the above code

edited Apr 30 '15 at 13:18

MrTux

32,350
30
109
146

answered Apr 30 '15 at 13:17

Vigneshwaran

387
1
2
14

score 0 · Answer 2 · answered Apr 30 '15 at 13:59

The percent signs are literals, if you want to include those in the SQL text, and have MySQL add those to the value you pass in, you could do something like this:

String query="SELECT path FROM `search` WHERE path LIKE CONCAT('%',?,'%')";

The other alternative is to remove the percent signs from the SQL text statement

String query="SELECT path FROM `search` WHERE path LIKE ?";

and then prepend/append percent characters to the value you supply.

unable to execute "like" query in java

2 Answers2