druid+mybatisplus多数据源集成postgresql和clickhouse简单实践

注：本来想写一个starter，但是比较难抽象

一、配置文件

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: $click-urlxxxxx
      username: xx
      password: xx
      initialSize: ${POSTGRESQL_POOL_INITIAL_SIZE:10}
      maxActive: ${POSTGRESQL_POOL_MAX_ACTIVE:100}
      minIdle: ${POSTGRESQL_POOL_MIN_IDLE:10}
      maxWait: ${POSTGRESQL_POOL_MAX_WAIT:6000}
      clusterName: replcluster
      dbName: ${CLICKHOUSE_DBNAME:original_data}
      #连接clickhouse失败时，是否一直重试.默认一直重试，设置为false时，只重试3次.
      alwaysRetryConnect: ${ALWAYS_RETRY_CONNECT_CLICKHOUSE:true}
    pg: #多数据源，此处配置的是postgre数据库
      driverClassName: org.postgresql.Driver
      url: pg-urlxxx
      username: xxx
      password: xxx
      initialSize: ${POSTGRESQL_POOL_INITIAL_SIZE:10}
      maxActive: ${POSTGRESQL_POOL_MAX_ACTIVE:100}
      minIdle: ${POSTGRESQL_POOL_MIN_IDLE:10}
      maxWait: ${POSTGRESQL_POOL_MAX_WAIT:6000}
    druid:
      #初始化数量
      initial-size: 10
      #最大活跃数
      max-active: 100
      min-idle: 10
      #最大连接等待超时时间
      max-wait: 6000
      testWhileIdle: true
      validationQuery: select 1

mybatis-plus:
  mapper-locations: classpath:/mapper/*Mapper.xml
  type-aliases-package: xxx.entity.pg
  global-config:
    id-type: 3  #0:数据库ID自增   1:用户输入id  2:全局唯一id(IdWorker)  3:全局唯一ID(uuid)
    #驼峰下划线转换
    db-column-underline: true
    refresh-mapper: true

二、config配置

@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = "com.rootcloud.emqxkafkabridge.mapper")
public class MybatisPlusConfig {

  @Value("${spring.datasource.click.alwaysRetryConnect}")
  private Boolean alwaysRetryConnect;

  /** clickhouse数据源.*/
  @Bean(name = "clickDataSource")
  @ConfigurationProperties(prefix = "spring.datasource.click")
  public DataSource clickDataSource() {
    DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();

    //https://blog.csdn.net/Eazon_chan/article/details/115395018
    if (!BooleanUtils.toBooleanDefaultIfNull(alwaysRetryConnect, true)) {
      //失败后重连的次数，避免一直重试。专属云不需要ck，可以指定错误的clickhouse地址，但是一定要指定参数
      druidDataSource.setConnectionErrorRetryAttempts(3);
      //请求失败之后中断
      druidDataSource.setBreakAfterAcquireFailure(true);
    }
    return druidDataSource;
  }

  /** postgresql数据源.*/
  @Bean(name = "pgDataSource")
  @ConfigurationProperties(prefix = "spring.datasource.pg")
  public DataSource pgDataSource() {
    return DruidDataSourceBuilder.create().build();
  }

  /**
   * 动态数据源配置.
   */
  @Bean
  @Primary
  public DataSource multipleDataSource(@Qualifier("clickDataSource") DataSource clickDataSource,
                                       @Qualifier("pgDataSource") DataSource pgDataSource) {
    MultipleDataSource multipleDataSource = new MultipleDataSource();
    Map<Object, Object> targetDataSources = Maps.newHashMapWithExpectedSize(2);
    targetDataSources.put(DbTypeEnum.CLICK_DB.getValue(), clickDataSource);
    targetDataSources.put(DbTypeEnum.PG_DB.getValue(), pgDataSource);
    //添加数据源
    multipleDataSource.setTargetDataSources(targetDataSources);
    //设置默认数据源
    multipleDataSource.setDefaultTargetDataSource(pgDataSource);
    return multipleDataSource;
  }

  /** sqlSessionFactory配置.*/
  @Bean("sqlSessionFactory")
  public SqlSessionFactory sqlSessionFactory() throws Exception {
    MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
    sqlSessionFactory.setDataSource(multipleDataSource(clickDataSource(), pgDataSource()));
    //添加分页插件
    sqlSessionFactory.setPlugins(paginationInterceptor());
    return sqlSessionFactory.getObject();
  }

  /**
   * 分页插件，自动识别数据库类型.
   */
  @Bean
  public PaginationInterceptor paginationInterceptor() {
    return new PaginationInterceptor();
  }

}

三、注解+AOP实现多数据源选择

注意切面扫的包就是对应数据源的应用。

（注：多数据源也可以使用特定方法名的方式截取，比如规定query开头走A数据源，select开头走B数据源，方式很多看自己场景）

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSourceSwitch {
  /**
   * 设置默认数据源.
   */
  DbTypeEnum value() default DbTypeEnum.PG_DB;
}

@Getter
@AllArgsConstructor(access = AccessLevel.PRIVATE)
public enum DbTypeEnum {
  CLICK_DB("click"), PG_DB("pg");
  private String value;
}

/**
 * AOP切面，注意多数据源分包处理.
 */
@Component
@Slf4j
@Aspect
@Order(-1)
public class DataSourceAspect {

  @Pointcut("@within(xxx.DataSourceSwitch) "
      + "|| @annotation(xxx.DataSourceSwitch)")
  public void pointCut() {

  }

  @Before("pointCut() && @annotation(dataSourceSwitch)")
  public void doBefore(DataSourceSwitch dataSourceSwitch) {
    String datasource = dataSourceSwitch.value().getValue();
    MultipleDataSource.setDataSource(datasource);
  }

  @After("pointCut()")
  public void doAfter() {
    MultipleDataSource.clear();
  }
}

public class MultipleDataSource extends AbstractRoutingDataSource {

  private static final ThreadLocal<String> contextHolder = new InheritableThreadLocal<>();

  @Override
  protected Object determineCurrentLookupKey() {
    return this.getDataSource();
  }

  /**
   * 设置数据源.
   */
  public static void setDataSource(String db) {
    contextHolder.set(db);
  }

  /**
   * 取得当前数据源.
   */
  public static String getDataSource() {
    return contextHolder.get();
  }

  /**
   * 清除上下文数据.
   */
  public static void clear() {
    contextHolder.remove();
  }
}

如果不想设置多数据源，只想给定某个数据源嗯？可以这样重置（Mybatis举例）：

  /** 给MybatisPlus设置默认数据源.*/
  @Bean
  public SqlSessionFactory sqlSessionFactory() throws Exception {
    MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
    sqlSessionFactoryBean.setDataSource(pgDataSource());
    return sqlSessionFactoryBean.getObject();
  }

四、MybatisPlus直接简单使用

gradle引入依赖：

    compile "com.baomidou:mybatis-plus-boot-starter:${mybatisplusVersion}"
    compile "com.baomidou:mybatis-plus-generator:${mybatisplusVersion}"
    compile "org.freemarker:freemarker:${freemarkerVersion}"
    compile "org.postgresql:postgresql:${pgDriveVersion}"
    compile "org.springframework.boot:spring-boot-starter-aop:${springBootVersion}"

一个demo举例:

entity:

/**
 * 设备影子日志实体.
 */
@TableName("xxx")
public class DeviceShadowLog extends Model<DeviceShadowLog> {
}

mapper:

public interface DeviceShadowLogMapper extends BaseMapper<DeviceShadowLog> {

}

service:

public interface DeviceShadowLogService extends IService<DeviceShadowLog> {
}

serviceImpl:

@Service
public class DeviceShadowLogServiceImpl extends ServiceImpl<DeviceShadowLogMapper,
    DeviceShadowLog> implements
    DeviceShadowLogService {
}

controller:

直接使用mybatisplus的starter里边集成的方法，还挺好用（就是不知道国产的稳不稳定）

/**
 * 查询PG日志.
 */
@RestController
@Api(value = "log", tags = "Log")
@RequestMapping("/log")
public class LogController {

  @Autowired
  private DeviceShadowLogService deviceShadowLogService;

  /**分页查询.*/
  @ApiOperation(value = "条件查询影子更新日志信息列表")
  @GetMapping("/shadows/list")
  public IPage deviceShadowLogPage(@RequestParam long current,
                    @RequestParam long size) {
    QueryWrapper<DeviceShadowLog> wrapper = new QueryWrapper<>();
    wrapper.orderByDesc("created");
    Page page = new Page(current, size);
    return deviceShadowLogService.page(page, wrapper);
  }
}

mapper.xml:

需要扩展写sql的可以写

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xxx.mapper.DeviceShadowLogMapper">

</mapper>