Getting "Invalid column type" exception, while using NamedParameterJDBCTemplate for insertion

Question

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)

By combinig answers from David and Halfbit, got the soultion: 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); Thanks to David and Halfbit — Ajeet Singh
– Ajeet Singh, Commented Sep 28, 2013 at 20:48
Whenever 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
– Ziaullhaq Savanur, Commented Jan 6, 2022 at 13:04

Vladimir Jovanović · Accepted Answer · 2018-02-02 11:51:07Z

16

You are getting this exception because you are using JdbcTemplate instead of NamedParameterJdbcTemplate. MapSqlParameterSource works with NamedParameterJdbcTemplate.

edited Feb 2, 2018 at 11:51

Vladimir Jovanović

2,2672 gold badges26 silver badges44 bronze badges

answered Feb 2, 2018 at 10:44

Pravin Sharma

1611 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex78191 Over a year ago

It's because of variadic arguments in JdbcTemplate.

Alex78191 Over a year ago

FMI Instead of LocalDate you should putDate.valueOf(localdate).

Ziaullhaq Savanur Over a year ago

Whenever 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,...]]

halfbit · Accepted Answer · 2013-09-28 16:50:41Z

11

You might want to try using strings instead of characters for your CHAR(1) Y/N fields.

answered Sep 28, 2013 at 16:50

halfbit

3,4641 gold badge23 silver badges28 bronze badges

2 Comments

Ajeet Singh Over a year ago

Thanks 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... :-)

Ziaullhaq Savanur Over a year ago

Whenever 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,...]]

Dave L. · Accepted Answer · 2013-09-28 17:02:31Z

2

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);

answered Sep 28, 2013 at 17:02

Dave L.

9,8408 gold badges48 silver badges70 bronze badges

1 Comment

Ajeet Singh Over a year ago

@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);

Deep · Accepted Answer · 2020-04-26 06:41:51Z

0

In my case I was providing an Enumeration datatype instead of a datatype compatible with VARCHAR2 in database (like 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:

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

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.

answered Apr 26, 2020 at 6:41

Deep

9792 gold badges18 silver badges35 bronze badges

1 Comment

Ziaullhaq Savanur Over a year ago

Whenever 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,...]]

Aniket Nandan · Accepted Answer · 2020-04-26 08:57:29Z

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.

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.

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 Types in MapSqlParameterSource. So my ultimately code looks like below -

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!

Collectives™ on Stack Overflow

Getting "Invalid column type" exception, while using NamedParameterJDBCTemplate for insertion

5 Answers 5

3 Comments

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related