Getting "Invalid column type" exception, while using NamedParameterJDBCTemplate for insertion
73,629
Solution 1
You might want to try using strings instead of characters for your CHAR(1) Y/N fields.
Solution 2
You are getting this exception because you are using JdbcTemplate
instead of NamedParameterJdbcTemplate
. MapSqlParameterSource
works with NamedParameterJdbcTemplate
.
Solution 3
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);
Author by
Ajeet Singh
Updated on July 17, 2022Comments
-
Ajeet Singh almost 2 years
I am using below code while inserting a row into database(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
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:
<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)
-
Ajeet Singh over 10 years@David- I tried using Types.xxxx which you suggested. But now I am getting error: SQL state [null]; error code [17132]; Invalid conversion requested; Even if I changed code to: paramSource.addValue("NAME1","somename", Types.VARCHAR); paramSource.addValue("INDUSTRY_TYPE",2, Types.INTEGER); paramSource.addValue("IS_NOT_SOLICITE",'N', Types.CHAR); paramSource.addValue("IS_REPORTING_SPONSOR",'N', Types.CHAR);
-
Ajeet Singh over 10 yearsThanks a lot @halfbit.. changed my database field from CHAR(1) to CHAR(2) and then tried 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.VARCHAR); paramSource.addValue("IS_REPORTING_SPONSOR", sponsor.getReportingFlag()?'Y':'N',Types.VARCHAR); It worked for me... :-)
-
Erikson about 6 yearsThat was exactly it! Thank you for posting it!
-
Alex78191 about 4 yearsIt's because of variadic arguments in JdbcTemplate.
-
Alex78191 about 4 yearsFMI Instead of LocalDate you should put
Date.valueOf(localdate)
. -
Ziaullhaq Savanur over 2 yearsWhenever you see this exp make sure the data you are passing as input are of same type as of the column to which you are passing. In my case the IN clause I was using was expecting Array of integers i.e [1,2,3,..] but I passed [[1,2,3,...]]
-
Ziaullhaq Savanur over 2 yearsWhenever you see this exp make sure the data you are passing as input are of same type as of the column to which you are passing. In my case the IN clause I was using was expecting Array of integers i.e [1,2,3,..] but I passed [[1,2,3,...]]
-
Ziaullhaq Savanur over 2 yearsWhenever you see this exp make sure the data you are passing as input are of same type as of the column to which you are passing. In my case the IN clause I was using was expecting Array of integers i.e [1,2,3,..] but I passed [[1,2,3,...]]