'Named parameter not bound : DATE_FORMAT Native Query in Spring Boot

I am trying to get table data as list using select query between dateFrom and current date using MySQL Native Query in Spring Boot. and MySQL database field datatype is String.

Below is Query in Repository:

@Query(value = "select * from Account where DATETIMESTAMP >= :dateFrom  AND DATETIMESTAMP < :DATE_FORMAT(curdate(), '%d/%m/%Y')", nativeQuery = true)
List<Account> findByDate(@Param("dateFrom") String dateFrom);

Getting below error for above query in Spring Boot:

 Named parameter not bound : DATE_FORMAT; nested exception is org.hibernate.QueryException: Named parameter not bound : DATE_FORMAT

Can anyone please help me to frame the query for the same.

javamysqlspring-bootspring-data-jpa


Solution 1:[1]

Remove the : from :DATE_FORMAT(curdate(), '%d/%m/%Y'). : is used for binding parameters in jpa query. The addition : in front of DATE_FORMAT makes JPA think it as a parameter. So the final query should be

@Query(value = "select * from Account where DATETIMESTAMP >= :dateFrom  AND DATETIMESTAMP < DATE_FORMAT(curdate(), '%d/%m/%Y')", nativeQuery = true)
List<Account> findByDate(@Param("dateFrom") String dateFrom);

Solution 2:[2]

In any case, If you face this problem. Before doing debugging or deep google search Check the following things

  1. Method variable names and your query variables are matched or not.

  2. Query semicolon with your parameters

  3. If you renamed the table with short expressions, double-check them they were used correctly.

  4. Check your query syntax.

Because most of the issues have come from the above minor mistakes.

Solution 3:[3]

I had the same error, but in my case I had a comparison operator '<>' joined on to my variable with no space in between. Adding a space between the variable and the operator fixed my issue. ie. changing :addressType<> to :addressType <> worked

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Shawrup
Solution 2
Solution 3 pancakesmaplesyrup

Related Questions