Springboot实现根据用户ID切换动态数据源
短信预约 -IT技能 免费直播动态提醒
首先在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
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();
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切换动态数据源内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
