SpringBoot + BeetlSQL3 多租户改造

多租户事务请参考另一篇文章:https://segmentfault.com/a/1190000041495681 最近听闻beetlSQL升级到3了,正好最近在研究多租户,于是乎就做了一下beetlSQL3的实现(分库分表)。 [BeetlSQL3官方文档](https://www.kancloud.cn/xiandafu/beetlsql3_guide/1945631)基本的思路是将租户id与数据源连接信息作为Map加载到动态数据源中, 过来的请求将租户id放到header上,通过配置过滤器获取当前请求的tenantId,然后设置当前Thread。 通过重写determineCurrentLookupKey方法根据当前租户id获取Datasource实现分库。

1. pom追加beetlSQL3的引用 com.ibeetl sql-springboot-starter 3.3.9-RELEASE

2. application.yml *这里设置主数据源,主数据源数据库里有租户管理表。*spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://略 username: root password: hikari: connection-timeout: 15000 connection-test-query: SELECT 1 max-lifetime: 200000 idle-timeout: 200000

beetlsql: sqlManagers: sqlManager1 sqlManager1: ds: master basePackage: org.beetl.sql.springboot.simple daoSuffix: Dao

3. DynamicDataSource 这个类用来继承AbstractRoutingDataSource,原理就是通过lookupKey去找当前数据源,详细说明网上应该有很多,这里只做实现。public class DynamicDataSource extends AbstractRoutingDataSource {@Override public DataSource determineTargetDataSource() { return super.determineTargetDataSource(); }/** * 底层根据lookupKey去找数据源,所以这里重写,去ThreadLocal里去取 * * @see org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource#determineCurrentLookupKey() */ @Override public Object determineCurrentLookupKey() { return DynamicDataSourceContextHolder.getDataSourceKey(); }/** * 设置默认数据源 * * @param defaultDataSource */ public void setDefaultDataSource(Object defaultDataSource) { super.setDefaultTargetDataSource(defaultDataSource); }/** * 设置数据源 * * @param dataSources */ public void setDataSources(Map dataSources) { super.setTargetDataSources(dataSources); // 将数据源的 key 放到数据源上下文的 key 集合中,用于切换时判断数据源是否有效 DynamicDataSourceContextHolder.addDataSourceKeys(dataSources.keySet()); }}

4. DynamicDataSourceContextHolder ThreadLocal控制public class DynamicDataSourceContextHolder {private static final ThreadLocal contextHolder = new ThreadLocal() { /** * 将 master 数据源的 key作为默认数据源的 key */ @Override protected String initialValue() { return "master"; }}; /** * 数据源的 key集合,用于切换时判断数据源是否存在 */ public static List dataSourceKeys = new ArrayList<>(); /** * 切换数据源 * * @param key */ public static void setDataSourceKey(String key) { contextHolder.set(key); }/** * 获取数据源 * * @return */ public static String getDataSourceKey() { return contextHolder.get(); }/** * 重置数据源 */ public static void clearDataSourceKey() { contextHolder.remove(); }/** * 判断是否包含数据源 * * @param key 数据源key * @return */ public static boolean containDataSourceKey(String key) { return dataSourceKeys.contains(key); }/** * 添加数据源keys * * @param keys * @return */ public static boolean addDataSourceKeys(Collection keys) { return dataSourceKeys.addAll(keys); }
5. DataSourceConfig 数据源注入@Configuration public class DataSourceConfig {/** * 环境 */ @Autowired private Environment env; /** * 主数据源 * * @return */ @Primary @Bean(name = "master") public DataSource master() { HikariDataSource ds = new HikariDataSource(); ds.setJdbcUrl(env.getProperty("spring.datasource.url")); ds.setUsername(env.getProperty("spring.datasource.username")); ds.setPassword(env.getProperty("spring.datasource.password")); ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name")); Properties dsProperties = hikariProperties(); ds.setDataSourceProperties(nameConvert(dsProperties)); return ds; }/** * 动态数据源加载 * * @return DataSource */ @Bean("dynamicDataSource") public DataSource dynamicDataSource() {DynamicDataSource dynamicDataSource = new DynamicDataSource(); Map dataSourceMap = new HashMap<>(); // 将 master 数据源作为默认指定的数据源 dataSourceMap.put("master", master()); dynamicDataSource.setDefaultDataSource(master()); //从数据库中获取其他动态数据源 SQLManager sqlManager = SpringUtil.getBean(SQLManager.class); HikariDataSource master = (HikariDataSource) master(); SysTenant query = new SysTenant(); query.setDelFlag(0); List tenantList = sqlManager.template(query); for (SysTenant dto : tenantList) { HikariDataSource dataSource = new HikariDataSource(); dataSource.setDriverClassName(dto.getDriver()); dataSource.setJdbcUrl(dto.getUrl()); dataSource.setUsername(dto.getUsername()); dataSource.setPassword(dto.getPassword()); dataSource.setDataSourceProperties(master.getDataSourceProperties()); dataSourceMap.put(dto.getTenantId(), dataSource); }//设置数据源 dynamicDataSource.setDataSources(dataSourceMap); //执行生效 dynamicDataSource.afterPropertiesSet(); return dynamicDataSource; }/** * hikari连接池 * * @return Properties */ @Bean(name = "hikariProperties") @ConfigurationProperties("spring.datasource.hikari") public Properties hikariProperties() { Properties dsProperties = new Properties(); return dsProperties; }/** * 转换 * * @param srcPorp * @return Properties */ private Properties nameConvert(Properties srcPorp) { Properties descProp = new Properties(); if (srcPorp == null) { return descProp; }Enumeration enu = srcPorp.propertyNames(); while (enu.hasMoreElements()) { String key = (String) enu.nextElement(); String underLineStr = StrUtil.replace(key, "-", "_"); String underLineKey = StrUtil.toCamelCase(underLineStr); descProp.put(underLineKey, srcPorp.get(key)); }return descProp; }}

6. TenantFilter 多租户过滤器@Configuration @WebFilter(urlPatterns = "/*", filterName = "TenantFilter") public class TenantFilter implements Filter {/** * LOGGER. */ private static final Logger log = LoggerFactory.getLogger(TenantFilter.class); @Override public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain chain) throws IOException, ServletException {HttpServletRequest request = (HttpServletRequest) servletRequest; HttpServletResponse response = (HttpServletResponse) servletResponse; String tenantId = request.getHeader("tenantId"); log.info("当前租户ID:" + tenantId); if (StrUtil.isEmpty(tenantId)) { tenantId = "master"; }//设置key DynamicDataSourceContextHolder.setDataSourceKey(tenantId); //放行 chain.doFilter(request, response); }}

7. HelpUtil帮助类,用来获取当前租户激活的数据源,创建 SQLManager 对象。public class HelpUtil {/** * 获取当前的SQLManager * * @return SQLManager */ public static SQLManager getCurrentSqlManaget() {SQLManager sqlManager = null; try { String key = DynamicDataSourceContextHolder.getDataSourceKey(); DynamicDataSource dynamicDataSource = (DynamicDataSource) SpringUtil.getBean("dynamicDataSource"); //获取当前DataSource DataSource dataSource = dynamicDataSource.determineTargetDataSource(); //构建SQLManager ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource); SQLManagerBuilder builder = new SQLManagerBuilder(source); builder.setNc(new UnderlinedNameConversion()); builder.setInters(new Interceptor[] { new DebugInterceptor() }); builder.setDbStyle(new MySqlStyle()); builder.setName(key); sqlManager = builder.build(); } catch (Exception e) {}return sqlManager; } }

8. 查询示例 /** * 通过登录名获取系统用户信息 * * @param loginName 用户登录名 * @return 用户信息 */ public LogonUserDto getUserInfoByName(String loginName) {SQLManager sqlManager = HelpUtil.getCurrentSqlManaget(); SysUser query = new SysUser(); query.setLoginName(loginName); SysUser user = sqlManager.templateOne(query); if (user == null) { return null; }LogonUserDto dto = new LogonUserDto(user); return dto; }

    推荐阅读