springboot开源热门项目-bootdo修改支持多数据源

本文链接：https://blog.csdn.net/qq_29467891/article/details/106758727

前言：由于之前用的是开源的bootdo项目作基础框架，在此基础上进行业务流程的开发。最近要进行国产化，数据库需要切换到达梦数据库。因此修改该框架配置，使其支持多数据源，且其中一个数据源是达梦数据库。下面是各个步骤的说明：

1.pom.xml文件配置

		<dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.4</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <!--druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.29</version>
        </dependency>
        
		<!--mysql驱动包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.25</version>
        </dependency>

        <!--达梦驱动包-->
        <dependency>
            <groupId>com.dameng</groupId>
            <artifactId>Dm8JdbcDriver18</artifactId>
            <version>8.1.1.49</version>
        </dependency>

2.yml文件配置：

spring:
  datasource:
    primary:
      driverClassName: com.mysql.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://192.168.4.163:3306/test?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
      username: root
      password: 
      initialSize: 1
      minIdle: 3
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 30000
      validationQuery: select 'x' from dual
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,slf4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 合并多个DruidDataSource的监控数据
      #useGlobalDataSourceStat: true
    dm:
      driverClassName:  dm.jdbc.driver.DmDriver
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:dm://localhost:5236/?STU&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
      username: SYSDBA
      password:

3.mybatis配置：

mybatis:
  configuration:
    map-underscore-to-camel-case: true
    jdbc-type-for-null: 'null'
  mapper-locations: mybatis/**/*Mapper.xml
  typeAliasesPackage: com.test.**.domain

4.数据源配置

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;


@Configuration
@EnableTransactionManagement
public class DatasourceConfiguration{
    private static final Logger log = LoggerFactory.getLogger(DatasourceConfiguration.class);

    @Value("${mybatis.typeAliasesPackage}")
    private String typeAliasesPackage;

    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        log.info("-------------------- primaryDataSource init ---------------------");
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Bean(name = "dmDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.dm")
    public DataSource dmDataSource() {
        log.info("-------------------- dmDataSource init ---------------------");
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    /**
     * 多数据源配置
     * 特别说明：@Qualifier这个注解是用来指定注入类的，当通过@bean创建实体时，实体的name会默认使用方法名
     * 所以@Qualifier的参数就是上面两个创建数据源的方法的方法名
     * @param dm 达梦数据库数据源
     * @param mysql mysql数据库数据源
     * @return 动态数据源
     */
    @Bean
    @Primary
    public DynamicDataSource dataSource(@Qualifier("primaryDataSource") DataSource mysql,
                                        @Qualifier("dmDataSource") DataSource dm) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DatabaseType.dm, dm);
        targetDataSources.put(DatabaseType.mysql, mysql);

        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
        dataSource.setDefaultTargetDataSource(mysql);// 默认的datasource设置为myTestDbDataSource

        return dataSource;
    }

    /**
     * 根据数据源创建SqlSessionFactory
     * @param dm 达梦数据库数据源
     * @param mysql mysql数据库数据源
     * @return SqlSessionFactory
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("primaryDataSource") DataSource mysql,
                                               @Qualifier("dmDataSource") DataSource dm,
            org.apache.ibatis.session.Configuration config) throws Exception{
        SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
        fb.setDataSource(this.dataSource(dm, mysql));
        fb.setTypeAliasesPackage(typeAliasesPackage);
        fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/**/*Mapper.xml"));
        fb.setConfiguration(config);
        return fb.getObject();
    }

    /**
     * 配置事务管理器
     * @param dataSource 动态数据源
     * @return DataSourceTransactionManager
     * @throws Exception
     */
    @Bean
    public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
        return new DataSourceTransactionManager(dataSource);
    }


    @Bean
    @ConfigurationProperties(prefix = "mybatis.configuration")
    public org.apache.ibatis.session.Configuration globalConfiguration() {
        return new org.apache.ibatis.session.Configuration();
    }

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("allow", ""); //白名单
        return reg;
    }

    @Bean public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
        filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");
        filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");
        return filterRegistrationBean;
    }
}

DynamicDataSource：

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 动态数据源（需要继承AbstractRoutingDataSource）
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    protected Object determineCurrentLookupKey() {
        return DatabaseContextHolder.getDatabaseType();
    }
}

DatabaseContextHolder：

/**
 * 作用：
 * 1、保存一个线程安全的DatabaseType容器
 */
public class DatabaseContextHolder {
    private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>();
    
    public static void setDatabaseType(DatabaseType type){
        contextHolder.set(type);
    }
    
    public static DatabaseType getDatabaseType(){
        return contextHolder.get();
    }
}

DatabaseType：

/**
 * 列出所有的数据源key（常用数据库名称来命名）
 * 注意：
 * 1）这里数据源与数据库是一对一的
 * 2）DatabaseType中的变量名称就是数据库的名称
 */
public enum DatabaseType {
    dm,mysql
}

PageQueryUtils：

import java.util.Map;

public class PageQueryUtils {
    
    public static void parsePageParams(Map<String,Object> params) throws Exception {
        Object pageObj = params.get("page");
        
        if(pageObj ==null){
            throw new Exception("parameter page is null ");
        }

        Object limitObj  =  params.get("limit");
        if(limitObj == null){
            throw new Exception("parameter limit is null ");
        }
        
        Integer page = Integer.parseInt(pageObj.toString());
        Integer limit = Integer.parseInt(limitObj.toString());

        Integer offset = (page-1) * limit;
        if(offset < 0){
            throw new Exception("offset must grantter than 0 , input page is :" 
            + page + " ,limit is : " + limit);
        }
        params.put("offset", offset);
    }
}

5.测试代码：

	@RequestMapping("queryDM")
    @ResponseBody
    public String queryDM(@RequestParam Map<String, Object> params) throws Exception {
		/* 特别注意，在调用mapper之前，必须调用该方法来切换数据源 */
        DatabaseContextHolder.setDatabaseType(DatabaseType.dm);
        return userService.listDm(params).toString();
    }

	@Override
    public List<Event> listDm(Map<String, Object> params) {
        params.put("page", 1);
        params.put("limit", 10);
        try {
            PageQueryUtils.parsePageParams(params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Event> events = userMapper.listDm(params);
        return events;
    }

	<!-- 列表查询 -->
    <select id="listDm" parameterType="map" resultMap="BaseResultMap">

        select "id","rule_id","rule_name","create_time"
        from "DM_TEST"."event_demo"
        <if test="offset > -1">
            limit #{offset},#{limit}
        </if>
    </select>