快捷導(dǎo)航

Springboot實(shí)現(xiàn)根據(jù)用戶ID切換動(dòng)態(tài)數(shù)據(jù)源

更新時(shí)間：2021年12月15日 10:13:55 作者：洛陽(yáng)泰山

在很多具體應(yīng)用場(chǎng)景中，我們需要用到動(dòng)態(tài)數(shù)據(jù)源的情況，比如多租戶的場(chǎng)景，系統(tǒng)登錄時(shí)需要根據(jù)用戶信息切換到用戶對(duì)應(yīng)的數(shù)據(jù)庫(kù)。這篇文章主要介紹了SpringBoot根據(jù)用戶ID實(shí)現(xiàn)切換動(dòng)態(tài)數(shù)據(jù)源的示例代碼，感興趣的可以了解一下

首先在application.yml 文件添加一下配置?

#每個(gè)庫(kù)可連接最大用戶數(shù)
dynamic-server:
  #每個(gè)服務(wù)最大建庫(kù)數(shù)
  database-max-number: 30
  #每個(gè)庫(kù)最大用戶連接數(shù)
  user-max-number: 200
  template: gis_template

然后項(xiàng)目中添加 dynamicds 模塊的代碼，僅展示模塊文件目錄，代碼太多不展示了

然后添加攔截器

 
    @Override
    public void addInterceptors(InterceptorRegistry registry){
        //排除登錄注冊(cè)攔截
        List<String> patterns = new ArrayList<>();
 
        patterns.add("/doc.html");
        patterns.add("/js/**");
        patterns.add("/webjars/**");
        patterns.add("/swagger-resources/**");
        patterns.add("/unify-resource/**");
        patterns.add("/unify-auth/oauth/token");
        patterns.add("/unify-auth/register");
        patterns.add("/unify-resource/**");
        patterns.add("/rabbit/**");
        //默認(rèn)數(shù)據(jù)源 不需要切換的 排除
        registry.addInterceptor(dynamicDataSourceInterceptor()).addPathPatterns("/**")
                .excludePathPatterns(patterns).order(1);
      //  registry.addInterceptor(dynamicDefaultDataSourceInterceptor()).addPathPatterns("/**").order(-1);
    }
 
    @Bean
    public DynamicDataSourceInterceptor dynamicDataSourceInterceptor(){
        return new DynamicDataSourceInterceptor();
    }

動(dòng)態(tài)數(shù)據(jù)源攔截器

跟據(jù)token 獲取用戶id 再根據(jù)用戶id切換對(duì)應(yīng)數(shù)據(jù)源?

 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springblade.core.secure.BladeUser;
import org.springblade.core.secure.utils.AuthUtil;
import org.springblade.gis.dynamicds.cache.DynamicDataSourceCache;
import org.springblade.gis.dynamicds.datasource.MyDynamicDataSource;
import org.springblade.gis.dynamicds.service.DynamicDataSourceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.servlet.HandlerInterceptor;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
/**
 * file:DynamicDataSourceInterceptor
 * <p>
 * 文件簡(jiǎn)要說(shuō)明
 *
 * @author 2021-10-28 tarzan 創(chuàng)建初始版本
 * @version V1.0  簡(jiǎn)要版本說(shuō)明
 */
public class DynamicDataSourceInterceptor implements HandlerInterceptor {
    private static final Logger log = LoggerFactory.getLogger(DynamicDataSourceInterceptor.class);
    @Autowired
    private MyDynamicDataSource dynamicDataSource;
    @Autowired
    private DynamicDataSourceCache dynamicDataSourceCache;
    @Autowired
    private DynamicDataSourceService dynamicDataSourceService;
 
 
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)  {
        //獲取當(dāng)前登錄用戶信息
        BladeUser user = AuthUtil.getUser();
     /*   if(user == null || user.getUserId() == null){
            throw new UnauthorizedException(ErrorEnum.E_401);
        }*/
        log.info("據(jù)源切換--------------用戶名-----"+user.getUserName()+"------------>【{}】", user.getUserId());
        //如果未獲取到 dsName 重新加載數(shù)據(jù)庫(kù)
        if(!dynamicDataSourceCache.hasDataSourceName(user.getUserId())){
            dynamicDataSourceService.addUserDataSource(user.getUserId());
        }
        String dsName = dynamicDataSourceCache.getUserIdDataSourceName(user.getUserId());
        if(!dynamicDataSource.switchDataSource(dsName)){
            //如果切換數(shù)據(jù)源失敗 返回錯(cuò)誤
            throw new RuntimeException("未找到用戶數(shù)據(jù)庫(kù)");
        }
        return true;
    }
 
}

數(shù)據(jù)庫(kù)設(shè)計(jì)

dynamicDataSource:
  default:
    url: jdbc:postgresql://${POSTGRES_HOST:172.16.10.201}:${POSTGRES_PORT:5432}/${POSTGRES_DATABASE:gis_db}
    username: ${POSTGRES_USERNAME:postgres}
    password: ${POSTGRES_PASSWORD:postgres}
    driverClassName: org.postgresql.Driver
    pool:
      #最小空閑連接
      minimum-idle: 2
      #最大連接
      maximum-pool-size: 3
      # 空閑連接存活最大時(shí)間，默認(rèn)600000（10分鐘）
      idle-timeout: 1200000
      # 據(jù)庫(kù)連接超時(shí)時(shí)間,默認(rèn)30秒
      connection-timeout: 300000

初始鏈接一個(gè)基礎(chǔ)數(shù)據(jù)庫(kù)，放置用戶表，數(shù)據(jù)源表，數(shù)據(jù)庫(kù)表

數(shù)據(jù)庫(kù)表建表語(yǔ)句

CREATE TABLE "public"."data_server_database" (
  "id" int8 NOT NULL,
  "data_source_key" varchar(128) COLLATE "pg_catalog"."default" NOT NULL,
  "database_name" varchar(128) COLLATE "pg_catalog"."default" NOT NULL,
  "source_id" int8 NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "priority" int4 NOT NULL,
  "amount" int4 NOT NULL DEFAULT 0,
  "status" int2 NOT NULL DEFAULT 2
)
;
COMMENT ON COLUMN "public"."data_server_database"."id" IS '主鍵';
COMMENT ON COLUMN "public"."data_server_database"."data_source_key" IS '數(shù)據(jù)源連接唯一key';
COMMENT ON COLUMN "public"."data_server_database"."database_name" IS '數(shù)據(jù)庫(kù)名';
COMMENT ON COLUMN "public"."data_server_database"."source_id" IS '數(shù)據(jù)源id(data_server_source表主鍵id)';
COMMENT ON COLUMN "public"."data_server_database"."create_time" IS '創(chuàng)建時(shí)間';
COMMENT ON COLUMN "public"."data_server_database"."update_time" IS '更新時(shí)間';
COMMENT ON COLUMN "public"."data_server_database"."priority" IS '數(shù)據(jù)庫(kù)使用順序(升序)';
COMMENT ON COLUMN "public"."data_server_database"."amount" IS '數(shù)據(jù)使用用戶數(shù)量';
COMMENT ON COLUMN "public"."data_server_database"."status" IS '使用狀態(tài)(1:正在使用；2：本庫(kù)使用用戶數(shù)已滿)';
COMMENT ON TABLE "public"."data_server_database" IS '用戶連接的數(shù)據(jù)庫(kù)配置';
 
-- ----------------------------
-- Uniques structure for table data_server_database
-- ----------------------------
ALTER TABLE "public"."data_server_database" ADD CONSTRAINT "source_key_unique" UNIQUE ("data_source_key");
COMMENT ON CONSTRAINT "source_key_unique" ON "public"."data_server_database" IS '數(shù)據(jù)源名 唯一';
 
-- ----------------------------
-- Primary Key structure for table data_server_database
-- ----------------------------
ALTER TABLE "public"."data_server_database" ADD CONSTRAINT "data_server_source_pkey" PRIMARY KEY ("id");

數(shù)據(jù)源表建表語(yǔ)句?

CREATE TABLE "public"."data_server_source" (
  "id" int8 NOT NULL,
  "driver_class_name" varchar(128) COLLATE "pg_catalog"."default" NOT NULL,
  "url" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "user_name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "password" varchar(128) COLLATE "pg_catalog"."default" NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "priority" int4,
  "amount" int4 DEFAULT 0,
  "status" int2 DEFAULT 2
)
;
COMMENT ON COLUMN "public"."data_server_source"."id" IS '主鍵';
COMMENT ON COLUMN "public"."data_server_source"."driver_class_name" IS '數(shù)據(jù)庫(kù)驅(qū)動(dòng)';
COMMENT ON COLUMN "public"."data_server_source"."url" IS '數(shù)據(jù)庫(kù)連接url';
COMMENT ON COLUMN "public"."data_server_source"."user_name" IS '數(shù)據(jù)庫(kù)用戶名';
COMMENT ON COLUMN "public"."data_server_source"."password" IS '數(shù)據(jù)庫(kù)用戶密碼';
COMMENT ON COLUMN "public"."data_server_source"."create_time" IS '創(chuàng)建時(shí)間';
COMMENT ON COLUMN "public"."data_server_source"."update_time" IS '更新時(shí)間';
COMMENT ON COLUMN "public"."data_server_source"."priority" IS '數(shù)據(jù)庫(kù)服務(wù)使用順序(升序)';
COMMENT ON COLUMN "public"."data_server_source"."amount" IS '數(shù)據(jù)服務(wù)建庫(kù)數(shù)量';
COMMENT ON COLUMN "public"."data_server_source"."status" IS '使用狀態(tài)(1:正在使用；2：本服務(wù)建庫(kù)數(shù)已滿)';
COMMENT ON TABLE "public"."data_server_source" IS '數(shù)據(jù)庫(kù)服務(wù)的數(shù)據(jù)源連接表';
 
-- ----------------------------
-- Records of data_server_source
-- ----------------------------
INSERT INTO "public"."data_server_source" VALUES (2, 'org.postgresql.Driver', 'jdbc:postgresql://localhost:5432/', 'hgl', 'hgl', '2021-11-01 14:53:45', '2021-11-01 14:53:47', 2, 0, 2);
INSERT INTO "public"."data_server_source" VALUES (1, 'org.postgresql.Driver', 'jdbc:postgresql://172.16.10.201:5432/', 'postgres', 'postgres', '2021-11-01 14:53:45', '2021-11-01 14:53:47', 1, 3, 1);
INSERT INTO "public"."data_server_source" VALUES (5, 'org.postgresql.Driver', 'jdbc:postgresql://172.16.10.6:5432/', 'hgl', 'hgl', '2021-11-01 14:54:12', '2021-11-01 14:54:14', 10, 0, 2);
INSERT INTO "public"."data_server_source" VALUES (10, 'org.postgresql.Driver', 'jdbc:postgresql://172.16.10.72:5432,172.16.10.73:5432/', 'postgres', 'pgpg', '2021-11-01 14:54:12', '2021-11-01 14:54:14', 10, 0, 2);
 
-- ----------------------------
-- Primary Key structure for table data_server_source
-- ----------------------------
ALTER TABLE "public"."data_server_source" ADD CONSTRAINT "data_server_source_pkey1" PRIMARY KEY ("id");

用戶表省略，就是常規(guī)用戶表，加上數(shù)據(jù)庫(kù)id外鍵即可

注冊(cè)用戶時(shí)，

調(diào)用DynamicDataSourceService類的getDatabaseId() 方法，將用戶和數(shù)據(jù)庫(kù)綁定。

      entity.setDatabaseId(dataSourceService.getDatabaseId());

getDatabaseId() 講解根據(jù)配置的數(shù)據(jù)庫(kù)最大用戶數(shù)配置，方法內(nèi)部判斷當(dāng)前數(shù)據(jù)庫(kù)用戶數(shù)是否大于配置用戶，沒(méi)有則返回當(dāng)前數(shù)據(jù)庫(kù)id,有則返回下一個(gè)數(shù)據(jù)庫(kù)id

使用方法，調(diào)用接口時(shí)候，傳入token ，動(dòng)態(tài)數(shù)據(jù)庫(kù)攔截器，自動(dòng)獲取用戶id,切換對(duì)應(yīng)數(shù)據(jù)源。?

到此這篇關(guān)于Springboot實(shí)現(xiàn)根據(jù)用戶ID切換動(dòng)態(tài)數(shù)據(jù)源的文章就介紹到這了,更多相關(guān)Springboot切換動(dòng)態(tài)數(shù)據(jù)源內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: