CODEWITHSUNDEEP
javamysqlspring-bootspring-data-jpa
Krishna
Krishna

Reputation: 261

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.

Upvotes: 6

Views: 43530

Answers (5)

Felipe Araujo
Felipe Araujo

Reputation: 1

Usually when you're writing native queries, you may will break lines on your repository code. Like this:

String value = "select from People " + "where id = :id" + "and address = :address"

The error will show: Named parameter not bound :idand

Notice that the repository joined the 2 strings on sequence, and changed the parameter name and reference. You must enter an empty space in the end of each line in order to have a correct semantic

The correct way will be like:

String value = "select from People " + "where id = :id " + "and address = :address"

Upvotes: 0

Shree Krishna
Shree Krishna

Reputation: 8562

If you're using JPQL & still getting this error then its possible you might have a space in between like

where id = : myId

Should be

where id = :myId

Upvotes: 5

pancakesmaplesyrup
pancakesmaplesyrup

Reputation: 101

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

Upvotes: 1

Sivaram Rasathurai
Sivaram Rasathurai

Reputation: 6403

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.

Upvotes: 4

Shawrup
Shawrup

Reputation: 2764

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);

Upvotes: 4

Related Questions