spring boot、mybatis集成druid数据库连接池，实现mysql cluster HA负载均衡访问

1.原理实现介绍

本质来说使用连接池是为了节省创建、关闭数据库连接的资源消耗，从而提升访问的性能。底层还是JDBC的调用，jdbc访问多主mysql有提供loadbalace的形式：

jdbc:mysql:loadbalance://[host1][:port],[host2][:port][,[host3][:port]]...[/[database]] »[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

通过上面这种JDBC形式，即可访问多主mysql，但是实际成果中，如果不做任何参数设置，检测失效mysql连接时间非常长，从而降低了数据库访问时间，特加上了以下参数作为性能调优：

roundRobinLoadBalance=true&failOverReadOnly=false&tcpRcvBuf=1024000&loadBalanceStrategy=bestResponseTime&loadBalanceBlacklistTimeout=300000&loadBalancePingTimeout=1000&selfDestructOnPingMaxOperations=200&queryTimeoutKillsConnection=true&loadBalanceValidateConnectionOnSwapServer=true&connectTimeout=1000

JDBC参数详解：

2.项目配置

引入druid相关的maven依赖

<dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency><!-- 阿里系的Druid依赖包 --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.0.20</version></dependency>

在application.properties中添加druid配置文件

# 驱动配置信息spring.datasource.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.url = jdbc:mysql:loadbalance://ip1:3307,ip2:3307,ip3:3307/test?roundRobinLoadBalance=true&failOverReadOnly=false&tcpRcvBuf=1024000&loadBalanceStrategy=bestResponseTime&loadBalanceBlacklistTimeout=300000&loadBalancePingTimeout=1000&selfDestructOnPingMaxOperations=200&queryTimeoutKillsConnection=true&loadBalanceValidateConnectionOnSwapServer=true&connectTimeout=1000spring.datasource.username = rootspring.datasource.password = unew2&spring.datasource.driverClassName = com.mysql.jdbc.Driver# 连接池的配置信息# 初始化大小，最小，最大spring.datasource.initialSize=5spring.datasource.minIdle=5spring.datasource.maxActive=20# 配置获取连接等待超时的时间spring.datasource.maxWait=60000# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒spring.datasource.timeBetweenEvictionRunsMillis=60000# 配置一个连接在池中最小生存的时间，单位是毫秒spring.datasource.minEvictableIdleTimeMillis=300000spring.datasource.validationQuery=SELECT 1 FROM DUALspring.datasource.testWhileIdle=truespring.datasource.testOnBorrow=falsespring.datasource.testOnReturn=false# 打开PSCache，并且指定每个连接上PSCache的大小spring.datasource.poolPreparedStatements=truespring.datasource.maxPoolPreparedStatementPerConnectionSize=20# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙spring.datasource.filters=stat,wall,log4j# 通过connectProperties属性来打开mergeSql功能；慢SQL记录spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

添加druidConfig.java作为配置生效文件,并添加druid监控页面

import com.alibaba.druid.pool.DruidDataSource;import com.alibaba.druid.support.http.StatViewServlet;import com.alibaba.druid.support.http.WebStatFilter;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.web.servlet.FilterRegistrationBean;import org.springframework.boot.web.servlet.ServletRegistrationBean;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;import java.util.Arrays;import java.util.HashMap;import java.util.Map;@Configurationpublic class DruidConfig {// 将所有前缀为spring.datasource下的配置项都加载到DataSource中@ConfigurationProperties(prefix = "spring.datasource")@Beanpublic DataSource druidDataSource() {return new DruidDataSource();}//配置Druid的监控//1、配置一个管理后台的Servlet@Beanpublic ServletRegistrationBean statViewServlet(){ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");Map<String,String> initParams = new HashMap<>();initParams.put("loginUsername","admin");initParams.put("loginPassword","123456");initParams.put("allow","");//默认就是允许所有访问initParams.put("deny","192.168.15.21");bean.setInitParameters(initParams);return bean;}//2、配置一个web监控的filter@Beanpublic FilterRegistrationBean webStatFilter(){FilterRegistrationBean bean = new FilterRegistrationBean();bean.setFilter(new WebStatFilter());Map<String,String> initParams = new HashMap<>();initParams.put("exclusions","*.js,*.css,/druid/*");bean.setInitParameters(initParams);bean.setUrlPatterns(Arrays.asList("/*"));return bean;}}

此刻mybatis集成druid已经完成了，可以正常使用druid作为数据连接池去连接数据了。

访问http://localhost:8080/druid/login.html 可以查看druid监控页面。

参考资料：/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

/doc/connector-j/5.1/en/connector-j-usagenotes-j2ee-concepts-managing-load-balanced-connections.html