Spring Boot2.X+mybatis+Druid+PageHelper实现多数据源并分页，支持多个字段动态排序，结构层级分明，代码耦合，框架入门

一、SpringBoot整合Mybatis、Druid和PageHelper 并实现多数据源和分页，支持多个字段动态排序，其中对分页插件进行了封装，满足于任何场景的开发

Druid是一个数据库连接池。Druid可以说是目前最好的数据库连接池！因其优秀的功能、性能和扩展性方面，深受开发人员的青睐。

Druid已经在阿里巴巴部署了超过600个应用，经过一年多生产环境大规模部署的严苛考验。Druid是阿里巴巴开发的号称为监控而生的数据库连接池！

Druid的主要功能如下:

是一个高效、功能强大、可扩展性好的数据库连接池。
可以监控数据库访问性能。
数据库密码加密
获得SQL执行日志
扩展JDBC

二、首先是Maven依赖，这里只贴部分依赖了，其他可以在本人gitee仓库获取 代码地址

_<_dependency_> _ _<_groupId_>com.github.pagehelper</_groupId_> _ _<_artifactId_>pagehelper-spring-boot-starter</_artifactId_> _ _<_version_>1.3.0</_version_> _ _</_dependency_> _

_<_dependency_> _ _<_groupId_>com.alibaba</_groupId_> _ _<_artifactId_>druid</_artifactId_> _ _<_version_>1.1.8</_version_> _ _</_dependency_> _ _<_dependency_> _ _<_groupId_>mysql</_groupId_> _ _<_artifactId_>mysql-connector-java</_artifactId_> _ _<_version_>8.0.11</_version_> _ _</_dependency_> _ _<_dependency_> _ _<_groupId_>org.mybatis.spring.boot</_groupId_> _ _<_artifactId_>mybatis-spring-boot-starter</_artifactId_> _ _<_version_>1.2.0</_version_> _ _</_dependency_> _ _<_dependency_> _ _<_groupId_>org.springframework.boot</_groupId_> _ _<_artifactId_>spring-boot-starter</_artifactId_> _ _</_dependency_> _ _<_dependency_> _ _<_groupId_>org.springframework.boot</_groupId_> _ _<_artifactId_>spring-boot-starter-web</_artifactId_> _ _</_dependency_> _ _<_dependency_> _ _<_groupId_>com.alibaba</_groupId_> _ _<_artifactId_>druid-spring-boot-starter</_artifactId_> _ _<_version_>1.1.14</_version_> _ _</_dependency_> _ _<_dependency_> _ _<_groupId_>log4j</_groupId_> _ _<_artifactId_>log4j</_artifactId_> _ _<_version_>1.2.17</_version_> _ _</_dependency_> _ _<_dependency_> _ _<_groupId_>org.apache.commons</_groupId_> _ _<_artifactId_>commons-lang3</_artifactId_> _ </_dependency_>

三、配置方面，主要的只需要在application.yml添加如下就可以了

server: port: 8080 tomcat: uri-encoding: utf-8

# 第一个数据源默认数据源 master: datasource: driverClassName: com.mysql.cj.jdbc.Driver # _这里换成你的数据源账号密码 _ url: jdbc:mysql://xxxxx:3306/hwswgp?useUnicode=true&characterEncoding=utf-8&&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8 username: xxxx password: xxxx # 第二个数据源 cluster: datasource: driverClassName: com.mysql.cj.jdbc.Driver # _这里换成你的数据源账号密码 _ url: jdbc:mysql://xxxxx:3306/hwswioc?useUnicode=true&characterEncoding=utf-8&&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8 username: xxxx password: xxxxx # 连接池的配置信息 # 初始化大小，最小，最大 spring: datasource: type: com.alibaba.druid.pool.DruidDataSource initialSize: 5 minIdle: 5 maxActive: 20 # _配置获取连接等待超时的时间 _ maxWait: 60000 # _配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 _ timeBetweenEvictionRunsMillis: 60000 # _配置一个连接在池中最小生存的时间，单位是毫秒 _ minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false # 打开__PSCache__，并且指定每个连接上__PSCache__的大小 _ poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 # 配置监控统计拦截的__filters__，去掉后监控界面__sql__无法统计，'wall'__用于防火墙 _ filters: stat,wall,log4j # _通过__connectProperties__属性来打开__mergeSql__功能；慢__SQL__记录 _ connectionProperties: druid: stat: mergeSql: true slowSqlMillis: 5000 logging: level: com: debug

四、成功添加了配置文件之后，我们再来编写Druid相关的类。

首先是MasterDataSourceConfig.java这个类，这个是默认的数据源配置类

package com.atxiaodei.pagehelp.mybatispagehelper.config; import com.alibaba.druid.pool.DruidDataSource; import com.github.pagehelper.PageInterceptor; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; import java.sql.SQLException; import java.util.Properties; /** _ * @author wangmeng _ * @date 2020/9/22 _ * 第一数据源配置类 _ **/ @Configuration @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory") public class MasterDataSourceConfig { _ // PACKAGE类型是public 主数据源持久层就要扫描master包下 public static final String PACKAGE = "com.atxiaodei.pagehelp.mybatispagehelper.dao.master"; // 主数据源扫描master下面的 *.xml public static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml"; @Value("${master.datasource.url}") _ private String url; @Value_("${master.datasource.username}") _ private String username; @Value_("${master.datasource.password}") _ private String password; @Value_("${master.datasource.driverClassName}") _ private String driverClassName; @Value_("${spring.datasource.initialSize}") _ private int initialSize; @Value_("${spring.datasource.minIdle}") _ private int minIdle; @Value_("${spring.datasource.maxActive}") _ private int maxActive; @Value_("${spring.datasource.maxWait}") _ private int maxWait; @Value_("${spring.datasource.timeBetweenEvictionRunsMillis}") _ private int timeBetweenEvictionRunsMillis; @Value_("${spring.datasource.minEvictableIdleTimeMillis}") _ private int minEvictableIdleTimeMillis; @Value_("${spring.datasource.validationQuery}") _ private String validationQuery; @Value_("${spring.datasource.testWhileIdle}") _ private boolean testWhileIdle; @Value_("${spring.datasource.testOnBorrow}") _ private boolean testOnBorrow; @Value_("${spring.datasource.testOnReturn}") _ private boolean testOnReturn; @Value_("${spring.datasource.poolPreparedStatements}") _ private boolean poolPreparedStatements; @Value_("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}") _ private int maxPoolPreparedStatementPerConnectionSize; @Value_("${spring.datasource.filters}") _ private String filters; @Value_("{spring.datasource.connectionProperties}") _ private String connectionProperties; @Bean_(name = "masterDataSource") _ @Primary //标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean 优先被考虑。 public DataSource masterDataSource_() { _ DruidDataSource dataSource = new DruidDataSource_(); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password); dataSource.setDriverClassName(driverClassName); //具体配置 dataSource.setInitialSize(initialSize); dataSource.setMinIdle(minIdle); dataSource.setMaxActive(maxActive); dataSource.setMaxWait(maxWait); dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); dataSource.setValidationQuery(validationQuery); dataSource.setTestWhileIdle(testWhileIdle); dataSource.setTestOnBorrow(testOnBorrow); dataSource.setTestOnReturn(testOnReturn); dataSource.setPoolPreparedStatements(poolPreparedStatements); dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); try { _ dataSource.setFilters(filters); } catch (SQLException e) { _ e.printStackTrace(); } _ dataSource.setConnectionProperties(connectionProperties); return dataSource; } _ @Bean(name = "masterTransactionManager") _ @Primary public DataSourceTransactionManager masterTransactionManager_() { _ return new DataSourceTransactionManager_(masterDataSource()); } @Bean(name = "masterSqlSessionFactory") _ @Primary public SqlSessionFactory masterSqlSessionFactory_(@Qualifier("masterDataSource")_ DataSource masterDataSource_) _ throws Exception { _ final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); sessionFactory.setDataSource(masterDataSource); sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver() _ .getResources_(MasterDataSourceConfig.MAPPER_LOCATION)); //分页插件 Interceptor interceptor = new PageInterceptor(); Properties properties = new Properties(); //数据库 properties.setProperty("helperDialect", "mysql"); //是否将参数offset作为PageNum使用 properties.setProperty("offsetAsPageNum", "true"); //是否进行count查询 properties.setProperty("rowBoundsWithCount", "true"); //是否分页合理化 properties.setProperty("reasonable", "false"); interceptor.setProperties(properties); sessionFactory.setPlugins(new Interceptor_[] {interceptor}); return sessionFactory.getObject_()_; } }

第二数据源前往gitee代码仓库查看地址下载：代码地址

其中这两个注解说明下:

@Primary ：标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean
优先被考虑。多数据源配置的时候注意，必须要有一个主数据源，用 @Primary 标志该 Bean。
@MapperScan：扫描 Mapper 接口并容器管理。

需要注意的是sqlSessionFactoryRef 表示定义一个唯一 SqlSessionFactory 实例。

_四、_上面的配置完之后，就可以将Druid作为连接池使用了。但是Druid并不简简单单的是个连接池，它也可以说是一个监控应用，它自带了web监控界面，可以很清晰的看到SQL相关信息。

在SpringBoot中运用Druid的监控作用，只需要编写StatViewServlet和WebStatFilter类，实现注册服务和过滤规则。这里我们可以将这两个写在一起，使用@Configuration和@Bean。

package com.atxiaodei.pagehelp.mybatispagehelper.config; import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import com.alibaba.druid.util.Utils; import lombok.extern.slf4j.Slf4j; import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty; 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.servlet.*; import java.io.IOException; /** _ * @author wangmeng _ * @date 2020/9/22 _ * druid__监控界面设置 _ **/ @Configuration @Slf4j public class DruidConfiguration { _ @Bean public ServletRegistrationBean druidStatViewServle() { _ //注册服务 ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); // 白名单(为空表示,所有的都可以访问,多个IP的时候用逗号隔开) servletRegistrationBean.addInitParameter_("allow", "127.0.0.1"); // IP黑名单 (存在共同时，deny优先于allow) servletRegistrationBean.addInitParameter("deny", "127.0.0.2"); // 设置登录的用户名和密码 servletRegistrationBean.addInitParameter("loginUsername", "xiaodei"); servletRegistrationBean.addInitParameter("loginPassword", "123456"); // 是否能够重置数据. servletRegistrationBean.addInitParameter("resetEnable", "false"); return servletRegistrationBean; } @Bean public FilterRegistrationBean druidStatFilter() { _ FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean_(new WebStatFilter()); // 添加过滤规则 filterRegistrationBean.addUrlPatterns("/*"); // 添加不需要忽略的格式信息 filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); log.info("druid初始化成功!")_; return filterRegistrationBean; }

/** _ * 去除监控页面底部的广告 _ */ _ @SuppressWarnings({ "rawtypes", "unchecked" }) _ @Bean @ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true") _ public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties) _ { // 获取web监控页面的参数 DruidStatProperties.StatViewServlet config = properties.getStatViewServlet_(); // 提取common.js的配置路径 String pattern = config.getUrlPattern()_ != null ? config.getUrlPattern_()_ : "/druid/*"; String commonJsPattern = pattern.replaceAll_("\\*", "js/common.js"); final String filePath = "support/http/resources/js/common.js"; // 创建filter进行过滤 Filter filter = new Filter() _ { @Override public void init_(javax.servlet.FilterConfig filterConfig)_ throws ServletException { _ } @Override public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) _ throws IOException, ServletException { _ chain.doFilter(request, response); // 重置缓冲区，响应头不会被重置 response.resetBuffer(); // 获取common.js String text = Utils.readFromResource(filePath); // 正则替换banner, 除去底部的广告信息 text = text.replaceAll("<a.*?banner\">
", ""); text = text.replaceAll("powered.*?shrek.wang", ""); response.getWriter().write(text); } @Override public void destroy() _ { } }; FilterRegistrationBean registrationBean = new FilterRegistrationBean_(); registrationBean.setFilter(filter); registrationBean.addUrlPatterns(commonJsPattern)_; return registrationBean; } }