Java springframework.jdbc.UncategorizedSQLException：无效的列类型-
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23114562/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
springframework.jdbc.UncategorizedSQLException :Invalid column type-
提问by Ravi
Hi am trying to do a QueryForInt using spring jbdc.The query returns a simple count.On execution i get springframework.jdbc.UncategorizedSQLException:java.sql.SQLException: Invalid column type. I did find similar posts and tried the suggestions but i am still stuck:(... Any help is appreciated.
嗨，我正在尝试使用 spring jbdc 执行 QueryForInt。查询返回一个简单的计数。执行时我得到 springframework.jdbc.UncategorizedSQLException:java.sql.SQLException: 无效的列类型。我确实找到了类似的帖子并尝试了这些建议，但我仍然被卡住了:(...任何帮助表示赞赏。
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue(DAOConstants.PROD_ID, custVo.getProdId(),OracleTypes.NUMBER);
params.addValue(DAOConstants.REQ_IND, DAOConstants.FLAG_Y,OracleTypes.VARCHAR);
The query:
查询：
select count(1) from prod where prod_id = :PROD_ID and req_ind =:REQ_IND
Table definition
表定义
Name Null Type
----------------- -------- ------------
PROD_ID NOT NULL NUMBER(5)
REQ_IND VARCHAR2(10)
The Logs..
日志..
Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback;
uncategorized SQLException for SQL [select count(1) from prod where prod_id = :PROD_ID and req_ind = :REQ_IND ]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:728)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:744)
at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:775)
... 45 more
Caused by: java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7937)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7517)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8174)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8155)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:724)
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
at org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
at org.springframework.jdbc.core.JdbcTemplate.doInPreparedStatement(JdbcTemplate.java:641)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
... 56 more
采纳答案by Thomas Risberg
Based on the stacktrace it looks like you are using the JdbcTemplate class. Since you are using the named parameter style place holders and providing a MapSqlParameterSource for the parameters you need to use the NamedParameterJdbcTemplate.
根据堆栈跟踪，您似乎正在使用 JdbcTemplate 类。由于您正在使用命名参数样式占位符并为需要使用 NamedParameterJdbcTemplate 的参数提供 MapSqlParameterSource。
So, why are you getting this strange error message? Well, your SQL query actually works using the Oracle JDBC driver. It does allow you to use either "?" or a named parameter style using a ":" as the prefix fro the placeholders. You still need to provide the parameter values in the correct order which the MapSqlParameterSource in your case does not. You can test this by using an Object array instead like:
那么，为什么会收到这个奇怪的错误消息？好吧，您的 SQL 查询实际上使用 Oracle JDBC 驱动程序工作。它确实允许您使用“？” 或使用“:”作为占位符前缀的命名参数样式。您仍然需要以正确的顺序提供参数值，而在您的情况下 MapSqlParameterSource 则没有。您可以使用 Object 数组来测试这一点，例如：
new Object[] {custVo.getProdId(), DAOConstants.FLAG_Y}
The error is based on the values coming in the wrong order from the params.getValues() method, with the String value being passed in for the :PROD_ID.
该错误基于来自 params.getValues() 方法的值以错误的顺序传入，并且为 :PROD_ID 传入了 String 值。
