[解決済み] org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; SQLの未分類SQLExceptionです。

質問

私の要件は、文字または文字のリストを入力すると、私はjquery autocompleteとajax呼び出しを使用して、DB2に格納されている宛先のリストを検索することです。私が宛先テキストボックスに文字または文字列を入力すると、それはDB2に問い合わせることによって、入力したフォーマットの宛先のリストを取得する必要があります。私は春のJDBCテンプレートを使用しています。何がエラーを引き起こしているのか、また私は春のJDBCに新しいです。ありがとうございます。

Jqueryとajxaの呼び出し。

$("#destinationName").autocomplete({

        source : function(request, response) {
            $.ajax({
                url : "${pageContext.request.contextPath}/showDestinations",
                type : "POST",
                data : {
                    term : request.term
                },
                dataType : "json",
                success : function(data) {
                    response(data);
                }
            });
        }
    });

コントローラを使用します。

@RequestMapping(value = "/showDestinations")
public void getDestinations(HttpServletRequest request,
        HttpServletResponse response) throws ServletException, IOException {
    LOG.info("start of--/showDestinations -- MonitoringController.getDestinations()");
    response.setContentType("application/json");
    try {
        String term = request.getParameter("term");
        List<String> destinationList = null;

        LOG.info("scenario 1");

        destinationList = monitorDAO.fetchDestinations(term);
        String searchList = new Gson().toJson(destinationList);
        response.getWriter().write(searchList);
        LOG.info("scenario 2");
    } catch (Exception e) {
        e.printStackTrace();
    }
    LOG.info("end of  -- MonitoringController.getDestinations()");
}

DAO

public List<String> fetchDestinations(String destinationName)
        throws Exception {
    LOG.info("start of  -- MonitorDAOImpl.fetchDestinations()");
    LOG.info("scenario 3");

    return this.jdbcTemplate.query(this.fetchDestinationsQuery, new Object[]{"%" + destinationName.toUpperCase() + "%",}, new RowMapper<String>(){
        public String mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            return resultSet.getString((EmsTypes.DESTINATION.getValue()));

        }
        });
}

LOGSです。

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT DISTINCT destination FROM ems_monit
(destination) like ?]; SQL state [     ]; error code [-313]; DB2 SQL error: SQLCODE: -313, SQLSTATE:      , SQLERRMC: null; nested exception is com.ibm.db2.j
-313, SQLSTATE:      , SQLERRMC: null
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
        at com.macys.platform.messaging.ems.monitoring.dao.MonitorDAOImpl.fetchDestinations(MonitorDAOImpl.java:191)
        at com.macys.platform.messaging.ems.monitoring.controller.MonitoringController.getDestinations(MonitoringController.java:216)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
        at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)
        at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:125)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:619)
Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -313, SQLSTATE:      , SQLERRMC: null
        at com.ibm.db2.jcc.c.fg.e(fg.java:1596)
        at com.ibm.db2.jcc.c.fg.a(fg.java:1206)
        at com.ibm.db2.jcc.b.gb.g(gb.java:140)
        at com.ibm.db2.jcc.b.gb.a(gb.java:39)
        at com.ibm.db2.jcc.b.w.a(w.java:34)
        at com.ibm.db2.jcc.b.vb.g(vb.java:139)
        at com.ibm.db2.jcc.c.fg.n(fg.java:1177)
        at com.ibm.db2.jcc.c.gg.eb(gg.java:1862)
        at com.ibm.db2.jcc.c.gg.d(gg.java:2295)
        at com.ibm.db2.jcc.c.gg.V(gg.java:424)
        at com.ibm.db2.jcc.c.gg.executeQuery(gg.java:407)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
        at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:643)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
        ... 61 more

解決方法は？

エラーメッセージが表示されます。

指定されたホスト変数の数がパラメータ・マーカの数と等しくありません。

参照 IBMナレッジ・センター をご覧ください。

クエリには2つのパラメータが必要です。しかし、あなたの配列にはパラメータが1つしかありません。そのため、destination を 2 回配列に追加する必要があります。