spring boot 动态切换数据源实现多租户开发

lgq2016

已于 2022-03-10 14:41:58 修改

阅读量2.7k

点赞数

文章标签： spring boot java spring 多租户动态数据源切换

于 2022-03-09 09:56:50 首次发布

本文链接：https://blog.csdn.net/lgq2016/article/details/123368422

版权

之前的文章有介绍过spring boot 动态切换数据源spring boot 动态切换数据源（数据源信息从数据库中读取）_lgq2016的博客-CSDN博客，今天简单介绍一下动态数据源切换实战，主要是实现多租户功能，数据隔离采用的方式是：同一个库，多个schema（数据库使用oracle）实现。多租户实现的核心逻辑是：通过访问域名区分不同的租户，进而切换到不同的数据源，即不同的schema.

AbstractRoutingDataSource

spring中对切换数据源提供了动态设置方法，通过determineCurrentLookupKey()设置值切换对应数据源。org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource 源码的介绍如下：

/*
 * Copyright 2002-2012 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.springframework.jdbc.datasource.lookup;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.util.Assert;

/**
 * Abstract {@link javax.sql.DataSource} implementation that routes {@link #getConnection()}
 * calls to one of various target DataSources based on a lookup key. The latter is usually
 * (but not necessarily) determined through some thread-bound transaction context.
 *
 * @author Juergen Hoeller
 * @since 2.0.1
 * @see #setTargetDataSources
 * @see #setDefaultTargetDataSource
 * @see #determineCurrentLookupKey()
 */
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {

	private Map<Object, Object> targetDataSources;

	private Object defaultTargetDataSource;

	private boolean lenientFallback = true;

	private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();

	private Map<Object, DataSource> resolvedDataSources;

	private DataSource resolvedDefaultDataSource;


	/**
	 * Specify the map of target DataSources, with the lookup key as key.
	 * The mapped value can either be a corresponding {@link javax.sql.DataSource}
	 * instance or a data source name String (to be resolved via a
	 * {@link #setDataSourceLookup DataSourceLookup}).
	 * <p>The key can be of arbitrary type; this class implements the
	 * generic lookup process only. The concrete key representation will
	 * be handled by {@link #resolveSpecifiedLookupKey(Object)} and
	 * {@link #determineCurrentLookupKey()}.
	 */
	public void setTargetDataSources(Map<Object, Object> targetDataSources) {
		this.targetDataSources = targetDataSources;
	}

	/**
	 * Specify the default target DataSource, if any.
	 * <p>The mapped value can either be a corresponding {@link javax.sql.DataSource}
	 * instance or a data source name String (to be resolved via a
	 * {@link #setDataSourceLookup DataSourceLookup}).
	 * <p>This DataSource will be used as target if none of the keyed
	 * {@link #setTargetDataSources targetDataSources} match the
	 * {@link #determineCurrentLookupKey()} current lookup key.
	 */
	public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
		this.defaultTargetDataSource = defaultTargetDataSource;
	}

	/**
	 * Specify whether to apply a lenient fallback to the default DataSource
	 * if no specific DataSource could be found for the current lookup key.
	 * <p>Default is "true", accepting lookup keys without a corresponding entry
	 * in the target DataSource map - simply falling back to the default DataSource
	 * in that case.
	 * <p>Switch this flag to "false" if you would prefer the fallback to only apply
	 * if the lookup key was {@code null}. Lookup keys without a DataSource
	 * entry will then lead to an IllegalStateException.
	 * @see #setTargetDataSources
	 * @see #setDefaultTargetDataSource
	 * @see #determineCurrentLookupKey()
	 */
	public void setLenientFallback(boolean lenientFallback) {
		this.lenientFallback = lenientFallback;
	}

	/**
	 * Set the DataSourceLookup implementation to use for resolving data source
	 * name Strings in the {@link #setTargetDataSources targetDataSources} map.
	 * <p>Default is a {@link JndiDataSourceLookup}, allowing the JNDI names
	 * of application server DataSources to be specified directly.
	 */
	public void setDataSourceLookup(DataSourceLookup dataSourceLookup) {
		this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
	}


	@Override
	public void afterPropertiesSet() {
		if (this.targetDataSources == null) {
			throw new IllegalArgumentException("Property 'targetDataSources' is required");
		}
		this.resolvedDataSources = new HashMap<Object, DataSource>(this.targetDataSources.size());
		for (Map.Entry<Object, Object> entry : this.targetDataSources.entrySet()) {
			Object lookupKey = resolveSpecifiedLookupKey(entry.getKey());
			DataSource dataSource = resolveSpecifiedDataSource(entry.getValue());
			this.resolvedDataSources.put(lookupKey, dataSource);
		}
		if (this.defaultTargetDataSource != null) {
			this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
		}
	}

	/**
	 * Resolve the given lookup key object, as specified in the
	 * {@link #setTargetDataSources targetDataSources} map, into
	 * the actual lookup key to be used for matching with the
	 * {@link #determineCurrentLookupKey() current lookup key}.
	 * <p>The default implementation simply returns the given key as-is.
	 * @param lookupKey the lookup key object as specified by the user
	 * @return the lookup key as needed for matching
	 */
	protected Object resolveSpecifiedLookupKey(Object lookupKey) {
		return lookupKey;
	}

	/**
	 * Resolve the specified data source object into a DataSource instance.
	 * <p>The default implementation handles DataSource instances and data source
	 * names (to be resolved via a {@link #setDataSourceLookup DataSourceLookup}).
	 * @param dataSource the data source value object as specified in the
	 * {@link #setTargetDataSources targetDataSources} map
	 * @return the resolved DataSource (never {@code null})
	 * @throws IllegalArgumentException in case of an unsupported value type
	 */
	protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
		if (dataSource instanceof DataSource) {
			return (DataSource) dataSource;
		}
		else if (dataSource instanceof String) {
			return this.dataSourceLookup.getDataSource((String) dataSource);
		}
		else {
			throw new IllegalArgumentException(
					"Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
		}
	}


	@Override
	public Connection getConnection() throws SQLException {
		return determineTargetDataSource().getConnection();
	}

	@Override
	public Connection getConnection(String username, String password) throws SQLException {
		return determineTargetDataSource().getConnection(username, password);
	}

	@Override
	@SuppressWarnings("unchecked")
	public <T> T unwrap(Class<T> iface) throws SQLException {
		if (iface.isInstance(this)) {
			return (T) this;
		}
		return determineTargetDataSource().unwrap(iface);
	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return (iface.isInstance(this) || determineTargetDataSource().isWrapperFor(iface));
	}

	/**
	 * Retrieve the current target DataSource. Determines the
	 * {@link #determineCurrentLookupKey() current lookup key}, performs
	 * a lookup in the {@link #setTargetDataSources targetDataSources} map,
	 * falls back to the specified
	 * {@link #setDefaultTargetDataSource default target DataSource} if necessary.
	 * @see #determineCurrentLookupKey()
	 */
	protected DataSource determineTargetDataSource() {
		Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
		Object lookupKey = determineCurrentLookupKey();
		DataSource dataSource = this.resolvedDataSources.get(lookupKey);
		if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
			dataSource = this.resolvedDefaultDataSource;
		}
		if (dataSource == null) {
			throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
		}
		return dataSource;
	}

	/**
	 * Determine the current lookup key. This will typically be
	 * implemented to check a thread-bound transaction context.
	 * <p>Allows for arbitrary keys. The returned key needs
	 * to match the stored lookup key type, as resolved by the
	 * {@link #resolveSpecifiedLookupKey} method.
	 */
	protected abstract Object determineCurrentLookupKey();

}

基于AbstractRoutingDataSource的多数据源动态切换，除了实现多租户功能，还可以实现读写分离等功能，但是注意一般情况下多数据源是在启动时加载，因此无法动态的增加数据源，只能在项目启动时加载。当然也可以增加一个定时线程定时扫描动态数据源表（下面会介绍），动态的加载数据表中新增的数据源。

实现逻辑

定义DynamicDataSource类继承抽象类AbstractRoutingDataSource，并实现了determineCurrentLookupKey()方法。
启动时加载多个数据源，并配置到AbstractRoutingDataSource的defaultTargetDataSource和targetDataSources中。
创建动态数据源数据表并导入数据
对所有controller方法做aop，根据当前域名或者前端设置值修改本地线程动态数据源名称。

新建类 DynamicDataSource

package com.xxx.datasource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 动态获取数据源
 * author:lgq
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        String datasource = DynamicDataSourceContextHolder.getDataSource();
        return datasource;
    }
}

通过基于本地线程的上下文管理来切换数据源

package com.xxx.datasource;

import com.xxx.entity.enums.SchemaName;
import com.xxx.util.ObjectsUtil;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 动态数据源上下文管理
 * author:lgq
 * date:2022.1.1
 */
public class DynamicDataSourceContextHolder {

    private static final Logger contextHolderLogger = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);

    /**
     * 存放当前线程使用的数据源类型信息
     */
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();


    /**
     * 设置数据源
     *
     * @param dataSourceName
     */
    public static void setDataSource(String dataSourceName) {
        contextHolder.set(dataSourceName);
    }

    /**
     * 获取数据源
     */
    public static String getDataSource() {
        String dataSource = contextHolder.get();
        if (ObjectsUtil.isEmpty(dataSource)) {
            contextHolderLogger.debug("数据源标识为空，使用默认的数据源");
            dataSource = SchemaName.XXX.getDesc();//字符串"XXX"
        } else {
            contextHolderLogger.debug("使用数据源:" + dataSource);
        }
        return dataSource;
    }

    /**
     * 清除数据源
     */
    public static void clearDataSource() {
        contextHolder.remove();
    }
}

初始化数据源

package com.xxx.config;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.xxx.entity.TenantSchemaInfo;
import com.xxx.datasource.DynamicDataSource;
import com.xxx.entity.enums.SchemaName;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

/**
 * 德鲁伊数据源配置
 * author:lgq
 * date:2022.1.1
 */
@Configuration
public class DruidConfiguration {
    private Logger druidDBLog = LoggerFactory.getLogger(DruidConfiguration.class);

    // 数据库连接信息
    @Value("${spring.datasource.druid.url}")
    private String dbUrl;
    @Value("${spring.datasource.druid.username}")
    private String username;
    @Value("${spring.datasource.druid.password}")
    private String password;
    @Value("${spring.datasource.druid.driver-class-name}")
    private String driverClassName;
    // 连接池连接信息
    @Value("${spring.datasource.druid.initial-size}")
    private int initialSize;
    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;
    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;
    @Value("${spring.datasource.druid.max-wait}")
    private int maxWait;

    @Bean(name = "dateSource") // 声明其为Bean实例
    @Primary // 在同样的DataSource中，首先使用被标注的DataSource
    public DynamicDataSource dynamicDataSource() throws SQLException {
        druidDBLog.info("=====初始化动态数据源=====");
        /**
         * 主数据源连接信息
         * 建立主数据源
         */
        TenantSchemaInfo tenantSchemaInfo = new TenantSchemaInfo();
        tenantSchemaInfo.setAccount(username);
        tenantSchemaInfo.setPassword(password);
        tenantSchemaInfo.setConUrl(dbUrl);
        DataSource dataSource = buildDataSource(tenantSchemaInfo);
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(dataSource);
        // 配置多数据源
        Map<Object, Object> customDataSources = new HashMap<>();
        List<String> customNames = new ArrayList<>();
        Connection connection;
        String name = "";
        try {
            connection = dataSource.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement("select * from TENANT_SCHEMA_INFO where IS_DELETE = 'N'");//oracle数据库
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                name = resultSet.getString(2);
                customNames.add(name);
                /**
                 * 主数据源在上面已经创建过
                 */
                if (name.equals(SchemaName.ALIBABA.getDesc())) {
                    customDataSources.put(name, dataSource);
                    continue;
                }
                tenantSchemaInfo.setConUrl(resultSet.getString(12));
                tenantSchemaInfo.setAccount(resultSet.getString(5));
                tenantSchemaInfo.setPassword(resultSet.getString(6));
                tenantSchemaInfo.setDomainName(resultSet.getString(4));
                tenantSchemaInfo.setName(resultSet.getString(2));
                DataSource ds = buildDataSource(tenantSchemaInfo);
                customDataSources.put(name, ds);
            }
        } catch (SQLException e) {
            druidDBLog.error("数据源：" + name + "创建出错！", e.getCause());
            throw e;
        }
        dynamicDataSource.setTargetDataSources(customDataSources);
        druidDBLog.info("已加载租户库数据源" + customNames);
        return dynamicDataSource;
    }

    private DataSource buildDataSource(TenantSchemaInfo tenantSchemaInfo) throws SQLException {
        DruidDataSource datasource = new DruidDataSource();
        // 基础连接信息
        datasource.setUrl(tenantSchemaInfo.getConUrl());
        datasource.setUsername(tenantSchemaInfo.getAccount());
        datasource.setPassword(tenantSchemaInfo.getPassword());
        datasource.setDriverClassName(driverClassName);
        // 连接池连接信息
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        //是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
        datasource.setPoolPreparedStatements(true);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(20);
        // 对于耗时长的查询sql，会受限于ReadTimeout的控制，单位毫秒
        //  datasource.setConnectionProperties("oracle.net.CONNECT_TIMEOUT=6000;oracle.jdbc.ReadTimeout=60000");
        //对于耗时长的查询sql，会受限于ReadTimeout的控制，单位毫秒
        datasource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000");
        //申请连接时执行validationQuery检测连接是否有效，这里建议配置为TRUE，防止取到的连接不可用
        datasource.setTestOnBorrow(true);
        //建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
        datasource.setTestWhileIdle(true);
        String validationQuery = "select 1 from dual";
        //用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
        datasource.setValidationQuery(validationQuery);
        //属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
        datasource.setFilters("stat");
        //配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        datasource.setTimeBetweenEvictionRunsMillis(60000);
        //配置一个连接在池中最小生存的时间，单位是毫秒，这里配置为3分钟180000
        datasource.setMinEvictableIdleTimeMillis(180000);
        //打开druid.keepAlive之后，当连接池空闲时，池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作，
        // 即执行druid.validationQuery指定的查询SQL，一般为select * from dual，只要minEvictableIdleTimeMillis设置的小于防火墙切断连接时间，
        // 就可以保证当连接空闲时自动做保活检测，不会被防火墙切断
        datasource.setKeepAlive(true);
        //是否移除泄露的连接/超过时间限制是否回收。
        datasource.setRemoveAbandoned(true);
        //泄露连接的定义时间(要超过最大事务的处理时间)；单位为秒。这里配置为1小时
        datasource.setRemoveAbandonedTimeout(3600);
        //移除泄露连接发生是是否记录日志
        datasource.setLogAbandoned(true);
        return datasource;
    }

    /**
     * 注册一个StatViewServlet
     *
     * @return
     */
    @Bean
    public ServletRegistrationBean DruidStatViewServlet() {
        // org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        //添加初始化参数：initParams

        //白名单：
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");

        //IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
        servletRegistrationBean.addInitParameter("deny", "192.168.0.114");

        //登录查看信息的账号密码.
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");

        //是否能够重置数据.
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    /**
     * 注册一个：filterRegistrationBean
     *
     * @return
     */
    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());

        //添加过滤规则.
        filterRegistrationBean.addUrlPatterns("/*");

        //添加不需要忽略的格式信息.
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");

        return filterRegistrationBean;
    }
}

动态数据源表及实体对象

ID	NAME	DATABASE_TYPE	DOMAIN_NAME	ACCOUNT	PASSWORD	CREATOR	CREATE_TIME	EDITOR	EDIT_TIME	IS_DELETE	CON_URL
1	阿里	ORACLE	alibabagroup.com	ALIBABA	123456	ZHANGSAN	2022/1/1 16:31			N	jdbc:oracle:thin:@10.1.1.1:1521:abc
2	字节	ORACLE	bytedance.com	BYTEDANCE	qwertyu	ZHANGSAN	2022/1/1 16:34			N	jdbc:oracle:thin:@10.1.1.2:1521:abc
3	腾讯	ORACLE	tencent.com	TENCENT	asdfgh	ZHANGSAN	2022/1/1 16:35			N	jdbc:oracle:thin:@10.1.1.3:1521:abc

package com.xxx.entity;

import java.util.Date;

import lombok.Data;

@Data
public class TenantSchemaInfo {
    private Short id;

    private String name;

    private String databaseType;

    private String domainName;

    private String account;

    private String password;

    private String creator;

    private Date createTime;

    private String editor;

    private String editTime;

    private String isDelete;

    private String conUrl;
}

所有请求AOP

package com.xxx.datasource;

import javax.servlet.http.HttpServletRequest;

import com.xxx.entity.enums.SchemaName;
import com.xxx.util.ObjectsUtil;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

/**
 * controller请求aop
 * author:lgq
 * date:2022.1.1
 */
@Component
@Aspect
@Order(-1)//不加执行顺序会在determineCurrentLookupKey之后
public class DataSourceAspect {
    private Logger logger = LoggerFactory.getLogger(DataSourceAspect.class);

    @Pointcut("execution(public * com.xxx.controller..*.*(..))")
    private void cutController() {
    }

    @Before("cutController()")
    public void before(JoinPoint joinPoint) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        // 租户标识, 这里使用域名来识别不同租户
        String url = request.getRequestURL().toString();
        if (!ObjectsUtil.isEmpty(url)) {
            if (url.contains("alibabagroup.com")) {
                DynamicDataSourceContextHolder.setDataSource(SchemaName.ALIBABA.getDesc());
            } else if (url.contains("bytedance.com")) {
                DynamicDataSourceContextHolder.setDataSource(SchemaName.BYTEDANCE.getDesc());
            }  else if (url.contains("tencent.com")) {
                DynamicDataSourceContextHolder.setDataSource(SchemaName.TENCENT.getDesc());
            } 
        } else {
            DynamicDataSourceContextHolder.clearDataSource();
        }
    }
}

参考文章：

spring boot 项目中多租户的实现 - 简书