allbs工具类说明 - 动态数据源

依赖jar包

引入包	版本
jdk	1.8
spring boot	2.6.2
dynamic-datasource-spring-boot-starter	3.2.0
druid-spring-boot-starter	1.2.3

使用

添加依赖

mavenGradleKotlin

<dependency>
  <groupId>cn.allbs</groupId>
  <artifactId>allbs-dynamic-db</artifactId>
  <version>1.1.7</version>
</dependency>
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid</artifactId>
   <version>1.2.3</version>
</dependency>
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>1.2.3</version>
</dependency>
<dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
   <version>3.2.0</version>
</dependency>   

implementation 'cn.allbs:allbs-dynamic-db:1.1.7'

implementation("cn.allbs:allbs-dynamic-db:1.1.7")

启用

启动类添加@EnableDynamicDataSource。注意该注解必须优先于@SpringBootApplication或者@SpringCloudApplication

添加数据源配置表

# 数据库
create table ds_datasource
(
    id          int auto_increment
        primary key,
    name        tinytext                            null,
    url         tinytext                            null,
    username    tinytext                            null,
    password    tinytext                            null,
    create_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
    update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
    del_flg    tinyint      default '0'               null
)
    comment '数据源表';

添加数据库连接配置，用于读取数据库中的多个库的配置，多个库的默认库为数据源配置所在的库

spring: 
    datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: ${MYSQL_USER:root}
      password: ${MYSQL_PWD:123456}
      url: jdbc:mysql://${MYSQL_HOST:cq-server}:${MYSQL_PORT:3306}/${MYSQL_DB:allbs}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&allowPublicKeyRetrieval=true
      # sql执行日志监控可视化，访问url为http://{ip}:{port}/{url-pattern}/index.html，生产环境记得去掉！！
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*

启动效果

动态切换

在serverimpl、dao类上加上DS(库名)或者在方法上加上DS(库名),不加注解将使用主库。注意！是service实现类上加！类中和方法同时加上注解时将遵循最近原则，优先使用方法注解。

controller

@RestController
@RequestMapping("/ds")
@AllArgsConstructor
public class TestDsController {
 
    private final MeterAccountService meterAccountService;
 
    private final AirMainPollutantService airMainPollutantService;
 
    @GetMapping("/all")
    public R testDs() {
        MeterAccountEntity accountEntity = meterAccountService.getOne(Wrappers.<MeterAccountEntity>query().last("limit 1"));
        AirMainPollutantEntity airMainPollutantEntity = meterAccountService.queryOnd();
        Map<String, Object> rs = new HashMap<>(2);
        rs.put("acc", accountEntity);
        rs.put("air", airMainPollutantEntity);
        return R.ok(rs);
    }
}

service interface

public interface MeterAccountService extends IService<MeterAccountEntity> {
 
    IPage<MeterAccountEntity> page(Page page, MeterAccountEntity meterAccountEntity);
 
    AirMainPollutantEntity queryOnd();
 
}

service implements

@Service("meterAccountService")
@AllArgsConstructor
// 切换至主库中数据库名称配置为test的库
@DS("test")
public class MeterAccountServiceImpl extends ServiceImpl<MeterAccountDao, MeterAccountEntity> implements MeterAccountService {
 
    private final MeterAccountDao meterAccountDao;
 
    private final AirMainPollutantDao airMainPollutantDao;
 
    @Override
    public IPage<MeterAccountEntity> page(Page page, MeterAccountEntity meterAccountEntity) {
        return meterAccountDao.queryPage(page, meterAccountEntity);
    }
 
    @Override
    public AirMainPollutantEntity queryOnd() {
        return airMainPollutantDao.queryOne();
    }
 
}

dao

@Mapper
// 切换至主库中数据库名称配置为postSql的库
@DS("postSql")
public interface AirMainPollutantDao extends BaseMapper<AirMainPollutantEntity> {
 
    IPage<AirMainPollutantEntity> queryPage(Page page, AirMainPollutantEntity airMainPollutantEntity);
 
    void insetALl(@Param("airMainPollutantEntity") AirMainPollutantEntity airMainPollutantEntity);
 
    AirMainPollutantEntity queryOne();
 
}

使用效果

监控查看sql记录

http://{url}:{port}/{url-pattern}/index.html