org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying

org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying

 

개발 환경

Spring Framework 3.2

MyBatis 3.2.7

MyBatis Spring 1.2.2

PostgreSQL 9.3-1101-jdbc41

JDK 1.6

Tomcat 7.0

Eclipse Kepler

 

다음과 같은 에러가 발생한다면?

org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying 

에러내용만을 잠깐 살펴보면. integer 타입의 데이터에 character varying 하는 operator가 존재하지 않는다고 되어 있다.

이유는 PostgreSQL 8.4 이후 버전부터는 자동으로 타입캐스팅을 해주는 기능이 사라졌다고 한다.

 

때문에 DB에 있는 데이터 타입과 DB의 구조를 담은 VO 파일에서도 해당 데이터 타입과 일치시켜주어야 한다. <<-- 반드시.

더불어서 MyBatis의 Mapper XML 파일에서 쿼리문을 정의할때 다음과 같이 jdbcType을 지정해주어야 한다. <<-- 선택사항

 

여기서 사용한 TB_NOTICE_BOARD 테이블의 SEQ_NO 컬럼의 데이터 타입과 일치시켜 준다.
 private int SEQ_NO            = 0; // 일련 번호     

 

Mapper XML의 쿼리문 정의

 <update id="updateNoticeHitsCount" parameterType="co.kr.xxxxx.xxxxx.vo.board.BoardVO">
  UPDATE TB_NOTICE_BOARD
  SET HITS_COUNT = HITS_COUNT + 1
  WHERE SEQ_NO = #{SEQ_NO, jdbcType=INTEGER}
 </update>

 

위와 같은 과정을 거치고 싶지 않다면? 일일이 타입캐스팅을 해주는 처리를 추가해주면 된다. ^^;

 

15:17:31.340 [http-bio-8282-exec-9] DEBUG jdbc.audit - 6. PreparedStatement.setString(1, "1") returned   sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java:-2)
15:17:31.340 [http-bio-8282-exec-9] DEBUG c.k.c.s.m.b.B.updateNoticeHitsCount - ==> Parameters: 1(String)
15:17:31.340 [http-bio-8282-exec-9] INFO  jdbc.sqlonly - SQL:::UPDATE TB_NOTICE_BOARD
  SET HITS_COUNT = HITS_COUNT + 1
  WHERE SEQ_NO = '1'
15:17:31.344 [http-bio-8282-exec-9] ERROR jdbc.audit - 6. PreparedStatement.execute() UPDATE TB_NOTICE_BOARD
  SET HITS_COUNT = HITS_COUNT + 1
  WHERE SEQ_NO = '1'
org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 73
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161) ~[postgresql-9.3-1101-jdbc41.jar:na]
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890) ~[postgresql-9.3-1101-jdbc41.jar:na]
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.3-1101-jdbc41.jar:na]
 at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559) ~[postgresql-9.3-1101-jdbc41.jar:na]
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) ~[postgresql-9.3-1101-jdbc41.jar:na]
 at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410) ~[postgresql-9.3-1101-jdbc41.jar:na]
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[commons-dbcp-1.4.jar:1.4]
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[commons-dbcp-1.4.jar:1.4]
 at net.sf.log4jdbc.PreparedStatementSpy.execute(PreparedStatementSpy.java:418) ~[log4jdbc-remix-0.2.7.jar:na]
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_40]
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_40]
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_40]
 at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_40]
 at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62) [mybatis-3.2.7.jar:3.2.7]
 at com.sun.proxy.$Proxy23.execute(Unknown Source) [na:na]
 at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44) [mybatis-3.2.7.jar:3.2.7]
 at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69) [mybatis-3.2.7.jar:3.2.7]
 at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48) [mybatis-3.2.7.jar:3.2.7]
 at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105) [mybatis-3.2.7.jar:3.2.7]
 at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71) [mybatis-3.2.7.jar:3.2.7]
 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152) [mybatis-3.2.7.jar:3.2.7]
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_40]
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_40]
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_40]
 at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_40]
 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358) [mybatis-spring-1.2.2.jar:1.2.2]
 at com.sun.proxy.$Proxy8.update(Unknown Source) [na:na]
 at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:254) [mybatis-spring-1.2.2.jar:1.2.2]
 at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:54) [mybatis-3.2.7.jar:3.2.7]
 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) [mybatis-3.2.7.jar:3.2.7]
 at com.sun.proxy.$Proxy17.updateNoticeHitsCount(Unknown Source) [na:na]
 at co.kr.xxxxx.xxxxx.service.board.BoardService.updateNoticeHitsCount(BoardService.java:60) [BoardService.class:na]
 at co.kr.xxxxx.xxxxx.controller.BoardController.customerNoticeRead(BoardController.java:84) [BoardController.class:na]
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_40]
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_40]
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_40]
 at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_40]
 at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219) [spring-web-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) [spring-web-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) [spring-webmvc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745) [spring-webmvc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686) [spring-webmvc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) [spring-webmvc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925) [spring-webmvc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856) [spring-webmvc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936) [spring-webmvc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827) [spring-webmvc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:621) [servlet-api.jar:na]
 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812) [spring-webmvc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) [servlet-api.jar:na]
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) [catalina.jar:7.0.37]
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) [catalina.jar:7.0.37]
 at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) [spring-web-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-3.2.2.RELEASE.jar:3.2.2.RELEASE]
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) [catalina.jar:7.0.37]
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) [catalina.jar:7.0.37]
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) [catalina.jar:7.0.37]
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) [catalina.jar:7.0.37]
 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) [catalina.jar:7.0.37]
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) [catalina.jar:7.0.37]
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) [catalina.jar:7.0.37]
 at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936) [catalina.jar:7.0.37]
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) [catalina.jar:7.0.37]
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) [catalina.jar:7.0.37]
 at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004) [tomcat-coyote.jar:7.0.37]
 at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) [tomcat-coyote.jar:7.0.37]
 at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312) [tomcat-coyote.jar:7.0.37]
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_40]
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_40]
 at java.lang.Thread.run(Thread.java:724) [na:1.7.0_40]