No operations allowed after connection closed--转

https://www.jianshu.com/p/1626d41572f2

Spring boot的单数据源配置比较简单，只需要在application.properties配置相关的jdbc连接的参数即可，下面简单的介绍下spring boot的JdbcTemplate多数据源的配置方式，因为我也是参考程序猿DD写的多数据源配置的文章来的。
参考链接： http://www.jianshu.com/p/34730e595a8c
那么我写这篇文章的目的，当然不是照抄一遍啦~~~，主要是参考配置完多数据源之后出现了异常，所以重点还是在配置多数据源之后异常问题的解决上做一些简单的介绍。

JdbcTemplate的多数据源配置

PS:这部分可以参考Spring Boot多数据源配置与使用 ，所以这里直接上配置，简单明了

DataSourceConfig 配置类

/**
 *创建一个Spring配置类，定义两个DataSource用来读取application.properties中的不同配置
 */
@Configuration
public class DataSourceConfig {

    private static Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);
    /************************多数据源配置******************************/
    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix="spring.datasource.primary")
    public DataSource primaryDataSource() {
        logger.info("-------------------- primaryDataSource init ---------------------");
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @Primary
    @ConfigurationProperties(prefix="spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        logger.info("-------------------- secondaryDataSource init ---------------------");
        return DataSourceBuilder.create().build();
    }
    /************************JdbcTemplate设置******************************/
    @Bean(name = "primaryJdbcTemplate")
    public JdbcTemplate primaryJdbcTemplate(
            @Qualifier("primaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

application.properties中DataSource配置

#多数据源支持
spring.datasource.primary.url=jdbc:mysql://localhost:3306/test?tinyInt1isBit=false&useUnicode=true&characterEncoding=utf8
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.secondary.url=jdbc:mysql://localhost:3366/test?tinyInt1isBit=false&useUnicode=true&characterEncoding=utf8
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver

多数据源的调用

/**
 * 多数据源的使用demo
 */
@Service
public class KOLDataService {
    
    private org.slf4j.Logger logger = LoggerFactory.getLogger(KOLDataService.class);
    /***********引入多个JdbcTemplate************/
    @Autowired
    @Qualifier("primaryJdbcTemplate")
    protected JdbcTemplate primaryJdbcTemplate;

    @Autowired
    @Qualifier("secondaryJdbcTemplate")
    protected JdbcTemplate secondaryJdbcTemplate;
    
    public JSONObject query(String user_ids){
        JSONObject data = new JSONObject();
        
        //primary库查询用户手机号
        String query = "select user_id,mobile_id from user where user_id in ("+user_ids+") ";
        List<JSONObject> listData = primaryJdbcTemplate.query(query, new RowMapper<JSONObject>() {
                @Override
                public JSONObject mapRow(ResultSet rs, int rowNum) throws SQLException {
                    JSONObject one = new JSONObject();
                    one.put("user_id",rs.getInt("user_id"));
                    one.put("mobile_id",rs.getString("mobile_id"));
                    return one;
                }
            });
        for(int i = 0;i<listData.size;i++){
            JSONObject one = listData.get(i);
            //secondary库根据用户名、手机号得到它的历史浏览信息
            query = "select title,content,create_time from message_info "+
            "where user_id="+one.getInt("user_id")+" and mobile_id = ""+one.getString("mobile_id")+""";
            List<JSONObject> messageData = secondaryJdbcTemplate.query(query, new RowMapper<JSONObject>() {
                @Override
                public JSONObject mapRow(ResultSet rs, int rowNum) throws SQLException {
                    JSONObject one = new JSONObject();
                    one.put("user_id",rs.getInt("user_id"));
                    one.put("mobile_id",rs.getString("mobile_id"));
                    return one;
                }
            });
            //讲历史浏览信息插入到指定用户下
            one.put("info_list",messageData);
            listData.set(i,one);
            
        }
        
        //封装数据
        data.put("count",listData.size);
        data.put("list",listData);
        return data;
        
    } 
    
}

上面就是多数据源的相关配置跟使用，如果application.properties中datasource使用的是上述中的application.properties中DataSource配置的话，项目运行8个小时(为啥就8小时呢？继续往下看就明白了)之后将会出现如下错误信息：

org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
        at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:305)
        at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:329)
        at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:214)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:134)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:97)
        at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:480)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:832)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:743)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:961)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:895)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:858)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:87)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
        at com.mysql.jdbc.Util.getInstance(Util.java:387)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
        at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1246)
        at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1241)
        at com.mysql.jdbc.ConnectionImpl.getMetaData(ConnectionImpl.java:2946)
        at com.mysql.jdbc.ConnectionImpl.getMetaData(ConnectionImpl.java:2941)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
        at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
        at com.sun.proxy.$Proxy64.getMetaData(Unknown Source)
        at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:294)
        ... 63 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

No operations allowed after connection closed错误原因及解决

之所以会出现这个异常，是因为MySQL5.0以后针对超长时间DB连接做了一个处理，那就是如果一个DB连接在无任何操作情况下过了8个小时后(Mysql 服务器默认的“wait_timeout”是8小时)，Mysql会自动把这个连接关闭。这就是问题的所在，在连接池中的connections如果空闲超过8小时，mysql将其断开，而连接池自己并不知道该connection已经失效，如果这时有 Client请求connection，连接池将该失效的Connection提供给Client，将会造成上面的异常。
所以配置datasource时需要配置相应的连接池参数，定是去检查连接的有效性，定时清理无效的连接。
解决办法：
在application.properties的primary数据源的配置下添加如下连接池配置:

#以下为连接池的相关参数配置
spring.datasource.primary.max-idle=10
spring.datasource.primary.max-wait=10000
spring.datasource.primary.min-idle=5
spring.datasource.primary.initial-size=5
spring.datasource.primary.validation-query=SELECT 1
spring.datasource.primary.test-on-borrow=false
spring.datasource.primary.test-while-idle=true
spring.datasource.primary.time-between-eviction-runs-millis=18800

在application.properties的primary数据源的配置下添加如下连接池配置:

#以下为连接池的相关参数配置
spring.datasource.secondary.max-idle=10
spring.datasource.secondary.max-wait=10000
spring.datasource.secondary.min-idle=5
spring.datasource.secondary.initial-size=5
spring.datasource.secondary.validation-query=SELECT 1
spring.datasource.secondary.test-on-borrow=false
spring.datasource.secondary.test-while-idle=true
spring.datasource.secondary.time-between-eviction-runs-millis=18800

这样上述异常就得到解决，其实出现该问题的原因，还是自己本人当时忽略了连接池的相关配置，以及对一些常见连接池的配置尚有欠缺，这里再补充一个小知识点，spring boot默认会优先使用的连接池是tomcat连接池，前提是在tomcat连接池可用的情况下