[SPRING] 삽입에 NamedParameterJDBCTemplate를 사용하는 동안 "잘못된 열 유형"예외가 발생합니다.

삽입에 NamedParameterJDBCTemplate를 사용하는 동안 "잘못된 열 유형"예외가 발생합니다.

데이터베이스에 행을 삽입하는 동안 아래 코드를 사용하고 있습니다 (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();

테이블의 구조는 다음과 같습니다.

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 
)

SEQ_SPONSOR_ID는 시퀀스입니다.

스폰서 클래스는 다음과 같습니다.

public class Sponsor{
      private int id;
      private String name;
      private boolean reportingFlag;
      private boolean notSoliciteFlag;
      private int industryType;
      //getter setter
}

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>

다음과 같은 예외가 발생합니다.

 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)

해결법

1. ==============================

   1.CHAR (1) Y / N 필드에 문자 대신 문자열을 사용하려고 할 수 있습니다.

   CHAR (1) Y / N 필드에 문자 대신 문자열을 사용하려고 할 수 있습니다.

2. ==============================

   2.NamedParameterJdbcTemplate 대신 JdbcTemplate을 사용 중이기 때문에이 예외가 발생합니다. MapSqlParameterSource는 NamedParameterJdbcTemplate과 함께 작동합니다.

   NamedParameterJdbcTemplate 대신 JdbcTemplate을 사용 중이기 때문에이 예외가 발생합니다. MapSqlParameterSource는 NamedParameterJdbcTemplate과 함께 작동합니다.

3. ==============================

   3.코드를 다음과 같이 변경해보십시오.

   코드를 다음과 같이 변경해보십시오.

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

4. ==============================

   4.나를 위해 타임 스탬프 열에 null 값을 설정할 때이 문제가 발생했습니다. 값 타임 스탬프 (0)를 변경했습니다. 이것은 나에게 드라이버 문제로 보인다. oracle 10g / ojdbc14.jar / oracle.jdbc.OracleDriver를 사용하고있었습니다.

   나를 위해 타임 스탬프 열에 null 값을 설정할 때이 문제가 발생했습니다. 값 타임 스탬프 (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)
   

from https://stackoverflow.com/questions/19069170/getting-invalid-column-type-excecption-while-using-namedparameterjdbctemplate by cc-by-sa and MIT license