前言 ：SaaS平台的实现模式有3种 简单来说分别是 分字段 分表 分库
这里作者展示的是隔离性最高的高的分库 当然对应的服务器成本也更高

图一

如图所示 总公司能够创建数据库并分账号

图二

创建数据库这里展示一下核心代码

1.测试数据是否能正常连接

public boolean testConnect(String ipAndPort, String username, String password) {
    final String defaultDatabaseName = "test";
    String url = new StringBuilder("jdbc:mysql://").append(ipAndPort).append("/").append(defaultDatabaseName).toString();
    Connection conn = null;
    try {
        System.out.println("连接数据库");
        //连接数据库
        conn = DataSourceBuilder.create().url(url).driverClassName(JDBCDriver).username(username).password(password).build().getConnection();
    } catch (SQLException se) {
        se.printStackTrace();
        return false;
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    } finally {
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
            return false;
        }
    }
    return true;
}

2开始创建数据库并插入默认数据

因为数据太多了这里省略N张表 直接看方法

public boolean createDatabases(Integer tenantId, String ipAndPort, String username, String password, String databaseName, String systemAccount, String systemPassword, String tenantName) {
    Connection conn = null;
    Statement stmt = null;
    PreparedStatement ps = null;

    try {
        String url = new StringBuilder("jdbc:mysql://").append(ipAndPort).append("/").toString();

        System.out.println("连接数据库");
        //连接数据库
        DataSource dataSource = DataSourceBuilder.create().url(url).driverClassName(JDBCDriver).username(username).password(password).build();
        conn = dataSource.getConnection();
        //获取执行的SQL的对象
        stmt = conn.createStatement();
        String sql = new StringBuilder().append("CREATE DATABASE").append(" ").append(databaseName).toString();
        stmt.executeUpdate(sql);
        System.out.println("数据库创建成功");
        stmt.close();
        conn.close();

        url = new StringBuilder("jdbc:mysql://").append(ipAndPort).append("/").append(databaseName).toString();
        //连接数据库
        dataSource = DataSourceBuilder.create().url(url).driverClassName(JDBCDriver).username(username).password(password).build();
        conn = dataSource.getConnection();
        stmt = conn.createStatement();


        String creatsql = "CREATE TABLE sys_user  (" +
                "id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id'," +
                "user_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名'," +
                "user_pwd varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '密码'," +
                "nick varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '昵称'," +
                "img_url varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '头像地址'," +
                "create_time datetime(0) NULL DEFAULT NULL COMMENT '创建时间'," +
                "PRIMARY KEY (id) USING BTREE," +
                "UNIQUE INDEX user_name(user_name) USING BTREE" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;";
        stmt.executeLargeUpdate(creatsql);

        String creatsql1 = "CREATE TABLE tbl_sys_user_role_relation  (" +
                "id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id'," +
                "user_id bigint(20) NULL DEFAULT NULL COMMENT '系统用户id'," +
                "role_id bigint(20) NULL DEFAULT NULL COMMENT '角色id'," +
                "PRIMARY KEY (id) USING BTREE" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后台用户和角色关系表' ROW_FORMAT = DYNAMIC;";
        stmt.executeLargeUpdate(creatsql1);
        ps = conn.prepareStatement(sql);
        ps.setInt(1, 32);
        ps.setInt(2, 30);
        ps.setString(3, "资源管理");
        ps.setString(4, "pms:permission");
        ps.setInt(5, 1);
        ps.setString(6, "permissionManager");
        ps.setInt(7, 1);
        ps.setTimestamp(8, new java.sql.Timestamp(System.currentTimeMillis()));
        ps.setInt(9, 32);
        result = ps.executeUpdate();// 返回值代表收到影响的行数


        sql = "INSERT INTO tbl_role(id, name, description, admin_count, create_time, status, sort) VALUES (1, '超级管理员', '拥有所有查看和操作功能', 0,'2020-02-02 00:00:01', 1, 0);";
        ps = conn.prepareStatement(sql);
        result = ps.executeUpdate();// 返回值代表收到影响的行数

        sql = "INSERT INTO tbl_sys_user_role_relation(id, user_id, role_id) VALUES (1, 1, 1);";
        ps = conn.prepareStatement(sql);
        result = ps.executeUpdate();// 返回值代表收到影响的行数

        sql = "INSERT INTO tbl_role_permission_relation(id, role_id, permission_id) VALUES (1, 1, 30);";
        ps = conn.prepareStatement(sql);
        result = ps.executeUpdate();// 返回值代表收到影响的行数

        sql = "INSERT INTO tbl_role_permission_relation(id, role_id, permission_id) VALUES (2, 1, 31);";
        ps = conn.prepareStatement(sql);
        result = ps.executeUpdate();// 返回值代表收到影响的行数

        sql = "INSERT INTO tbl_role_permission_relation(id, role_id, permission_id) VALUES (3, 1, 32);";
        ps = conn.prepareStatement(sql);
        result = ps.executeUpdate();// 返回值代表收到影响的行数

        System.out.println("插入成功" + username);

        // 添加到map中
        DynamicDataSource dynamicDataSource = (DynamicDataSource) SpringContextUtils.getBean("dynamicDataSource");
        Map<Object, Object> dataSourceMap = new HashMap<>();
        HikariDataSource master = (HikariDataSource) SpringContextUtils.getBean("master");
        HikariDataSource newDataSource = new HikariDataSource();
        newDataSource.setDriverClassName(JDBCDriver);
        newDataSource.setJdbcUrl(url + "?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai");
        newDataSource.setUsername(username);
        newDataSource.setPassword(password);
        newDataSource.setDataSourceProperties(master.getDataSourceProperties());
        dataSourceMap.put(String.valueOf(tenantId), newDataSource);
        // 设置数据源
        dynamicDataSource.setDataSources(dataSourceMap);
        /**
         * 必须执行此操作，才会重新初始化AbstractRoutingDataSource 中的 resolvedDataSources，也只有这样，动态切换才会起效
         */
        dynamicDataSource.afterPropertiesSet();

        System.out.println("新增数据源添加到dataSourceMap成功！");

    } catch (SQLException se) {
        se.printStackTrace();
        return false;
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    } finally {
        try {
            if (stmt != null)
                stmt.close();
            if (ps != null)
                ps.close();
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
            return false;
        }
    }
    return true;
}

重点来了 如何动态切换数据源

项目启动时将数据源放入在map中 交给spring管理 每个账号登入时都会得到一个租户ID 在调用接口的时候 将租户Id 传入Head 中 通过AOP 环绕增强@Around在切换数据源 如图二所示

图二

aop 环绕增强的核心代码

// 监听 app接口
@Around("execution(* com.spring.security.demo.controller.app.*.*(..))")
public Object appAround(ProceedingJoinPoint jp) throws Throwable {
    ServletRequestAttributes sra = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    Object result="";
    try {
        HttpServletRequest request = sra.getRequest();
      String tenantId = request.getHeader("tenantId");
        log.info("当前租户+tenantId"+tenantId);
        DynamicDataSourceContextHolder.setDataSourceKey(tenantId);
        result = jp.proceed();
    }catch (Exception e){
        e.printStackTrace();
        result ="系统异常";
    }finally {
        DynamicDataSourceContextHolder.clearDataSourceKey();
    }
    return result;
}