Springboot＆＃x2B; MS SQL Server（Azure Cloud）选择由于连接重置而导致的100K记录失败

Question

我正在使用Mybatis＆amp;使用Springboot框架。 jdbctemplate要通过SQL Server数据库执行选择操作。

以下代码摘要在此处仅发布jdbctemplate

List<TableName> tnames = new ArrayList<>();
jdbcTemplate.query("select * from testdb.table_name", new RowCallbackHandler() {
    public void processRow(ResultSet resultSet) throws SQLException {
        while (resultSet.next()) {
            TableName tn = new TableName();
            tn.setStatus(resultSet.getString("status"));
            tnames.add(tn);
            // process it
        }
    }
});
return tnames;

注意：代码缩短 查询也有4-5个表的加入，该表在SQL Server端的性能很好，并获取了将近100k行。

但是与Springboot集成时，当我应用top（n）标准时，我发现超过47000行，它无法正常工作，而在错误

{"@timestamp":"2022-07-06T04:12:24.161-04:00","message":"InternalServerException Exception : ","loggerSource":"c.m.d.d.a.s.i.r.h.GlobalExceptionHandler","level":"ERROR","stacktrace":"java.io.IOException: Connection reset by peer sun.nio.ch.SocketDispatcher.write0(SocketDispatcher.java) sun.nio.ch.SocketDispatcher.write(SocketDispatcher.java:54) sun.nio.ch.IOUtil.writeFromNativeBuffer(IOUtil.java:113) sun.nio.ch.IOUtil.write(IOUtil.java:79) sun.nio.ch.IOUtil.write(IOUtil.java:50) sun.nio.ch.SocketChannelImpl.write(SocketChannelImpl.java:506)... 10 frames excluded... 114 common frames omittedWrapped by: org.apache.catalina.connector.ClientAbortException: java.io.IOException: Connection reset by peer org.apache.catalina.connector.OutputBuffer.realWriteBytes(OutputBuffer.java:353) org.apache.catalina.connector.OutputBuffer.flushByteBuffer(OutputBuffer.java:783)... 7 frames excluded com.fasterxml.jackson.core.json.UTF8JsonGenerator._flushBuffer(UTF8JsonGenerator.java:2171) com.fasterxml.jackson.core.json.UTF8JsonGenerator.writeFieldName(UTF8JsonGenerator.java:289) com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:726) com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:774) com.fasterxml.jackson.databind.ser.BeanSerializer.serialize(BeanSerializer.java:178) com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serializeContents(CollectionSerializer.java:145) com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serialize(CollectionSerializer.java:107) com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serialize(CollectionSerializer.java:25) com.fasterxml.jackson.databind.ser.DefaultSerializerProvider._serialize(DefaultSerializerProvider.java:480) com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:400) com.fasterxml.jackson.databind.ObjectWriter$Prefetch.serialize(ObjectWriter.java:1514) com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:1007)... 13 frames excluded javax.servlet.http.HttpServlet.service(HttpServlet.java:655) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) javax.servlet.http.HttpServlet.service(HttpServlet.java:764)... 77 frames excluded java.lang.Thread.run(Thread.java:832)Suppressed: java.io.IOException: Connection reset by peer sun.nio.ch.SocketDispatcher.write0(SocketDispatcher.java) sun.nio.ch.SocketDispatcher.write(SocketDispatcher.java:54) sun.nio.ch.IOUtil.writeFromNativeBuffer(IOUtil.java:113) sun.nio.ch.IOUtil.write(IOUtil.java:79) sun.nio.ch.IOUtil.write(IOUtil.java:50) sun.nio.ch.SocketChannelImpl.write(SocketChannelImpl.java:506)... 10 frames excluded... 104 common frames omittedWrapped by: org.apache.catalina.connector.ClientAbortException: java.io.IOException: Connection reset by peer org.apache.catalina.connector.OutputBuffer.realWriteBytes(OutputBuffer.java:353) org.apache.catalina.connector.OutputBuffer.flushByteBuffer(OutputBuffer.java:783)... 7 frames excluded com.fasterxml.jackson.core.json.UTF8JsonGenerator._flushBuffer(UTF8JsonGenerator.java:2171) com.fasterxml.jackson.core.json.UTF8JsonGenerator.close(UTF8JsonGenerator.java:1214)... 1 frames excluded... 93 common frames omitted","logType":"SERVICE"}
{"@timestamp":"2022-07-06T04:12:24.164-04:00","message":"Found 'Content-Type:application/json' in response","loggerSource":"o.s.w.s.m.m.a.HttpEntityMethodProcessor","level":"DEBUG","logType":"SERVICE"}
{"@timestamp":"2022-07-06T04:12:24.166-04:00","message":"Writing [ErrorApiResponse(code=HTTP500, message=HTTP 500 - Internal Server Error, description=java.io.IOExcep (truncated)...]","loggerSource":"o.s.w.s.m.m.a.HttpEntityMethodProcessor","level":"DEBUG","logType":"SERVICE"}
{"@timestamp":"2022-07-06T04:12:24.167-04:00","message":"Failure in @ExceptionHandler com.dev.infra.rest.handlers.GlobalExceptionHandler#handleInternalException(Exception)","loggerSource":"o.s.w.s.m.m.a.ExceptionHandlerExceptionResolver","level":"WARN","stacktrace":"java.io.IOException: Connection reset by peer sun.nio.ch.SocketDispatcher.write0(SocketDispatcher.java) sun.nio.ch.SocketDispatcher.write(SocketDispatcher.java:54) sun.nio.ch.IOUtil.writeFromNativeBuffer(IOUtil.java:113) sun.nio.ch.IOUtil.write(IOUtil.java:79) sun.nio.ch.IOUtil.write(IOUtil.java:50) sun.nio.ch.SocketChannelImpl.write(SocketChannelImpl.java:506)... 10 frames excluded... 108 common frames omittedWrapped by: org.apache.catalina.connector.ClientAbortException: java.io.IOException: Connection reset by peer org.apache.catalina.connector.OutputBuffer.realWriteBytes(OutputBuffer.java:353) org.apache.catalina.connector.OutputBuffer.flushByteBuffer(OutputBuffer.java:783)... 7 frames excluded com.fasterxml.jackson.core.json.UTF8JsonGenerator._flushBuffer(UTF8JsonGenerator.java:2171) com.fasterxml.jackson.core.json.UTF8JsonGenerator.flush(UTF8JsonGenerator.java:1184) com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:1009)... 16 frames excluded javax.servlet.http.HttpServlet.service(HttpServlet.java:655) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) javax.servlet.http.HttpServlet.service(HttpServlet.java:764)... 77 frames excluded java.lang.Thread.run(Thread.java:832)","logType":"SERVICE"}
{"@timestamp":"2022-07-06T04:12:24.168-04:00","message":"Failed to complete request: org.apache.catalina.connector.ClientAbortException: java.io.IOException: Connection reset by peer","loggerSource":"o.s.web.servlet.DispatcherServlet","level":"DEBUG","logType":"SERVICE"}

查询之后抛出

了我尝试使用QUERYTIMEOUT 无法工作。 这里的任何建议都很棒！ 提前致谢 ：）

Answer 1

我建议使用分页，而不是一眼选择所有记录。该连接保持很长时间，并由数据库服务器终止。如果您最终有更多记录，即使您更改连接超时设置，也会失败。请访问我如何在Spring JDBCTEMPLATE中实现分页>知道如何进行分页并检索所有记录