英文:
Named parameter not bound : DATE_FORMAT Native Query in Spring Boot
问题
@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);
在 Spring Boot 中，我正在尝试使用 MySQL 原生查询通过 select 查询在 dateFrom 和当前日期之间获取表数据作为列表，MySQL 数据库字段数据类型为 String。
以下是 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);
在 Spring Boot 中针对上述查询代码，我遇到了以下错误：
Named parameter not bound : DATE_FORMAT; nested exception is org.hibernate.QueryException: Named parameter not bound : DATE_FORMAT
有没有人可以帮我修改查询，解决这个问题呢？
英文:
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.
答案1
得分: 3
从 :DATE_FORMAT(curdate(), '%d/%m/%Y') 中删除 :。: 用于在 JPA 查询中绑定参数。在 DATE_FORMAT 前面添加 : 会让 JPA 将其视为参数。
因此，最终的查询应为：
@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);
英文:
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);
答案2
得分: 3
在任何情况下，如果你遇到这个问题。在进行调试或深入的谷歌搜索之前，请检查以下事项：
-
方法变量名称和你的查询变量是否匹配。
-
查询分号与你的参数是否匹配。
-
如果你重新命名了表格并使用了简短表达式，请仔细检查它们是否被正确使用。
-
检查你的查询语法。
因为大多数问题都来自上述小错误。
英文:
In any case, If you face this problem. Before doing debugging or deep google search Check the following things
-
Method variable names and your query variables are matched or not.
-
Query semicolon with your parameters
-
If you renamed the table with short expressions, double-check them they were used correctly.
-
Check your query syntax.
Because most of the issues have come from the above minor mistakes.
答案3
得分: 2
如果您正在使用JPQL并且仍然出现此错误，则有可能在您的代码中出现了空格，就像这样：
where id = : myId
应该改为
where id = :myId
英文:
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
答案4
得分: 1
我遇到了相同的错误，但在我的情况下，我在变量上使用了一个比较运算符<>，并且两者之间没有空格。在变量和运算符之间添加一个空格解决了我的问题。
即将:addressType<>更改为:addressType <>就可以了。
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论