首先在application.yml 文件添加一下配置
#每个库可连接最大用户数 dynamic-server: #每个服务最大建库数 database-max-number: 30 #每个库最大用户连接数 user-max-number: 200 template: gis_template
gis_template 是数据库模板，就是一个只有表结构的数据库，后边随着用户数的增加，用户数超过每个库的最大用户数的时候，就会根据gis_template自动创建一个新的用户库。
然后项目中添加 dynamicds 模块的代码，仅展示模块文件目录，代码太多。进站时部分代码
编辑
数据源配置类
import org.springblade.gis.dynamicds.interceptor.DynamicDataSourceInterceptor; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.web.servlet.config.annotation.InterceptorRegistry; import org.springframework.web.servlet.config.annotation.WebMvcConfigurer; @Configuration public class DynamicDataSourceConfiguration implements WebMvcConfigurer { @Bean public DynamicDataSourceInterceptor dynamicDataSourceInterceptor(){ return new DynamicDataSourceInterceptor(); } @Override public void addInterceptors(InterceptorRegistry registry){ //数据源拦截 registry.addInterceptor(dynamicDataSourceInterceptor()).addPathPatterns("/**").order(-99); } }
动态数据源拦截器
根据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){ //如果未获取到 dsName 重新加载数据库 if(!dynamicDataSourceCache.hasDataSourceName(user.getUserId())){ dynamicDataSourceService.addUserDataSource(user.getUserId()); } String dsName = dynamicDataSourceCache.getUserIdDataSourceName(user.getUserId()); if(!dynamicDataSource.switchDataSource(dsName)){ //如果切换数据源失败 返回错误 throw new RuntimeException("未找到用户数据库"); } log.info("数据源切换--------------用户名-----"+user.getUserName()+"------------>【{}】", dsName); }else{ log.info("数据源切换------------------------------->默认数据源"); dynamicDataSource.switchDefaultDataSource(); } 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() 方法，将用户和数据库绑定。
user.setDatabaseId(dataSourceService.getDatabaseId());
getDatabaseId() 讲解 根据配置的数据库最大用户数配置，方法内部判断当前数据库用户数是否大于配置用户，没有则返回当前数据库id,有则返回下一个数据库id
使用方法，调用接口时候，传入token ，动态数据库拦截器，自动获取用户id,切换对应数据源。
