数据类型不匹配（SQLCODE=-245）

@Overridepublic List<NonSchlCalendarDto> getHoliday(String day) {List<NonSchlCalendarDto> list = new ArrayList<NonSchlCalendarDto>();Map<String, Object> params = new HashMap<String, Object>();StringBuilder searchCriteria = new StringBuilder();searchCriteria.append(" WHERE HOLIDAY = Date(:day) ");params.put("day", day);String sql = "SELECT HOLIDAY, SCHLLEVLTYPECODE, SCHLSESSTYPECODE " +" FROM " + DBConstants.DB_EDIS_STM +"SCHOLIDAY_GLOBAL " + searchCriteria.toString() + " ORDER BY HOLIDAY, SCHLLEVLTYPECODE, SCHLSESSTYPECODE FOR FETCH ONLY";try {list = namedParameterJdbcTemplate.query(sql, params, BeanPropertyRowMapper.newInstance(NonSchlCalendarDto.class));} catch (Exception e) {logger.error("[added-getHoliday] failed: " + e);e.printStackTrace();} return list;}

抛出的错误信息如下：

[ERROR][17-Feb-2016|02:23:50][hk.gov.edb.edis.scm.repository.terrwideschlstat.dao.impl.NonSchlCalendarDaoImpl] - [added-getHoliday] failed: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT HOLIDAY, SCHLLEVLTYPECODE, SCHLSESSTYPECODE  FROM EDIS_STM.T_STM_SCHOLIDAY_GLOBAL  WHERE HOLIDAY = Date(?)  ORDER BY HOLIDAY, SCHLLEVLTYPECODE, SCHLSESSTYPECODE FOR FETCH ONLY]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-245, SQLSTATE=428F5, SQLERRMC=DATE;1, DRIVER=3.67.27[err] org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT HOLIDAY, SCHLLEVLTYPECODE, SCHLSESSTYPECODE  FROM EDIS_STM.T_STM_SCHOLIDAY_GLOBAL  WHERE HOLIDAY = Date(?)  ORDER BY HOLIDAY, SCHLLEVLTYPECODE, SCHLSESSTYPECODE FOR FETCH ONLY]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-245, SQLSTATE=428F5, SQLERRMC=DATE;1, DRIVER=3.67.27[err] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)[err] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)[err] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)[err] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)[err] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)[err] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)[err] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)[err] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772)[err] at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192)[err] at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:199)[err] at hk.gov.edb.edis.scm.repository.terrwideschlstat.dao.impl.NonSchlCalendarDaoImpl.getHoliday(NonSchlCalendarDaoImpl.java:111)[err] at hk.gov.edb.edis.scm.repository.terrwideschlstat.dao.impl.NonSchlCalendarDaoImpl$$FastClassBySpringCGLIB$$8b600547.invoke(<generated>)[err] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)[err] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:640)[err] at hk.gov.edb.edis.scm.repository.terrwideschlstat.dao.impl.NonSchlCalendarDaoImpl$$EnhancerBySpringCGLIB$$52793bb9.getHoliday(<generated>)[err] at hk.gov.edb.edis.scm.service.terrwideschlstat.impl.NonSchlCalendarServiceImpl.getHoliday(NonSchlCalendarServiceImpl.java:48)[err] at hk.gov.edb.edis.scm.mvc.terrwideschlstat.controllers.NonSchlCalendarController.setMonth(NonSchlCalendarController.java:437)[err] at hk.gov.edb.edis.scm.mvc.terrwideschlstat.controllers.NonSchlCalendarController.setHolidayCalander(NonSchlCalendarController.java:96)[err] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[err] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)[err] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)[err] at java.lang.reflect.Method.invoke(Method.java:606)[err] at org.springframework.expression.spel.support.ReflectiveMethodExecutor.execute(ReflectiveMethodExecutor.java:63)[err] at org.springframework.expression.spel.ast.MethodReference.getValueInternal(MethodReference.java:122)[err] at org.springframework.expression.spel.ast.MethodReference.access$000(MethodReference.java:44)[err] at org.springframework.expression.spel.ast.MethodReference$MethodValueRef.getValue(MethodReference.java:258)[err] at org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:84)[err] at org.springframework.expression.spel.ast.SpelNodeImpl.getTypedValue(SpelNodeImpl.java:114)[err] at org.springframework.expression.spel.standard.SpelExpression.getValue(SpelExpression.java:105)[err] at org.springframework.binding.expression.spel.SpringELExpression.getValue(SpringELExpression.java:84)[err] at org.springframework.webflow.action.EvaluateAction.doExecute(EvaluateAction.java:75)[err] at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:188)[err] at org.springframework.webflow.execution.AnnotatedAction.execute(AnnotatedAction.java:145)[err] at org.springframework.webflow.execution.ActionExecutor.execute(ActionExecutor.java:51)[err] at org.springframework.webflow.engine.ActionList.execute(ActionList.java:154)[err] at org.springframework.webflow.engine.Flow.start(Flow.java:526)[err] at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:368)[err] at org.springframework.webflow.engine.impl.RequestControlContextImpl.start(RequestControlContextImpl.java:234)[err] at org.springframework.webflow.engine.SubflowState.doEnter(SubflowState.java:101)[err] at org.springframework.webflow.engine.State.enter(State.java:194)[err] at org.springframework.webflow.engine.Transition.execute(Transition.java:228)[err] at org.springframework.webflow.engine.impl.FlowExecutionImpl.execute(FlowExecutionImpl.java:395)[err] at org.springframework.webflow.engine.impl.RequestControlContextImpl.execute(RequestControlContextImpl.java:214)[err] at org.springframework.webflow.engine.TransitionableState.handleEvent(TransitionableState.java:116)[err] at org.springframework.webflow.engine.Flow.handleEvent(Flow.java:547)[err] at org.springframework.webflow.engine.impl.FlowExecutionImpl.handleEvent(FlowExecutionImpl.java:390)[err] at org.springframework.webflow.engine.impl.RequestControlContextImpl.handleEvent(RequestControlContextImpl.java:210)[err] at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:105)[err] at org.springframework.webflow.engine.State.enter(State.java:194)[err] at org.springframework.webflow.engine.Transition.execute(Transition.java:228)[err] at org.springframework.webflow.engine.impl.FlowExecutionImpl.execute(FlowExecutionImpl.java:395)[err] at org.springframework.webflow.engine.impl.RequestControlContextImpl.execute(RequestControlContextImpl.java:214)[err] at org.springframework.webflow.engine.TransitionableState.handleEvent(TransitionableState.java:116)[err] at org.springframework.webflow.engine.Flow.handleEvent(Flow.java:547)[err] at org.springframework.webflow.engine.impl.FlowExecutionImpl.handleEvent(FlowExecutionImpl.java:390)[err] at org.springframework.webflow.engine.impl.RequestControlContextImpl.handleEvent(RequestControlContextImpl.java:210)[err] at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:105)[err] at org.springframework.webflow.engine.State.enter(State.java:194)[err] at org.springframework.webflow.engine.Flow.start(Flow.java:527)[err] at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:368)[err] at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:223)[err] at org.springframework.webflow.executor.FlowExecutorImpl.launchExecution(FlowExecutorImpl.java:140)[err] at org.springframework.webflow.mvc.servlet.FlowHandlerAdapter.handle(FlowHandlerAdapter.java:238)[err] at org.springframework.faces.webflow.JsfFlowHandlerAdapter.handle(JsfFlowHandlerAdapter.java:57)[err] at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)[err] at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)[err] at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)[err] at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)[err] at javax.servlet.http.HttpServlet.service(HttpServlet.java:575)[err] at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)[err] at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)[err] at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1275)[err] at [internal classes][err] at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:98)[err] at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:192)[err] at [internal classes][err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)[err] at hk.gov.edb.edis.scs.client.filters.SwitchPostFilter.doFilter(SwitchPostFilter.java:131)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)[err] at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at hk.gov.edb.edis.scs.client.filters.PreAuthenticationFilter.doFilter(PreAuthenticationFilter.java:259)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)[err] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)[err] at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)[err] at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)[err] at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)[err] at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)[err] at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)[err] at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:192)[err] at [internal classes][err] at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)[err] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)[err] at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:192)[err] at [internal classes][err] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)[err] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)[err] at java.lang.Thread.run(Thread.java:745)[err] Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-245, SQLSTATE=428F5, SQLERRMC=DATE;1, DRIVER=3.67.27[err] at com.ibm.db2.jcc.am.gd.a(gd.java:749)[err] at com.ibm.db2.jcc.am.gd.a(gd.java:66)[err] at com.ibm.db2.jcc.am.gd.a(gd.java:135)[err] at com.ibm.db2.jcc.am.so.c(so.java:2763)[err] at com.ibm.db2.jcc.am.so.d(so.java:2751)[err] at com.ibm.db2.jcc.am.so.a(so.java:2200)[err] at com.ibm.db2.jcc.am.to.a(to.java:7384)[err] at com.ibm.db2.jcc.t4.ab.h(ab.java:141)[err] at com.ibm.db2.jcc.t4.ab.b(ab.java:41)[err] at com.ibm.db2.jcc.t4.o.a(o.java:32)[err] at com.ibm.db2.jcc.t4.tb.i(tb.java:145)[err] at com.ibm.db2.jcc.am.so.ib(so.java:2169)[err] at com.ibm.db2.jcc.am.to.tc(to.java:3547)[err] at com.ibm.db2.jcc.am.to.b(to.java:4345)[err] at com.ibm.db2.jcc.am.to.gc(to.java:739)[err] at com.ibm.db2.jcc.am.to.executeQuery(to.java:708)[err] at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:703)[err] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)[err] ... 120 more

导致错误的原因是Java中定义day参数的数据类型String与DB2中Holiday的数据类型DATE不匹配。

解决方法可以是

1.Sting转换为Date类型，其他不变。

      如：SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");

             java.util.Date date=sdf.parse(day);  

2.在.append的时候直接加上‘

     如：searchCriteria.append(" WHERE HOLIDAY = Date('" + day + "') ");