java - ORA-00900: invalid SQL statement - NamedParameterJdbcTemplate
问题描述
I have this query as a property constant:
get.policy.list=SELECT policy FROM table WHERE policy IN (:policyList) AND state = :state
On feeding and getting a list from the jdbcTemplate, I get:
Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
on the first line inside the try block.
@Override
public List<MyVO> areValidPolicies(String state, List<String> policyList) {
MapSqlParameterSource namedParameters = new MapSqlParameterSource().addValue("state", state)
.addValue("policyList", policyList);
List<MyVO> retrievedPolicyList = new ArrayList<MyVO>();
try{
retrievedPolicyList = myNamedJdbcTemplate.query("SELECT policy FROM table WHERE policy IN (:policyList) AND state = :state", namedParameters, new RowMapper<MyVO>() {
@Override
public MyVO mapRow(ResultSet rs, int rowNum) throws SQLException {
MyVO myVO = new MyVO();
MyVO.setPolicy(rs.getString("policy"));
return myVO;
}
});
} catch (Exception ex){
System.out.println(ex.getMessage());
ex.printStackTrace();
}
retrievedPolicyList.stream().forEach(System.out::println);
return retrievedPolicyList;
}
Stack trace:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [get.policy.list]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:684)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:711)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:761)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192)
at com.project.policyImpl.areValidPolicies(ESRepublishDaoImpl.java:56)
at com.project.policyImpl$$FastClassBySpringCGLIB$$683fad37.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
Please guide on where am I getting wrong.
解决方案
table
是 SQL 中的保留字。我建议重命名表格以避免将来的麻烦,但如果这不是一个选项,您可以使用双引号 ( "
) 转义它:
retrievedPolicyList =
myNamedJdbcTemplate.query("SELECT policy FROM \"table\" WHERE policy IN (:policyList) AND state = :state", namedParameters, new RowMapper<MyVO>() {
// Here --------------------------------------^^-----^^
推荐阅读
- lucene - 特定的 Lucene 类是否旨在由应用程序使用?
- asp.net-web-api - 是否可以在单独的项目中为 Asp Web Api 创建 Swagger 文档?
- python - 使用 Python 读取 CSV 文件特定的列和行
- batch-file - 命令提示符 - 批处理文件 - BAT 文件
- python - Numba - 来自jited函数内部的参数(numpy数组)的内存地址
- javascript - 为什么我们将变量的值括在括号中?
- python - 使用 Selenium 和 Python 抓取文本值
- javascript - 在较新的 iPhone 上无法点击链接(3D Touch 问题?)
- sql - 排序具有数字和字母的字符串列(Oracle SQL)
- java - 实例化一个类返回空值