org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar

huangapple
go评论101阅读模式
英文:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar

问题

  1. StringBuilder myquery = new StringBuilder();
  3. myquery.append("SELECT * from Student stu")
  4.       .append(" WHERE stu.Roll IN ('120', '140') AND stuv2.Roll is null ")
  5.       .append(" AND date(stu.admission) between ")
  6.       .append("timestamp(?)").append(" AND CURRENT DATE-30 DAY");
  8. final Object[] args = new Object[] {
  9.     getAdm() 
  10. }; //its returning '2020-10-01 03:00:00'
  11. int[] types = new int[] { Types.CHAR };
  12. List<Students> result = null;  
  13. result = jdbcTemplate.query(myquery, args, types, new Mapper());
  16. Incorrect query is being built, I am expecting value from args to replace the '?':
  18. SELECT * from Student stu WHERE stu.Roll IN ('120', '140') AND stuv2.Roll is null 
  19. AND date(stu.admission) between timestamp(?) AND CURRENT DATE-100 DAY
  22. I am getting the following error:
  24. org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=4.19.66
  26. nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=4.19.66
  27.   at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  28.   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  29.   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  30.   at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1444) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  31.   at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  32.   at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  33.   at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  34.   at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:706) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  35.   at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:758) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  36.   at com.optum.chy.component.DB2Service.selectData(DB2Service.java:278) ~[classes/:na]
英文: 
  1. StringBuilder myquery = new StringBuilder();
  2. myquery.append(&quot;SELECT * from Student stu&quot;)
  3. .append(&quot; WHERE stu.Roll IN ( &#39;120&#39;, &#39;140&#39;) AND stuv2.Roll is null &quot;)
  4. .append(&quot; AND date(stu.admission) between &quot;)
  5. .append(&quot;timestamp(&quot;).append(&quot;?&quot;).append(&quot;)&quot;).append(&quot; AND  CURRENT DATE- 30 DAY&quot;);
  6. final Object[] args = new Object[] {
  7. getAdm() 
  8. } ; //its returning &#39;2020-10-01 03:00:00&#39;
  9. int[] types = new int[] { Types.CHAR  };
  10. List&lt;Students&gt; result = null;  
  11. result = jdbcTemplate.query(myquery, args, types, new Mapper());

Incorrect query is being built, I am expecting value from args to replace the "?" :

  1.  SELECT * from Student stu WHERE stu.Roll IN ( &#39;120&#39;, &#39;140&#39;) AND stuv2.Roll is null 
  2. AND date(stu.admission) between timestamp(?) AND CURRENT DATE-100 DAY

I am getting the following error :

  1. org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=4.19.66
  2. nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=4.19.66
  3. at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  4. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  5. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  6. at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1444) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  7. at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  8. at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  9. at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  10. at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:706) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  11. at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:758) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
  12. at com.optum.chy.component.DB2Service.selectData(DB2Service.java:278) ~[classes/:na]

答案1

得分: 2

看起来错误提示显示参数未被识别，或者至少不被视为日期...

尝试将你的参数转换为日期 CAST( &quot;parameter_here&quot; AS DATE)

另外，我更建议使用 MapSqlParameterSource parameters = new MapSqlParameterSource();，但这是不同的主题。

编辑补充：jarlh 指出并更正了你的查询中的错误，你有一些拼写错误需要修复。

你可以查阅这个帖子，与之类似：https://stackoverflow.com/questions/25734378/namedjdbcparametertemplate-418-sql-error-with-setting-date

英文:

Looking at the error it seems like the argument is not recognized or not as a date at least ...

Try casting your argument as a date CAST( &quot;parameter_here&quot; AS DATE)

Also, I'd rather use MapSqlParameterSource parameters = new MapSqlParameterSource(); , but that's different subject

Edit bis : jarlh pointed out and corrected mistakes in your query, you have some typos you should fix.

You can look into this thread , which is similar : https://stackoverflow.com/questions/25734378/namedjdbcparametertemplate-418-sql-error-with-setting-date

huangapple
  • 本文由 发表于 2020年10月12日 18:20:20
  • 转载请务必保留本文链接：https://go.coder-hub.com/64315917.html
  • db2
  • java
  • jdbctemplate
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定