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连接池可用的情况下

最新文章

  1. 【SAP业务模式】之ICS（七）：IDOC配置
  2. 公钥私钥和RSA算法
  3. select 标签的两种方式（以动态插入为例）
  4. eclipse或者myeclipse安装svn报错”unable to load default svn client”
  5. (九)errno和perror、标准IO
  6. C语言初学者代码中的常见错误与瑕疵(7)
  7. 不带www的域名跳转至www域名
  8. 转：ASP.NET中的SESSION实现与操作方法
  9. static的应用以及静态与非静态的区别
  10. partial函数-python学习
  11. bash:command not found
  12. 文本框按键事件onkeydown、onkeypress、onkeyup区别
  13. 用函数式的 Swift 实现图片转字符画的功能
  14. 关于SQLSERVER去掉如何重复值的记录
  15. jquery 如何动态添加、删除class样式方法介绍_jquery_脚本之家
  16. delegate vs event
  17. Codeforces 828B Black Square（简单题）
  18. 解决安装fiddler后IE打开网页提示“代理服务器无响应”
  19. BurpSuiteProxy安装使用
  20. ansible普通用户su切换

热门文章

  1. Educational Codeforces Round 41 D. Pair Of Lines(961D)
  2. 【MongoDB】2、安装MongoDB 2.6.1 on Unbuntu 14.04(学习流水账)
  3. html to canvas
  4. [luoguP1316] 丢瓶盖（二分答案）
  5. Win32编程API 基础篇 -- 5.使用资源
  6. [poj1678]I Love this Game!_博弈论
  7. 洛谷——P1255 数楼梯
  8. python实现汉诺塔算法
  9. 在docker上安装运行mysql实例
  10. HDU 1853 Cyclic Tour（最小费用最大流）