频繁调用的接口中数据库连接无故关闭，出现com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed异常，接口无法响应
问题遇到的现象和发生背景
公司上线了一个扫码支付功能，每天中午吃饭的人在300左右，高峰时期同时扫码就餐的最多不过15个人，但现在每隔两三天支付接口中数据库连接都会关闭，出现com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed和后面请求出现的java.lang.IllegalStateException: Session/EntityManager is closed的异常，导致异常后扫码没有反应，重启tomcat后可以正常运行，但两三天后又会出现此问题。贴上接口代码、异常信息和报错位置，请大家帮忙看下
用代码块功能插入代码，请勿粘贴截图
报错位置是接口中自己封装的用于获取单据id值的代码，以下是支付接口
/**
* 用户消费支付
*
* @param request
* @return
* @throws IOException
*/
public Result paySaleInfo(HttpServletRequest request, PaymentOrder order) throws IOException {
User user = ContextUtils.getLoginUser();
log.info("消费支付:{},操作用户:{}", JSONObject.toJSONString(order), user);
Date date = new Date();
boolean isNew = false;
if (order.getId() == null) {
** order.setId(billNoService.getMaxIntegerID("PaymentOrder")); ** //报错位置，获取单据id
String prefix = "PAY";
order.setBillNo(billNoService.getBillNo(prefix, "PaymentOrder"));
order.setDate(date);
isNew = true;
} else {
if(JpaUtil.linq(PaymentOrder.class).equal("id", order.getId()).exists()) {
PaymentOrder bill = JpaUtil.linq(PaymentOrder.class).equal("id", order.getId()).findOne();
if (bill.getStatus().equals("已支付")) {
return Result.error("此订单交易已结束");
}
}else {
return Result.error("此订单不存在");
}
}
/**剩余代码省略*/
以下是自己封装的获取id值的方法，方法加了同步，按理说不存在线程不安全的问题啊
/**
* 获取主键
* @param 前缀
* @param 表名
* @return
*/
public synchronized Integer getMaxIntegerID(String tableName) {
int no = 1;
BillNo entity;
** if(JpaUtil.linq(BillNo.class).equal("tableName", tableName).equal("prefix", "ID").exists()) { ** //报错位置
entity = JpaUtil.linq(BillNo.class).equal("tableName", tableName).equal("prefix", "ID").findOne();
no = entity.getCount();
no = no + 1;
entity.setCount(no);
JpaUtil.mergeAndFlush(entity);
}else {
entity = new BillNo();
entity.setTableName(tableName);
entity.setPrefix("ID");
entity.setCount(no);
JpaUtil.persistAndFlush(entity);
}
return entity.getCount();
}
以下是数据库部分配置
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.continue-on-error=true
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=true
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=100
spring.datasource.hikari.idle-timeout=500000
spring.datasource.hikari.max-lifetime=540000
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.pool-name=MyHikariCPTT
运行结果及报错内容
日志异常：
2022-11-12 11:36:02.709 [Thread-17360] INFO org.apache.catalina.core.StandardWrapper[173] - 正在等待为Servlet[dispatcherServlet]释放[19]实例
2022-11-12 11:36:03.804 [Thread-17360] INFO org.apache.catalina.core.StandardWrapper[173] - 正在等待为Servlet[dispatcherServlet]释放[19]实例
2022-11-12 11:36:04.898 [Thread-17360] INFO org.apache.catalina.core.StandardWrapper[173] - 正在等待为Servlet[dispatcherServlet]释放[19]实例
2022-11-12 11:36:05.023 [Thread-17360] INFO com.zaxxer.hikari.HikariDataSource[381] - MyHikariCPTT - Shutdown initiated...
2022-11-12 11:36:05.023 [Thread-17360] INFO com.zaxxer.hikari.HikariDataSource[383] - MyHikariCPTT - Shutdown completed.
2022-11-12 11:36:05.023 [http-nio-8091-exec-15] WARN com.zaxxer.hikari.pool.ProxyConnection[161] - MyHikariCPTT - Connection ConnectionID:5511 ClientConnectionId: 741edc07-2f2f-4cf4-9f06-61021c3d47c1 marked as broken because of SQLSTATE(08S01), ErrorCode(0)
com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654)
at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1789)
at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:4838)
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6150)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:402)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2168)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)
at org.hibernate.loader.Loader.doQuery(Loader.java:938)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
at org.hibernate.loader.Loader.doList(Loader.java:2692)
at org.hibernate.loader.Loader.doList(Loader.java:2675)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)
at org.hibernate.loader.Loader.list(Loader.java:2502)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1489)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
at org.hibernate.query.Query.getResultList(Query.java:146)
at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72)
at com.bstek.bdf3.dorado.jpa.lin.impl.LinqImpl.executeCountQuery(LinqImpl.java:583)
at com.bstek.bdf3.dorado.jpa.lin.impl.LinqImpl.count(LinqImpl.java:567)
at com.bstek.bdf3.dorado.jpa.lin.impl.LinqImpl.exists(LinqImpl.java:576)
** at com.cqjysoft.tt.business.common.service.GenaralBillNoService.getMaxIntegerID(GenaralBillNoService.java:56)
at com.cqjysoft.tt.business.common.service.GenaralBillNoService$$FastClassBySpringCGLIB$$e99e7983.invoke()
**
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
at com.cqjysoft.tt.business.common.service.GenaralBillNoService$$EnhancerBySpringCGLIB$$ae140de5.getMaxIntegerID(<generated>)
at com.cqjysoft.tt.business.order.service.OrderService.paySaleInfo(OrderService.java:700)
at com.cqjysoft.tt.business.order.service.OrderService$$FastClassBySpringCGLIB$$a4032cda.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
at com.cqjysoft.tt.business.order.service.OrderService$$EnhancerBySpringCGLIB$$71d16c20.paySaleInfo(<generated>)
at com.cqjysoft.tt.business.order.controller.OrderController.paySaleInfo(OrderController.java:103)
at sun.reflect.GeneratedMethodAccessor478.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle
你好，你这个问题解决不难，主要原因是因为使用了默认的hikari数据库连接池，而没有正确配置数据库连接池；
主要解决方法：
1.使用druid数据库连接池，解决相对简单一些，给一个成功的参考配置
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://ip:3306/zxl_sso_dev?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.datasource.username=用户名
spring.datasource.password=密码
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.initial-size=10
spring.datasource.druid.max-wait=3000
spring.datasource.druid.min-idle=50
spring.datasource.druid.max-active=100
2.在现有基础上进行配置修改，主要寻找配置思路是检测数据库连接时间设置短一些，空闲连接数设置少一些；
把检查项都加上看看
spring.datasource.validation-query=select 1
spring.datasource.test-on-connect=true
spring.datasource.connection-test-query=select 1
提供参考实例，链接：https://www.cnblogs.com/weibanggang/p/9295228.html
【实例讲解详细，代码清晰，值得借鉴】
com.microsoft.sqlserver.jdbc.SQLServerException: 该连接已关闭的解决方案
https://blog.csdn.net/gaoqiao1988/article/details/9235853
希望对你有所帮助
