0

I want to use java prepared statement with LIKE keyword. I'm trying to match a date string. Heres my code.

String summaryChooser = "SELECT * FROM PRODUCT p, CODE_COVERAGE_SUMMARY s WHERE "
                    + " p.product_id = s.product_id AND p.product_id = ? AND s.date LIKE ? "
                    + " ORDER BY s.date DESC LIMIT 1 ";
ps = dbConnection.prepareStatement(summaryChooser);
ps.setInt(1, productsResult.getInt("product_id"));
ps.setString(2,  "%" + date + "%");

It gives below exception.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND s.date LIKE ? ORDER BY s.date DESC LIMIT 1' at line 1

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Pramodya Mendis
  • 686
  • 8
  • 24
  • 6
    Its a bit unusual to use LIKE against a DATE column? What datatype is `s.date` – RiggsFolly Mar 04 '19 at 16:54
  • 2
    I bet that your next statement says `ps.executeQuery(summaryChooser)` and that's wrong. It should be just `ps.executeQuery()`. By passing the SQL text to the `Statement.executeQuery(String sql)` method, when you should be calling the `PreparedStatement.executeQuery()` method, the JDBC driver ignores the "prepared" parts and tries to execute the SQL directly, as-is. It is a common mistake when using `PreparedStatement`. – Andreas Mar 04 '19 at 17:02
  • 1
    Off topic but it is better to use JOIN rather than implicit join using `,` – Joakim Danielson Mar 04 '19 at 17:09
  • Please show the full exception stacktrace and a [mcve] (at minimum include how you execute it). – Mark Rotteveel Mar 04 '19 at 17:09
  • And as @Andreas commented on, also post the when executing the query – Joakim Danielson Mar 04 '19 at 17:10
  • 1
    @MarkRotteveel Would be nice, but not really needed, because the error is coming from the MySQL server, and the error includes the `?` markers, and the only way that happens is if the SQL wasn't "prepared", which is why I'm willing to bet on my "guess". ;-) – Andreas Mar 04 '19 at 17:12
  • 1
    @Andreas You are probably right, and in that case, this would be a good duplicate: [MySQLSyntaxErrorException near “?” when trying to execute PreparedStatement](https://stackoverflow.com/questions/4131092/mysqlsyntaxerrorexception-near-when-trying-to-execute-preparedstatement) – Mark Rotteveel Mar 04 '19 at 17:14
  • @RiggsFolly Here I'm trying to get any record for the date given. s.date field is DateTime. – Pramodya Mendis Mar 05 '19 at 04:41
  • @PramodyaMendis If your `date` variable is a complete date, like 2019-03-05, then you can skip the first '%'. As mentioned in previous comments, it is not the query itself that generates the error so please add the the code where you execute the query! – Joakim Danielson Mar 05 '19 at 09:34

2 Answers2

-1

I could make it work by doing this.

ps.setString(2, date + "%");

Thanks everyone :)

Pramodya Mendis
  • 686
  • 8
  • 24
-5

Replace:

ps.setString(2,  "%" + date + "%");

with:

ps.setString(2,  "'" + date + "'");
Mahdi Khardani
  • 52
  • 1
  • 10
  • 2
    Why would that help? `'` is not a LIKE wildcard character. Besides, it's unlikely that the *value* of the column starts and ends with apostrophes. – Andreas Mar 04 '19 at 17:01