前言 ：SaaS平臺的實現(xiàn)模式有3種 簡單來說分別是 分字段 分表 分庫
這里作者展示的是隔離性最高的高的分庫 當(dāng)然對應(yīng)的服務(wù)器成本也更高

圖一

如圖所示 總公司能夠創(chuàng)建數(shù)據(jù)庫并分賬號

圖二

創(chuàng)建數(shù)據(jù)庫這里展示一下核心代碼

1.測試數(shù)據(jù)是否能正常連接

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("連接數(shù)據(jù)庫");
        //連接數(shù)據(jù)庫
        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開始創(chuàng)建數(shù)據(jù)庫并插入默認(rèn)數(shù)據(jù)

因為數(shù)據(jù)太多了這里省略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("連接數(shù)據(jù)庫");
        //連接數(shù)據(jù)庫
        DataSource dataSource = DataSourceBuilder.create().url(url).driverClassName(JDBCDriver).username(username).password(password).build();
        conn = dataSource.getConnection();
        //獲取執(zhí)行的SQL的對象
        stmt = conn.createStatement();
        String sql = new StringBuilder().append("CREATE DATABASE").append(" ").append(databaseName).toString();
        stmt.executeUpdate(sql);
        System.out.println("數(shù)據(jù)庫創(chuàng)建成功");
        stmt.close();
        conn.close();

        url = new StringBuilder("jdbc:mysql://").append(ipAndPort).append("/").append(databaseName).toString();
        //連接數(shù)據(jù)庫
        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 '創(chuàng)建時間'," +
                "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 '系統(tǒng)用戶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 = '后臺用戶和角色關(guān)系表' 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();// 返回值代表收到影響的行數(shù)


        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();// 返回值代表收到影響的行數(shù)

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

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

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

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

        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);
        // 設(shè)置數(shù)據(jù)源
        dynamicDataSource.setDataSources(dataSourceMap);
        /**
         * 必須執(zhí)行此操作脱货，才會重新初始化AbstractRoutingDataSource 中的 resolvedDataSources瞻颂，也只有這樣胀瞪，動態(tài)切換才會起效
         */
        dynamicDataSource.afterPropertiesSet();

        System.out.println("新增數(shù)據(jù)源添加到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;
}

重點來了 如何動態(tài)切換數(shù)據(jù)源

項目啟動時將數(shù)據(jù)源放入在map中 交給spring管理 每個賬號登入時都會得到一個租戶ID 在調(diào)用接口的時候 將租戶Id 傳入Head 中 通過AOP 環(huán)繞增強@Around在切換數(shù)據(jù)源 如圖二所示

圖二

aop 環(huán)繞增強的核心代碼

// 監(jiān)聽 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("當(dāng)前租戶+tenantId"+tenantId);
        DynamicDataSourceContextHolder.setDataSourceKey(tenantId);
        result = jp.proceed();
    }catch (Exception e){
        e.printStackTrace();
        result ="系統(tǒng)異常";
    }finally {
        DynamicDataSourceContextHolder.clearDataSourceKey();
    }
    return result;
}