SpringBoot多数据源配置下集成MyBatis、PageHelper以及druid监控

骑猪看日落 2022-02-27 12:10 239阅读 0赞

**Demo项目采用Springboot  2.0.3RELEASE版本**

**使用IDEA创建Spring boot项目后，在pom.xml中添加如下依赖**

**          
        <dependency>  
            <groupId>org.mybatis.spring.boot</groupId>  
            <artifactId>mybatis-spring-boot-starter</artifactId>  
            <version>1.3.2</version>  
        </dependency>  
   
          
        <dependency>  
            <groupId>com.github.pagehelper</groupId>  
            <artifactId>pagehelper-spring-boot-starter</artifactId>  
            <version>1.2.3</version>  
        </dependency>  
   
          
        <dependency>  
            <groupId>com.alibaba</groupId>  
            <artifactId>druid</artifactId>  
            <version>1.0.18</version>  
        </dependency>  
   
          
        <dependency>  
            <groupId>com.oracle</groupId>  
            <artifactId>ojdbc6</artifactId>  
            <version>11.2.0.4</version>  
        </dependency>  
   
          
        <dependency>  
            <groupId>mysql</groupId>  
            <artifactId>mysql-connector-java</artifactId>  
        </dependency>  
    <build>  
        <plugins>  
            <plugin>  
                <groupId>org.mybatis.generator</groupId>  
                <artifactId>mybatis-generator-maven-plugin</artifactId>  
                <version>1.3.2</version>  
                <executions>  
                    <execution>  
                        <id>Generate MyBatis Artifacts</id>  
                        <goals>  
                            <goal>generate</goal>  
                        </goals>  
                    </execution>  
                </executions>  
                <configuration>  
                      
                    <configurationFile>src/main/resources/generator/generatorConfig.xml</configurationFile>  
                    <verbose>true</verbose>  
                    <overwrite>true</overwrite>  
                </configuration>  
                <dependencies>  
                      
                    <dependency>  
                        <groupId>mysql</groupId>  
                        <artifactId>mysql-connector-java</artifactId>  
                        <version>5.1.46</version>  
                    </dependency>  
                      
                    <dependency>  
                        <groupId>com.oracle</groupId>  
                        <artifactId>ojdbc6</artifactId>  
                        <version>11.2.0.4</version>  
                    </dependency>  
                      
                    <dependency>  
                        <groupId>org.mybatis.generator</groupId>  
                        <artifactId>mybatis-generator-core</artifactId>  
                        <version>1.3.2</version>  
                    </dependency>  
                </dependencies>  
            </plugin>  
            <plugin>  
                <groupId>org.springframework.boot</groupId>  
                <artifactId>spring-boot-maven-plugin</artifactId>  
                <configuration>  
                    <classifier>exec</classifier>  
                </configuration>  
            </plugin>  
        </plugins>  
    </build>**

配置文件使用yml格式，在application.yml文件中添加数据库连接信息，本示例中同时使用MySQL和Oracle数据库作为数据库源

spring:  
  datasource:  
    mysql:  
      url: jdbc:mysql://localhost:3306/customerservice?useUnicode=true&characterEncoding=utf8&useSSL=false  
      username: root  
      password: 123  
      driverClassName: com.mysql.jdbc.Driver  
      initialSize: 5 \#初始化大小  
      minIdle: 5     \#最小  
      maxActive: 20  \#最大  
      maxWait: 60000 \#等待超时时间  
      timeBetweenEvictionRunsMillis: 60000 \# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒  
      minEvictableIdleTimeMillis: 30000 \# 配置一个连接在池中最小生存的时间，单位是毫秒  
      validationQuery: SELECT 1 FROM DUAL  
      testWhileIdle: true \#是否在连接空闲一段时间后检测其可用性  
      testOnBorrow: false   \#是否在获得连接后检测其可用性  
      testOnReturn: false  \#是否在连接放回连接池后检测其可用性  
      poolPreparedStatements: true  \#打开PSCache，并且指定每个连接上PSCache的大小  
      maxPoolPreparedStatementPerConnectionSize: 20  
      filters: stat,wall,log4j2  \#配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙  
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 \# 通过connectProperties属性来打开mergeSql功能；慢SQL记录  
      type: com.alibaba.druid.pool.DruidDataSource  
    oracle:  
      url: jdbc:oracle:thin:@oracle:1521:orcl  
      username: scott  
      password: orcl  
      driverClassName: oracle.jdbc.driver.OracleDriver  
      initialSize: 5 \#初始化大小  
      minIdle: 5     \#最小  
      maxActive: 20  \#最大  
      maxWait: 60000 \#等待超时时间  
      timeBetweenEvictionRunsMillis: 60000 \# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒  
      minEvictableIdleTimeMillis: 30000 \# 配置一个连接在池中最小生存的时间，单位是毫秒  
      validationQuery: SELECT 1 FROM DUAL  
      testWhileIdle: true \#是否在连接空闲一段时间后检测其可用性  
      testOnBorrow: false \#是否在获得连接后检测其可用性  
      testOnReturn: false \#是否在连接放回连接池后检测其可用性  
      poolPreparedStatements: true  \#打开PSCache，并且指定每个连接上PSCache的大小  
      maxPoolPreparedStatementPerConnectionSize: 20  
      filters: stat,wall,log4j2  \#配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙  
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 \# 通过connectProperties属性来打开mergeSql功能；慢SQL记录  
      type: com.alibaba.druid.pool.DruidDataSource

### **分别新建MySQL和Oracle数据源的配置类，如下：** ###

@Configuration  
@MapperScan(basePackages = DataSource1Config.BASE\_PACKAGE, sqlSessionFactoryRef = "datasource1SqlSessionFactory")  
public class DataSource1Config \{  
   
    private static final Logger log = LogManager.getLogger(DataSource1Config.class);  
   
    static final String BASE\_PACKAGE = "com.transcend.demo.dao.datasource1";  
    private static final String MAPPER\_LOCATION = "classpath:mapper/datasource1/\*.xml";  
   
   
    @Primary  
    @Bean(name = "datasource1")  
    @ConfigurationProperties(prefix = "spring.datasource.mysql")  
    public DataSource datasource1() \{  
//        return DataSourceBuilder.create().type(DruidDataSource.class).build();  
        log.info("datasource1 开始初始化。");  
        return new DruidDataSource();  
    \}  
   
    @Primary  
    @Bean(name = "datasource1TransactionManager")  
    public DataSourceTransactionManager datasource1Transaction(@Qualifier("datasource1") DataSource dataSource) \{  
        return new DataSourceTransactionManager(dataSource);  
    \}  
   
    @Primary  
    @Bean(name = "datasource1SqlSessionFactory")  
    public SqlSessionFactory datasource1SqlSessionFactory(@Qualifier("datasource1") DataSource dataSource) throws Exception \{  
        final SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();  
        factoryBean.setDataSource(dataSource);  
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(DataSource1Config.MAPPER\_LOCATION));  
   
        //添加PageHelper插件  
        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);  
        factoryBean.setPlugins(new Interceptor\[\] \{interceptor\});  
   
        return factoryBean.getObject();  
    \}  
   
    @Primary  
    @Bean(name = "datasource1SqlSessionTemplate")  
    public SqlSessionTemplate datasource1SqlSessionTemplate(@Qualifier("datasource1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception \{  
        return new SqlSessionTemplate(sqlSessionFactory);  
    \}  
\}

@Configuration  
@MapperScan(basePackages = DataSource2Config.BASE\_PACKAGE,sqlSessionFactoryRef = "datasource2SqlSessionFactory")  
public class DataSource2Config \{  
   
    private static final Logger log = LogManager.getLogger(DataSource2Config.class);  
   
    static final String BASE\_PACKAGE = "com.transcend.demo.dao.datasource2";  
    private static final String MAPPER\_LOCATION = "classpath:mapper/datasource2/\*.xml";  
   
   
    @Bean(name = "datasource2")  
    @ConfigurationProperties(prefix = "spring.datasource.oracle")  
    public DataSource datasource2() \{  
//        return DataSourceBuilder.create().type(DruidDataSource.class).build();  
        log.info("datasource2 开始初始化。");  
        return new DruidDataSource();  
    \}  
   
    @Bean(name = "datasource2TransactionManager")  
    public DataSourceTransactionManager datasource2Transaction(@Qualifier("datasource2") DataSource dataSource) \{  
        return new DataSourceTransactionManager(dataSource);  
    \}  
   
    @Bean(name = "datasource2SqlSessionFactory")  
    public SqlSessionFactory datasource2SqlSessionFactory(@Qualifier("datasource2") DataSource dataSource) throws Exception \{  
        final SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();  
        factoryBean.setDataSource(dataSource);  
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(DataSource2Config.MAPPER\_LOCATION));  
   
        //添加PageHelper插件  
        Interceptor interceptor = new PageInterceptor();  
        Properties properties = new Properties();  
        //数据库  
        properties.setProperty("helperDialect", "oracle");  
        //是否将参数offset作为PageNum使用  
        properties.setProperty("offsetAsPageNum", "true");  
        //是否进行count查询  
        properties.setProperty("rowBoundsWithCount", "true");  
        //是否分页合理化  
        properties.setProperty("reasonable", "false");  
   
        interceptor.setProperties(properties);  
        factoryBean.setPlugins(new Interceptor\[\] \{interceptor\});  
   
        return factoryBean.getObject();  
    \}  
   
    @Bean(name = "datasource2SqlSessionTemplate")  
    public SqlSessionTemplate datasource2SqlSessionTemplate(@Qualifier("datasource2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception \{  
        return new SqlSessionTemplate(sqlSessionFactory);  
    \}  
\}

**配置PageHelper分页插件后，由于多数据源，会出现 *在系统中发现了多个分页插件，请检查系统配置！***

**此时需要在 Springboot启动类上添加**

@SpringBootApplication(exclude = PageHelperAutoConfiguration.class)

**接下来就是配置 Druid监控，创建DruidConfig.java文件，如下：**

@Configuration  
@EnableTransactionManagement  
public class DruidConfig \{  
   
    private static final Logger log = LogManager.getLogger(DruidConfig.class);  
   
    @Bean  
    public ServletRegistrationBean<StatViewServlet> druidServlet() \{  
        log.info("开始Druid Servlet初始化配置...");  
        ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>();  
        servletRegistrationBean.setServlet(new StatViewServlet());  
        servletRegistrationBean.addUrlMappings("/druid/\*");  
        Map<String, String> initParameters = new HashMap<>();  
        //用户名  
        initParameters.put("loginUsername", "admin");  
        //密码  
        initParameters.put("loginPassword", "123");  
        // 禁用HTML页面上的“Reset All”功能  
        initParameters.put("resetEnable", "false");  
        // IP白名单 (没有配置或者为空，则允许所有访问)  
        initParameters.put("allow", "127.0.0.1");  
        // IP黑名单 (存在共同时，deny优先于allow)  
        initParameters.put("deny", "");  
        servletRegistrationBean.setInitParameters(initParameters);  
        return servletRegistrationBean;  
    \}  
   
    @Bean  
    public FilterRegistrationBean<WebStatFilter> filterRegistrationBean() \{  
        FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>();  
        filterRegistrationBean.setFilter(new WebStatFilter());  
        filterRegistrationBean.addUrlPatterns("/\*");  
        filterRegistrationBean.addInitParameter("exclusions", "\*.js,\*.gif,\*.jpg,\*.png,\*.css,\*.ico,/druid/\*");  
        return filterRegistrationBean;  
    \}  
\}

**至此，MyBatis以及druid和多数据源 的配置已完成，不过没有mapper.xml并不能成功运行项目，需要首先通过MyBatis-generator来生成。在resource文件夹下创建generator文件夹，并创建generatorConfig.xml文件，如下为generatorConfig.xml文件（参考https://blog.csdn.net/jjlovefj/article/details/71439091）： **

<?xml version="1.0" encoding="UTF-8"?>  
<!DOCTYPE generatorConfiguration  
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"  
        "http://mybatis.org/dtd/mybatis-generator-config\_1\_0.dtd">  
<generatorConfiguration>  
      
    <classPathEntry  location="F:\\workspace\\niuwa-p2ps\\WebContent\\WEB-INF\\lib\\mysql-connector-java-5.1.25.jar"/>  
   
    <context id="myBatis3"  targetRuntime="MyBatis3">  
        <property name="mergeable" value="true" />  
        <property name="javaFileEncoding" value="UTF-8" />  
   
        <commentGenerator>  
            <property name="suppressDate" value="true"/>  
            <property name="suppressAllComments" value="true"/>  
        </commentGenerator>  
   
          
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"  
                        connectionURL="jdbc:mysql://localhost/test" userId="root" password="root">  
        </jdbcConnection>  
        <javaTypeResolver>  
            <property name="forceBigDecimals" value="false"/>  
        </javaTypeResolver>  
   
          
        <javaModelGenerator targetPackage="com.jiangjian.model" targetProject="src">  
            <property name="enableSubPackages" value="true"/>  
            <property name="trimStrings" value="true"/>  
        </javaModelGenerator>  
   
          
        <sqlMapGenerator targetPackage="com.jiangjian.mapping" targetProject="src">  
            <property name="enableSubPackages" value="true"/>  
        </sqlMapGenerator>  
   
          
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.jiangjian.dao"   
            targetProject="src">  
            <property name="enableSubPackages" value="true"/>  
        </javaClientGenerator>  
   
          
        <table tableName="students" domainObjectName="Student" enableCountByExample="false"  
               enableUpdateByExample="false" enableDeleteByExample="false"  
               enableSelectByExample="false" selectByExampleQueryId="false">  
        </table>  
    </context>  
</generatorConfiguration>

修改generatorConfig.xml中的数据库连接信息、数据库表信息以及生成文件位置后，使用 mvn mybatis-generator:generate命令进行生成。

![70][]

以上，已完成Springboot+MyBatis+PageHelper+druid以及多数据源的配置。

原文：[https://blog.csdn.net/zhengyidi1357/article/details/80851856][https_blog.csdn.net_zhengyidi1357_article_details_80851856]

[70]: /images/20220227/7ea1475b758943acb109d75e77ace4b4.png
[https_blog.csdn.net_zhengyidi1357_article_details_80851856]: https://blog.csdn.net/zhengyidi1357/article/details/80851856