mybatis-plus多数据源,原因@DS注解实现写配置文件不够灵活,所以动态从数据库读取数据源

/**
 * @DS("#header.tenantId")
 * 重写处理器实现自定义参数，比如从请求header里面获取参数切换数据源
 */
public class DsHeaderProcessor extends DsProcessor {

    private static final String HEADER_PREFIX = "#header";

    @Override
    public boolean matches(String key) {
        return key.startsWith(HEADER_PREFIX);
    }

    @Override
    public String doDetermineDatasource(MethodInvocation invocation, String key) {
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        return request.getHeader(key.substring(8));
    }
}

拦截器拦截所有请求

@Aspect
@Component
@RequiredArgsConstructor(onConstructor_ = @Autowired)
public class DsInterceptor implements HandlerInterceptor {

    @Pointcut("execution(public * com.store.controller.*.*(..))")
    public void datasourcePointcut() {
    }

    /**
     * 前置操作，拦截具体请求，获取header里的数据源id，设置线程变量里，用于后续切换数据源
     */
    @Before("datasourcePointcut()")
    public void doBefore(JoinPoint joinPoint) throws Exception {
        Signature signature = joinPoint.getSignature();
        MethodSignature methodSignature = (MethodSignature) signature;
        Method method = methodSignature.getMethod();

        // 排除不可切换数据源的方法
        DefaultDs annotation = method.getAnnotation(DefaultDs.class);
        if (null != annotation) {
            DynamicDataSourceContextHolder.push("master");
        } else {
            RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();
            ServletRequestAttributes attributes = (ServletRequestAttributes) requestAttributes;
            assert attributes != null;
            HttpServletRequest request = attributes.getRequest();
            //String header = request.getHeader("dbName");

            String token =  request.getHeader("token");
            Map jwt = JwtUtil.parseJWT(token);
            //数据库库名
            String header = (String) jwt.get("aud");
            //切换数据源
            if (StrUtil.isNotBlank(header)) {
                DynamicDataSourceContextHolder.push(header);
            } else {
                throw new RuntimeException("dbName不能为空");
            }
        }
    }

    /**
     * 后置操作，设置回默认的数据源id
     */
    @AfterReturning("datasourcePointcut()")
    public void doAfter() {
        DynamicDataSourceContextHolder.push("master");
    }

}

/**
 * 本地数据源提供者->从数据库查出已存在的数据源给框架管理
 *
 * @author starsray
 * @date 2021/11/16
 */
@Primary
@Configuration
public class LocalDatasourceProvider {

    /**
     * 数据源配置
     */
    @Resource
    private PrimaryDatasourceConfig primaryDatasourceConfig;

    /**
     * 动态数据源jdbc提供者
     *
     * @return {@link DynamicDataSourceProvider}
     */
    @Bean
    public DynamicDataSourceProvider jdbcDynamicDataSourceProvider() {
        return new AbstractJdbcDataSourceProvider(primaryDatasourceConfig.getDriverClassName(), primaryDatasourceConfig.getUrl(), primaryDatasourceConfig.getUsername(), primaryDatasourceConfig.getPassword()) {
            /**
             * 执行支撑
             *
             * @param statement 声明
             * @return {@link Map}<{@link String}, {@link DataSourceProperty}>
             */
            @Override
            protected Map<String, DataSourceProperty> executeStmt(Statement statement) {
                Map<String, DataSourceProperty> dataSourcePropertiesMap = null;
                ResultSet rs = null;
                try {
                    dataSourcePropertiesMap = new HashMap<>();
                    String createSql = SQLUtils.createTable();
                    statement.executeUpdate(createSql);
                    rs = statement.executeQuery(SQLUtils.selectDs());
                    while (rs.next()) {
                        String name = rs.getString("datasource_id");
                        DataSourceProperty property = new DataSourceProperty();
                        property.setDriverClassName(rs.getString("drive_class"));
                        property.setUrl(rs.getString("url"));
                        property.setUsername(rs.getString("user_name"));
                        property.setPassword(rs.getString("pass_word"));
                        dataSourcePropertiesMap.put(name, property);
                    }
                    if (dataSourcePropertiesMap.size() == 0) {
                        statement.execute(SQLUtils.primaryDs(primaryDatasourceConfig));
                        DataSourceProperty dataSourceProperty = new DataSourceProperty();
                        dataSourceProperty.setDriverClassName(primaryDatasourceConfig.getDriverClassName());
                        dataSourceProperty.setUrl(primaryDatasourceConfig.getUrl());
                        dataSourceProperty.setUsername(primaryDatasourceConfig.getUsername());
                        dataSourceProperty.setPassword(primaryDatasourceConfig.getPassword());
                        dataSourcePropertiesMap.put(primaryDatasourceConfig.getPrimary(), dataSourceProperty);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    try {
                        if (rs != null) {
                            rs.close();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                return dataSourcePropertiesMap;
            }
        };
    }
}

数据库字段

public class SQLUtils {


    public static String selectDs() {
        return "select * from databasesource where databasetype='mysql'";
    }

    public static String createTable() {
        return "create table if not exists databasesource (datasource_id varchar(50) null, user_name varchar(50) null, pass_word varchar(50) null, url varchar(200) null, driver_class varchar(50) null, create_time datetime default current_timestamp)";
    }

    public static String primaryDs(PrimaryDatasourceConfig primaryDatasourceConfig) {
        return "insert into databasesource (datasource_id, user_name, pass_word, url, drive_class,databasetype,type) VALUES " +
                "(" + "'" + primaryDatasourceConfig.getPrimary() + "'," + "'" + primaryDatasourceConfig.getUsername() +
                "'," + "'" + primaryDatasourceConfig.getPassword() + "'," + "'" +
                primaryDatasourceConfig.getUrl() + "'," + "'" + primaryDatasourceConfig.getDriverClassName() + "'" +
                ",'mysql','1'"+")";
    }

    public static String slaveDs(DatasourceProperties properties) {
        return "insert into databasesource (datasource_id, user_name, pass_word, url, drive_class, type, status) " +
                "VALUES (" + "'" + properties.getPoolName() + "'," + "'" + properties.getUsername() + "'," +
                "'" + properties.getPassword() + "'," + "'" + properties.getUrl() + "'," +
                "'" + properties.getDriverClassName() + "'" + ",2," + properties.getStatus()
                + ")";
    }

    public static String removeDs(String poolName) {
        return "delete from databasesource where datasource_id ='" + poolName + "' limit 1";
    }
}

自定义注解:标记这个注解则进入主数据源(yml里面配置的数据源)

/**
 * <p>
 * 用户标识仅可以使用默认数据源
 * </p>
 */

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DefaultDs {
}

/**
 * 注册自定义处理器
 */
@Configuration
public class MyDynamicDataSourceConfig {

    /**
     * 分页
     * @return
     */
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        //这是分页拦截器
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();

        paginationInnerInterceptor.setOverflow(false);
        paginationInnerInterceptor.setMaxLimit(500L);
        mybatisPlusInterceptor.addInnerInterceptor(paginationInnerInterceptor);
        //设置请求的页面大于最大页后操作，true调回到首页，false继续请求默认false
        // paginationInterceptor.setOverflow(false);//设置最大单页限制数量，默认500条，-1不受限制
        //paginationInterceptor.setLimit(500);
        //开启 count 的 join 优化,只针对部分 left join
        return mybatisPlusInterceptor;
    }

    @Bean
   public DsProcessor dsProcessor() {
        DsHeaderProcessor headerProcessor = new DsHeaderProcessor();
        DsSessionProcessor sessionProcessor = new DsSessionProcessor();
        DsSpelExpressionProcessor spelExpressionProcessor = new DsSpelExpressionProcessor();
        headerProcessor.setNextProcessor(sessionProcessor);
        sessionProcessor.setNextProcessor(spelExpressionProcessor);
        return headerProcessor;
   }


    /**
     * 事务管理器
     *
     * @param dataSource 数据源
     */
    @Bean("transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

@ConfigurationProperties(prefix = "spring.datasource.dynamic.datasource.master")
@Configuration
@Data
public class PrimaryDatasourceConfig {

    /**
     * mybatis plus默认数据源 primary
     */
    private String primary = "master";
    private String driverClassName;
    private String url;
    private String username;
    private String password;
}

 /**
     * 添加数据源
     *
     * @param properties 属性
     * @return {@link Set}<{@link String}>
     */
    @DefaultDs
    @PostMapping("addDatasource")
    public Set<?> addDatasource(@RequestBody @Valid DatasourceProperties properties) throws Exception {
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        Map<String, DataSource> dataSources = ds.getDataSources();
        if (dataSources.containsKey(properties.getPoolName())) {
            return dataSources.keySet();
        }

        DataSourceProperty dataSourceProperty = new DataSourceProperty();
        BeanUtils.copyProperties(properties, dataSourceProperty);

        // 加密密码
//        try {
//            properties.setPassword("ENC(" + CryptoUtils.encrypt(properties.getPassword()) + ")");
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
        properties.setPassword(properties.getPassword());

        try {
            Connection connection = dataSource.getConnection();
            PreparedStatement statement = connection.prepareStatement(SQLUtils.slaveDs(properties));
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty);
        ds.addDataSource(properties.getPoolName(), dataSource);
        return dataSources.keySet();

    }

    /**
     * 查询数据源
     *
     * @return {@link Set<String> }
     */
    @GetMapping("list")
    @DefaultDs
    public Set<String> list() {
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        return ds.getDataSources().keySet();
    }

    /**
     * 删除数据源
     *
     * @param poolName 数据库库名
     * @return boolean
     */
    @DefaultDs
    @PostMapping ("remove")
    public boolean remove(@RequestParam("poolName") String poolName) {

        if ("master".equals(poolName)) {
            throw new RuntimeException("主数据源不能移除!");
        }
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        if (!ds.getDataSources().containsKey(poolName)) {
            throw new RuntimeException("数据源不存在!");
        }

        DataSource master = ds.getDataSource("master");
        try {
            Connection connection = master.getConnection();
            PreparedStatement statement = connection.prepareStatement(SQLUtils.removeDs(poolName));
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        ds.removeDataSource(poolName);
        return true;

    }

server:
  port: 9012
spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
  application:
    name: store
  redis:
    host: 127.0.0.1
    port: 6379
    password:
    timeout: 3000
    database: 0
  datasource:
    dynamic:
      strict: false
      primary: master
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          #url: jdbc:mysql://11.102.1.118:23306/wbrj_accset?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
          url: jdbc:mysql://127.0.0.1:23306/wbrj_accset?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
          username: root
          password: root
      druid:
        wall:
          comment-allow: true
          none-base-statement-allow: true
          variant-check: false
          multi-statement-allow: true
    ###############以上为配置druid添加的配置########################################

   <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.5.0</version>
        </dependency>

注:以上实现为精简版

完整代码示例:星光/dynamic-datasource