动态创建租户数据源

对于多租户来说，常见的数据隔离方案有以下几种：

• 所有租户使用同一数据源下同一数据库下共同数据表（单数据源单数据库单数据表）
• 所有租户使用同一数据源下同一数据库下不同数据表（单数据源单数据库多数据表）
• 所有租户使用同一数据源下不同数据库下不同数据表（单数据源多数据库多数据表）
• 所有租户使用不同数据源下不同数据库下不同数据表（多数据源多数据库多数据表）

抛开成本、维护等问题，本文简单谈谈多数据源多数据库的代码实现。

废话不多说，直接上代码：

Controller：

@RestController
@RequestMapping("/ds")
public class DatasourceController {

    @Autowired
    private DatasourceService datasourceService;


    /**
     * 根据租户ID动态创建租户数据源
     *  支持单数据源多数据库
     *  支持多数据源多数据库
     *
     * @return
     */
    @GetMapping("/initTenantDs/{id}")
    public String initTenantDs(@PathVariable int id){
        return datasourceService.initTenantDs(id);
    }
}

Service：

@Service
public class DatasourceService {

    private static final Logger LOGGER = LoggerFactory.getLogger(DatasourceService.class);

    @Resource
    private DatasourceMapper datasourceMapper;

    public String initTenantDs(int id) {

        Tenant tenant = datasourceMapper.getTenantById(id);

        initDbSchema(tenant);//初始化schema

        return "";
    }

    public void initDbSchema(Tenant tenant) {

        ScriptRunner runner = getScriptRunner(tenant);
        try {
            //创建租户schema
            runner.runScript(new StringReader(StrUtil.format(DbConstant.CREATE_SCHEMA, tenant.getTenantCode()))); //创建租户专属schema
            //初始化库表及数据
            runScript(runner, tenant.getTenantCode(), "scripts/init-tables.sql");

        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    private void runScript(ScriptRunner runner, String schema, String path) {
        try {
            String useDb = StrUtil.format("use {};", schema);

            StringBuilder script = new StringBuilder();
            script.append(useDb);
            script.append(System.lineSeparator());

            Reader reader = Resources.getResourceAsReader(path);
            BufferedReader lineReader = new BufferedReader(reader);
            String line;
            while ((line = lineReader.readLine()) != null) {
                script.append(line);
                script.append(System.lineSeparator());
            }
//                log.info("------> 拼接后的SQL语句: {}", script.toString());
            runner.runScript(new StringReader(script.toString()));
        } catch (Exception e) {
            LOGGER.error("初始化数据失败", e);
        }
    }

    public static ScriptRunner getScriptRunner(Tenant tenant) {
        try {
            Connection connection = getConnection(tenant);
            ScriptRunner runner = new ScriptRunner(connection);
            runner.setAutoCommit(true);
            runner.setStopOnError(true);
            return runner;
        } catch (Exception e) {
            LOGGER.error("初始化ScriptRunner失败...", e);
        }
        return null;
    }

    public static Connection getConnection(Tenant tenant) {
        try {
            //jdbc:mysql://192.168.3.130:3307/lisi?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
            String dbUrl = tenant.getDbUrl();
            //解析出DB的 IP 和 端口
            String iport = "";
//            String substring = dbUrl.substring(dbUrl.indexOf("//") + 2, dbUrl.lastIndexOf("/"));
            Matcher matcher = Pattern.compile("(\\d+\\.\\d+\\.\\d+\\.\\d+)\\:(\\d+)").matcher(dbUrl);//正则匹配
            while (matcher.find()) {
                iport = matcher.group();
            }
            String url = StrUtil.format(DbConstant.URL, iport, DbConstant.DEFAULT_CONN_SCHEMA);
            return DriverManager.getConnection(url, tenant.getDbUser(), tenant.getDbPassword());
        } catch (SQLException e) {
            LOGGER.error("获取数据源连接失败！", e);
        }
        return null;
    }

}

Mapper

public interface DatasourceMapper {

    @Select("select tenant_name as tenantName, tenant_code as tenantCode, db_url as dbUrl, db_user as dbUser, db_password as dbPassword from t_tenant where id=#{id} ")
    Tenant getTenantById(int id);
}

Filter：

@Component
@WebFilter(urlPatterns = "/**")
public class TenantHolderFilter extends OncePerRequestFilter {

    private static final Logger log = LoggerFactory.getLogger(TenantHolderFilter.class);

    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain chain) throws ServletException, IOException {

        log.info("+++++++++++++请求路径:[=>{}<=]+++请求时间:[{}]++++++++++++++++++++++++", request.getRequestURL(), LocalDateTime.now());
        String tenantCode = request.getHeader("X-TENANT-ID");
        log.info("处理请求的线程是: [{}], 租户是: [{}]", Thread.currentThread().getName(), tenantCode);


        //设置tenant
        TenantHolder.setTenantCode(tenantCode);
        try {
            chain.doFilter(request, response);
        } catch (Exception e) {
            //异常处理
            log.error("{}", e.getMessage());
        } finally {
            //移除tenant
            TenantHolder.remove();
        }
    }

}

Constant：

public class DbConstant {

    public static final String CREATE_SCHEMA = "CREATE DATABASE `{}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;";
    public static final String DEFAULT_DRIVER_CLASS_NAME = "com.mysql.cj.jdbc.Driver";
    public static final String DEFAULT_CONN_SCHEMA = "mysql";
    public static final String URL = "jdbc:mysql://{}/{}?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&nullCatalogMeansCurrent=true";

}

租户信息表：

 使用IDEA 的Http client工具测试，测试结果如下：

 

 

根据租户ID新增的数据库/表

 打完收工......

参考：

springboot实现动态数据源，动态数据源原理与解析，租户系统动态数据源_51CTO博客_springboot 动态数据源

4种多租户数据库设计方案对比及思考，一文全讲透_神州数码云基地的博客-CSDN博客