Springboot实现根据用户ID切换动态数据源

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 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

*

* 文件简要说明

*

* @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_http://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,切换对应数据源。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。