jdbc连接突然开始检查严格类型并抛出类型不兼容的java.sql.sqlexception

8aqjt8rx  于 2021-06-18  发布在  Mysql
关注(0)|答案(0)|浏览(239)

我在执行一个简单的 COUNT(*) 来自java的存储过程。我得到以下java.sql.sqlexception与不兼容的类型。请参阅示例存储过程，除了 COUNT(*) .
奇怪的是，下面的例外并不是一直都发生，而是发生在一段时间之后。请注意 COUNT 在下面的过程中，将始终返回非常小的值，如40或50。事实上它从来不会变成3位数，所以它应该是整数。
我无法理解的是，如果它是实际的数据类型不匹配，那么为什么它有时会工作，而突然停止工作，而数据库上没有发生任何数据更新。
所以现在一切正常，几分钟后或几次请求后突然停止工作。它是非常随机的，在数据库中的数据没有任何变化的情况下，它突然开始抛出下面的错误。如果我重新启动服务器或重新部署相同的代码而不更改数据库中的任何代码或数据，它就会重新开始工作。它正在嵌入式tomcat环境中部署这个。
请检查下面的jdbc连接代码加上配置，我正在做连接到数据库。另外请注意，保存count列的值的变量定义为整数，而不是此时的长度。我可能可以通过简单地改变变量类型来解决这个问题，但我不确定我是否想改变它，而不理解为什么有时它工作正常，突然开始崩溃。
有许多数据点抛出类似类型的类型失败错误，因此将其更改为所有这些错误可能很困难。顺便说一句，如果我重新启动服务器或重新部署相同的代码而不更改数据库中的任何代码或数据，它就会重新开始工作。
请找出以下例外，它有时抛出，但不是所有的时间。

2018-10-11T19:50:45.175782Z app[web.1]: {"remoteAddress": "172.28.","serverName": "test","remoteUser": "-","timeLocal": "11/Oct/2018:19:50:45 +0000","request": "GET /v1/test?input=ABCD&_type=json&limit=200 HTTP/1.1","status": "500","bodyBytesSent": 127,"httpReferer": "-","httpUserAgent": "-","requestTime": "0.013","upstreamCacheStatus": "MISS","httpXForwardedFor": "172.28.9.10","suuid": "-","puuid": "-","env": "PPE"}
2018-10-11T19:50:45.181112Z app[web.1]: 2018-10-11T19:50:45,178 test ERROR [http-nio-8088-exec-2,,,] com.aaa.us.test.ws.dao.DataDao: [proc_test] Failed to fetch data for input - ABCD
2018-10-11T19:50:45.181152Z app[web.1]: java.sql.SQLException: Cannot set count: incompatible types, cannot convert java.lang.Long to int Query: CALL proc_test(?); Parameters: [ABCD]
2018-10-11T19:50:45.181162Z app[web.1]:     at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:527) ~[commons-dbutils-1.7.jar!/:1.7]
2018-10-11T19:50:45.181186Z app[web.1]:     at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:391) ~[commons-dbutils-1.7.jar!/:1.7]
2018-10-11T19:50:45.1812Z app[web.1]:   at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:329) ~[commons-dbutils-1.7.jar!/:1.7]
2018-10-11T19:50:45.181213Z app[web.1]:     at com.aaa.us.test.ws.dao.getData(DataDao.java:200) [classes!/:1.0.1]
2018-10-11T19:50:45.181228Z app[web.1]:     at com.aaa.us.test.ws.DataImpl.getData(DataImpl.java:98) [classes!/:1.0.1]
2018-10-11T19:50:45.181241Z app[web.1]:     at sun.reflect.GeneratedMethodAccessor92.invoke(Unknown Source) ~[?:?]
2018-10-11T19:50:45.181252Z app[web.1]:     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_181-heroku]
2018-10-11T19:50:45.181265Z app[web.1]:     at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_181-heroku]
2018-10-11T19:50:45.181275Z app[web.1]:     at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179) [cxf-core-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181286Z app[web.1]:     at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96) [cxf-core-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181294Z app[web.1]:     at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:192) [cxf-rt-frontend-jaxrs-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181306Z app[web.1]:     at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:103) [cxf-rt-frontend-jaxrs-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181316Z app[web.1]:     at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59) [cxf-core-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181326Z app[web.1]:     at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96) [cxf-core-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181352Z app[web.1]:     at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308) [cxf-core-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181364Z app[web.1]:     at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121) [cxf-core-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181376Z app[web.1]:     at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:267) [cxf-rt-transports-http-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181385Z app[web.1]:     at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234) [cxf-rt-transports-http-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181396Z app[web.1]:     at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208) [cxf-rt-transports-http-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181406Z app[web.1]:     at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160) [cxf-rt-transports-http-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181414Z app[web.1]:     at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:191) [cxf-rt-transports-http-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181423Z app[web.1]:     at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:301) [cxf-rt-transports-http-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181432Z app[web.1]:     at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:225) [cxf-rt-transports-http-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.182000+00:00 system[router]: at=info method=GET path=/v1/data?input=ABCD&_type=json&limit=10 host=test fwd="172.28.9.10" zone=va container=web.1 connect=0ms service=13ms status=500 bytes=475 request_id=e1aa4b25-f8ab-48c4-a89f-09fe9867d273
2018-10-11T19:50:45.18144Z app[web.1]:  at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181449Z app[web.1]:     at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:276) [cxf-rt-transports-http-3.2.0.jar!/:3.2.0]
2018-10-11T19:50:45.181458Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181467Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181476Z app[web.1]:     at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat-embed-websocket-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181486Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181497Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181508Z app[web.1]:     at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110) [spring-boot-actuator-1.5.9.RELEASE.jar!/:1.5.9.RELEASE]
2018-10-11T19:50:45.181516Z app[web.1]:     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181524Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181533Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181544Z app[web.1]:     at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181553Z app[web.1]:     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181565Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181577Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181587Z app[web.1]:     at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181601Z app[web.1]:     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181612Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181622Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181633Z app[web.1]:     at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181644Z app[web.1]:     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181656Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181666Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181677Z app[web.1]:     at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181688Z app[web.1]:     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181697Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181708Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.23.jar!/:8.5.23]
2018-10-11T19:50:45.181721Z app[web.1]:     at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106) [spring-boot-actuator-1.5.9.RELEASE.jar!/:1.5.9.RELEASE]
2018-10-11T19:50:45.181731Z app[web.1]:     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]
2018-10-11T19:50:45.181742Z app[web.1]:     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.23.jar!/:8.5.23]

请注意，我们在这里使用apache dbutil commons库连接到db。

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

    try {
        QueryRunner run = new QueryRunner(readDataSource);

        ResultSetHandler<Dto> h1 = new BeanHandler(Dto.class);
        Dto data = (run.query("CALL proc_WS_GetAlpsHoldings_Metadata(?);", h1, input));

            response.setMeta(extendedMetaModel(intPage, intLimit, holdings.size(), data.getCount(), input, null, data.getDate()));

    } catch (SQLException ex) {
        log.error("[proc_test] Failed to fetch data for input - " + input, ex);
        throw new GenericException().errorCode(500).errorMessage("Failed to fetch data for input - " + input);
    }

我将count value定义为int的dto类如下所示。

public class Dto {

    private String date;

    private String inceptionDate;

    private int count;

    public String getAsOfDate() {
        return asOfDate;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }
}

请在下面找到我们设置的jdbc连接池属性。

datasource:
  dbClientvendordata:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://testmysqlread.something.com:3306/db_testdb?useOldAliasMetadataBehavior=true
    username: "test"
    password: "test"
    auth: Container
    maxActive: 10
    maxIdle: 1
    maxWait: 120000
    testWhileIdle: true
    timeBetweenEvictionRunsMillis: 10000
    minEvictableIdleTimeMillis: 60000
    removeAbandoned: true
    removeAbandonedTimeout: 300
    noAccessToProcedureBodies: true

请查找下面的简单存储过程代码。

CREATE PROCEDURE `proc_test`(
    input VARCHAR (10)
)
BEGIN
    SELECT 
        `column1` AS `Ticker`, 
        DATE_FORMAT(`date`, '%Y-%m-%d') AS `date`,
        COUNT(*) AS `count` 
    FROM 
        `db_testdb`.`table1`
    WHERE
        `column1` = input
    GROUP BY 
        `column1`;
END$$

DELIMITER ;
Javamysqljdbcmysql-connectorapache-commons-dbutils

暂无答案！

目前还没有任何答案，快来回答吧！

我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com