spring 使用 NamedParameterJDBCTemplate 进行插入时，获取“无效的列类型”异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19069170/
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
Getting "Invalid column type" excecption, while using NamedParameterJDBCTemplate for insertion
提问by Ajeet Singh
I am using below code while inserting a row into database(oracle 10g xe,jar: ojdbc14.jar)
我在向数据库中插入一行时使用以下代码（oracle 10g xe，jar：ojdbc14.jar）
String sql = "INSERT INTO SPONSOR_TB(ID,NAME,INDUSTRY_TYPE,IS_REPORTING_SPONSOR,IS_NOT_SOLICITE) VALUES(SEQ_SPONSOR_ID.NEXTVAL,:NAME1,:INDUSTRY_TYPE,:IS_REPORTING_SPONSOR,:IS_NOT_SOLICITE)";
MapSqlParameterSource paramSource = new MapSqlParameterSource();
paramSource.addValue("NAME1",sponsor.getName());
paramSource.addValue("INDUSTRY_TYPE", sponsor.getIndustryType());
paramSource.addValue("IS_NOT_SOLICITE", sponsor.getNotSoliciteFlag()?'Y':'N');
paramSource.addValue("IS_REPORTING_SPONSOR", sponsor.getReportingFlag()?'Y':'N');
KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
namedParameterJdbcTemplate.update(sql, paramSource, generatedKeyHolder,new String[]{"ID"});
int id = generatedKeyHolder.getKey().intValue();
Structure of the table is:
表的结构是：
create table SPONSOR_TB
(
id INTEGER not null,
name VARCHAR2(20),
industry_type INTEGER not null,
is_reporting_sponsor CHAR(1) not null,
is_not_solicite CHAR(1) not null
)
and SEQ_SPONSOR_ID is sequence
和 SEQ_SPONSOR_ID 是序列
And Sponsor class is:
赞助商类别是：
public class Sponsor{
private int id;
private String name;
private boolean reportingFlag;
private boolean notSoliciteFlag;
private int industryType;
//getter setter
}
And db configuration in spring-servlet.xml is:
spring-servlet.xml 中的 db 配置是：
<beans:bean
id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
<beans:property
name="driverClassName"
value="oracle.jdbc.driver.OracleDriver" />
<beans:property
name="username"
value="SPONSOR_DB" />
<beans:property
name="password"
value="ajeet" />
<beans:property
name="url"
value="jdbc:oracle:thin:@localhost:1521:XE" />
</beans:bean>
I am getting following exception:
我收到以下异常：
SEVERE: Servlet.service() for servlet [spring] in context with path [/GroupSolution] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO SPONSOR_TB (ID,NAME,INDUSTRY_TYPE,IS_REPORTING_SPONSOR,IS_NOT_SOLICITE) VALUES(SEQ_SPONSOR_ID.NEXTVAL,?,?,?,?)]; SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type] with root cause
java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9168)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8749)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9471)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9454)
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:127)
at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:298)
at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:251)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:581)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:843)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:288)
at com.groupsolution.dao.SponsorDaoImpl.createSponsor(SponsorDaoImpl.java:55)
at com.groupsolution.service.SponsorServiceImpl.createSponsor(SponsorServiceImpl.java:31)
at com.groupsolution.controller.SponsorController.addSponsor(SponsorController.java:38)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:436)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:424)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1001)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
回答by halfbit
You might want to try using strings instead of characters for your CHAR(1) Y/N fields.
您可能想尝试在 CHAR(1) Y/N 字段中使用字符串而不是字符。
回答by Pravin Sharma
You are getting this exception because you are using JdbcTemplateinstead of NamedParameterJdbcTemplate. MapSqlParameterSourceworks with NamedParameterJdbcTemplate.
您收到此异常是因为您使用的是JdbcTemplate而不是NamedParameterJdbcTemplate。MapSqlParameterSource与NamedParameterJdbcTemplate.
回答by Dave L.
Try changing your code to:
尝试将您的代码更改为：
paramSource.addValue("NAME1",sponsor.getName(), Types.VARCHAR);
paramSource.addValue("INDUSTRY_TYPE", sponsor.getIndustryType(), Types.INTEGER);
paramSource.addValue("IS_NOT_SOLICITE", sponsor.getNotSoliciteFlag()?'Y':'N', Types.CHAR);
paramSource.addValue("IS_REPORTING_SPONSOR", sponsor.getReportingFlag()?'Y':'N', Types.CHAR);
回答by jzqa
for me this issue happened when i try to set null value on timestamp column. I changed the value timestamp(0). This seems to me a driver issue. I was using oracle 10g / ojdbc14.jar / oracle.jdbc.OracleDriver
对我来说，当我尝试在时间戳列上设置空值时发生了这个问题。我更改了值时间戳（0）。这在我看来是驱动程序问题。我使用的是 oracle 10g / ojdbc14.jar / oracle.jdbc.OracleDriver
java.sql.SQLException: Invalid column type
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:6433)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:1354)
回答by Golu
In my case I was providing an Enumeration datatype instead of a datatype compatible with VARCHAR2 in database (like String ).
就我而言，我提供的是 Enumeration 数据类型，而不是与数据库中的 VARCHAR2 兼容的数据类型（如 String ）。
My query was like :
我的查询是这样的：
private static final String QUERY ="SELECT res.id, FROM result res " +
"WHERE res.country = :resCountry ";
And at the time of passing the parameter resCountry, I did like this:
在传递参数 resCountry 时，我是这样做的：
List<Map<String, Object>> listResult = jdbcTemplate.queryForList(QUERY,Country.US);
Now the complaint/exception came from the fact that Country.US was of type Enumeration Country
现在投诉/例外来自 Country.US 是Enumeration Country类型的事实
I did the following change to make it work.
我做了以下更改以使其工作。
List<Map<String, Object>> listResult = jdbcTemplate.queryForList(QUERY,Country.US.name());
After this change it worked fine.
在此更改后，它工作正常。
回答by Aniket Nandan
Though it is too late, today I found the same issue. I tried all of the above options which were provided by the other members, but no luck. What I found was, to add a char column in where clause I was providing the java.lang.char in that space.
虽然为时已晚，但今天我发现了同样的问题。我尝试了其他成员提供的所有上述选项，但没有运气。我发现的是，在 where 子句中添加一个 char 列，我在该空间中提供 java.lang.char 。
Suppose my query looks like this - SELECT * FROM app_config WHERE enable = :enable;, where the 'enable' column is char(1) column in DB. So while passing the parameter in namedparameterJdbcTemplate, I was passing a java char as 'Y', which was causing the error.
假设我的查询看起来像这样 - SELECT * FROM app_config WHERE enable = :enable;，其中“启用”列是 DB 中的 char(1) 列。因此，在 namedparameterJdbcTemplate 中传递参数时，我传递了一个 java char as 'Y'，这导致了错误。
In order to solve that I passed normal java string as the parameter value in the namedparameterJdbcTemplate like "Y"and BANG!!! It works fine. Also I need not to provide the Typesin MapSqlParameterSource. So my ultimately code looks like below -
为了解决我通过普通的java字符串作为namedparameterJdbcTemplate中的参数值，就像"Y"和BANG一样！！！它工作正常。另外我不需要Types在 MapSqlParameterSource 中提供。所以我的最终代码如下所示 -
protected String getConfigValue(String configKey, String enable) throws BusinessException {
String value = "";
try {
if (StringUtils.isNotBlank(configKey)) {
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue(ApplicationDaoConstants.CONFIG_KEY, configKey); // This is the VARCHAR column in DB
parameters.addValue(ApplicationDaoConstants.ENABLE, enable); // This is the character column in DB
value = nmJdbcTmplt.query(AppConfigSQL.GET_CONFIG_VALUE, parameters, rs -> {
if (rs.next()) {
return rs.getString(ApplicationDaoConstants.CONFIG_VALUE);
}
return "";
});
}
} catch (Exception e) {
log.error("Exception occured while accessing app_config ", e);
throw new BusinessException(ApplicationConstants.ERROR_ACCESSING_DATABASE);
}
return value;
}
I hope this could help others. Thanks!
我希望这可以帮助其他人。谢谢！
