I want to write a mysql select query using preparedstatement. But theres syntax error at the last part which is concat('%', itemName, '%')"; itemName is a column of table ItemMain.
I already tried 3 queries given below.
String sql ="SELECT * FROM ItemMain WHERE ? = 'All' OR ? like concat('%', itemName, '%')";
String sql ="SELECT * FROM ItemMain WHERE ? = 'All' OR ? like '%'+itemName+'%'";
String sql ="SELECT * FROM ItemMain WHERE ? = 'All' OR ? like '%itemName%'";
You can't use placeholders for field names. The queries would have to be
... WHERE somefield=? OR otherfield LIKE concat('%', ?, '%')
placeholders are for VALUES only. field/table names, function namesm or any of the "Structural" words in SQL are offlimits.
This is a general rule for mysql prepared statements. It is not a java/php/c#/whatever restriction.
Although @Marc B is absolutely right (+1) I would like to add something. I believe that your have a real task where you need such functionality, so I would like to suggest you the following solution.
You can create query dynamically as following. If you are using plain JDBC you can run query like desc YOUR_TABLE_NAME. It will return a easy-to-parse list of fields in your table. You can implement your "like" statement yourself either using regular expression or simple string manipulation methods as startsWith("xyz") instead of like 'xyz%', endsWith("xyz") instead of like '%xyz' and contains("xyz") instead of like '%xyz%'. Now you can create SQL statement dynamically by adding fields the meet your requirements.
Found the answer to my problem.
String sql ="SELECT * FROM ItemMain WHERE ? = 'All' OR itemName like '%"+keyword+"%'";
Object []values ={keyword};
ResultSet res = DBHandller.getData(sql, conn, values);
I swapped the column name, keyword and change the syntax here '%"+keyword+"%'"; Now it works fine. thnx al