CODEWITHSUNDEEP
javamysqlprepared-statementsql-like
amal
amal

Reputation: 3582

Using MySQL like for Column name

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%'";

Upvotes: 0

Views: 1864

Answers (3)

amal
amal

Reputation: 3582

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

Upvotes: 0

AlexR
AlexR

Reputation: 115328

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.

Upvotes: 1

Marc B
Marc B

Reputation: 360572

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.

Upvotes: 5

Related Questions