Springboot實現根據用戶ID切換動態數據源
首先在application.yml 文件添加一下配置
#每個庫可連接最大用戶數 dynamic-server: #每個服務最大建庫數 database-max-number: 30 #每個庫最大用戶連接數 user-max-number: 200 template: gis_template
然後項目中添加 dynamicds 模塊的代碼，僅展示模塊文件目錄，代碼太多不展示瞭
然後添加攔截器
@Override
public void addInterceptors(InterceptorRegistry registry){
//排除登錄註冊攔截
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/**");
//默認數據源 不需要切換的 排除
registry.addInterceptor(dynamicDataSourceInterceptor()).addPathPatterns("/**")
.excludePathPatterns(patterns).order(1);
// registry.addInterceptor(dynamicDefaultDataSourceInterceptor()).addPathPatterns("/**").order(-1);
}
@Bean
public DynamicDataSourceInterceptor dynamicDataSourceInterceptor(){
return new DynamicDataSourceInterceptor();
}
動態數據源攔截器
跟據token 獲取用戶id 再根據用戶id切換對應數據源
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>
* 文件簡要說明
*
* @author 2021-10-28 tarzan 創建初始版本
* @version V1.0 簡要版本說明
*/
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) {
//獲取當前登錄用戶信息
BladeUser user = AuthUtil.getUser();
/* if(user == null || user.getUserId() == null){
throw new UnauthorizedException(ErrorEnum.E_401);
}*/
log.info("據源切換--------------用戶名-----"+user.getUserName()+"------------>【{}】", user.getUserId());
//如果未獲取到 dsName 重新加載數據庫
if(!dynamicDataSourceCache.hasDataSourceName(user.getUserId())){
dynamicDataSourceService.addUserDataSource(user.getUserId());
}
String dsName = dynamicDataSourceCache.getUserIdDataSourceName(user.getUserId());
if(!dynamicDataSource.switchDataSource(dsName)){
//如果切換數據源失敗 返回錯誤
throw new RuntimeException("未找到用戶數據庫");
}
return true;
}
}
數據庫設計
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
# 空閑連接存活最大時間，默認600000（10分鐘）
idle-timeout: 1200000
# 據庫連接超時時間,默認30秒
connection-timeout: 300000
初始鏈接一個基礎數據庫，放置用戶表，數據源表，數據庫表
數據庫表建表語句
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 '數據源連接唯一key';
COMMENT ON COLUMN "public"."data_server_database"."database_name" IS '數據庫名';
COMMENT ON COLUMN "public"."data_server_database"."source_id" IS '數據源id(data_server_source表主鍵id)';
COMMENT ON COLUMN "public"."data_server_database"."create_time" IS '創建時間';
COMMENT ON COLUMN "public"."data_server_database"."update_time" IS '更新時間';
COMMENT ON COLUMN "public"."data_server_database"."priority" IS '數據庫使用順序(升序)';
COMMENT ON COLUMN "public"."data_server_database"."amount" IS '數據使用用戶數量';
COMMENT ON COLUMN "public"."data_server_database"."status" IS '使用狀態(1:正在使用；2：本庫使用用戶數已滿)';
COMMENT ON TABLE "public"."data_server_database" IS '用戶連接的數據庫配置';
-- ----------------------------
-- 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 '數據源名 唯一';
-- ----------------------------
-- Primary Key structure for table data_server_database
-- ----------------------------
ALTER TABLE "public"."data_server_database" ADD CONSTRAINT "data_server_source_pkey" PRIMARY KEY ("id");
數據源表建表語句
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 '數據庫驅動';
COMMENT ON COLUMN "public"."data_server_source"."url" IS '數據庫連接url';
COMMENT ON COLUMN "public"."data_server_source"."user_name" IS '數據庫用戶名';
COMMENT ON COLUMN "public"."data_server_source"."password" IS '數據庫用戶密碼';
COMMENT ON COLUMN "public"."data_server_source"."create_time" IS '創建時間';
COMMENT ON COLUMN "public"."data_server_source"."update_time" IS '更新時間';
COMMENT ON COLUMN "public"."data_server_source"."priority" IS '數據庫服務使用順序(升序)';
COMMENT ON COLUMN "public"."data_server_source"."amount" IS '數據服務建庫數量';
COMMENT ON COLUMN "public"."data_server_source"."status" IS '使用狀態(1:正在使用；2：本服務建庫數已滿)';
COMMENT ON TABLE "public"."data_server_source" IS '數據庫服務的數據源連接表';
-- ----------------------------
-- 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");
用戶表 省略，就是常規用戶表，加上 數據庫id外鍵即可
註冊用戶時，
調用DynamicDataSourceService類的getDatabaseId() 方法，將用戶和數據庫綁定。
entity.setDatabaseId(dataSourceService.getDatabaseId());
getDatabaseId() 講解 根據配置的數據庫最大用戶數配置，方法內部判斷當前數據庫用戶數是否大於配置用戶，沒有則返回當前數據庫id,有則返回下一個數據庫id
使用方法，調用接口時候，傳入token ，動態數據庫攔截器，自動獲取用戶id,切換對應數據源。
到此這篇關於Springboot實現根據用戶ID切換動態數據源的文章就介紹到這瞭,更多相關Springboot切換動態數據源內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！
推薦閱讀：
- PostgreSQL標準建表語句分享
- PostgreSQL使用MySQL外表的步驟詳解(mysql_fdw)
- PostgreSQL入門簡介
- MySQL中CURRENT_TIMESTAMP的使用方式
- 教你使用VS Code的MySQL擴展管理數據庫的方法