时间戳

首页 > 解决方案 > org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; 糟糕的 SQL 语法

java - org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; 糟糕的 SQL 语法

问题描述

StringBuilder myquery = new StringBuilder();

  myquery.append("SELECT * from Student stu")
  .append(" WHERE stu.Roll IN ( '120', '140') AND stuv2.Roll is null ")
  .append(" AND date(stu.admission) between ")
  .append("timestamp(").append("?").append(")").append(" AND  CURRENT DATE- 30 DAY");

 final Object[] args = new Object[] {
        getAdm() 
    } ; //its returning '2020-10-01 03:00:00'
    int[] types = new int[] { Types.CHAR  };
    List<Students> result = null;  
    result = jdbcTemplate.query(myquery, args, types, new Mapper());

正在构建不正确的查询，我期望 args 的值替换“？” ：

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

我收到以下错误：

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

nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=4.19.66
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1444) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:706) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:758) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at com.optum.chy.component.DB2Service.selectData(DB2Service.java:278) ~[classes/:na]

标签： javasqldb2jdbctemplate

解决方案

查看错误似乎该参数未被识别或至少不是日期......

尝试将您的论点转换为日期CAST( "parameter_here" AS DATE)

另外，我宁愿使用MapSqlParameterSource parameters = new MapSqlParameterSource();，但这是不同的主题

编辑之二：jarlh 指出并纠正了您的查询中的错误，您有一些错别字需要修正。

你可以查看这个线程，它是类似的：NamedJdbcParameterTemplate: -418 SQL error with setting Date

推荐阅读