java - Multi-Tenancy 应用程序 Java Spring Hibernate Mysql OAuth2 Spring Security

Question

我正在开发支持 Multi-Tenancy 的 POC java 应用程序。我使用 JHipster 生成器启动我的 POC，并在 spring boot 上启动 OAUTH2 身份验证。每个租户都有自己的 SCHEMA，但租户和 OAUTH2 表是公开的。 JHipster 使用 hibernate 和 Spring Data 连接数据库。在我的示例中，我使用 Mysql 作为数据库。

我想用单个数据源和单个连接池实现解决方案。作为连接池，JHipster 使用 HikariCP。在 MultiTenantConnectionProvider 中，我想以类似 Hibernate 文档描述的方式更改 SCHEMA(参见示例 16.3。)

http://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html#d5e4866

当 hibernate 调用 getConnection(String tenantIdentifier) 时，我想在 MYSQL 数据库中设置正确的 SCHEMA。我的实现使用 Mysql 命令来更改方案“USE sample_tenant_identifier”。我必须使用名称为“user”和“admin”的用户。每个用户都有自己的 SCHEMA。我遇到的问题很奇怪。例如，所有 SELECT 操作都使用“user”模式，但 INSERT 或 UPDATE 使用“admin”模式。结果“admin”在“user”SCHEMA 中看到数据，但将数据插入“admin”SCHEMA。

包 com.mycompany.myapp.tenancy.hibernate;

import org.hibernate.HibernateException;
import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * Created by AdamS on 2015-04-02.
 */
public class SchemaMultiTenantConnectionProviderImpl  implements MultiTenantConnectionProvider, ServiceRegistryAwareService {

    private final Logger log = LoggerFactory.getLogger(SchemaMultiTenantConnectionProviderImpl.class);
    DataSource dataSource;

    @Override
    public Connection getAnyConnection() throws SQLException {
        return this.dataSource.getConnection();
    }

    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        try {
            connection.createStatement().execute("USE jhipster;");
        }
        catch (SQLException e) {
            throw new HibernateException("Could not alter JDBC connection to specified schema [public]", e);
        }
        connection.close();
    }

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        log.debug("Tenatd is:"+tenantIdentifier);
        final Connection connection = getAnyConnection();
        try {
            connection.createStatement().execute("USE " + tenantIdentifier + ";");
//            connection.setCatalog(tenantIdentifier);
//            connection.setSchema(tenantIdentifier);
        }
        catch (SQLException e) {
            throw new HibernateException("Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]", e);
        }
        return connection;
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
//        try {
//            connection.createStatement().execute("USE "+tenantIdentifier+";");
//        }
//        catch (SQLException e) {
//            throw new HibernateException("Could not alter JDBC connection to specified schema [public]", e);
//        }
        connection.close();
    }

    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }

    @Override
    public boolean isUnwrappableAs(Class unwrapType) {
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> unwrapType) {
        return null;
    }

    @Override
    public void injectServices(ServiceRegistryImplementor serviceRegistry) {
        Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();
        DataSource localDs =  (DataSource) lSettings.get("hibernate.connection.datasource");
        dataSource = localDs;
    }
}

我创建了第二个工作示例，我在其中为每个租户创建了新的 DataSource 并将其存储在 Map 中。这个例子工作正常，但并发映射这不是我想要的。

package com.mycompany.myapp.tenancy.hibernate;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


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

/**
 * Created by AdamS on 2015-03-12.
 */
public class MyMultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider, ServiceRegistryAwareService {

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

    DataSource dataSource;
    private Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();

    public MyMultiTenantConnectionProviderImpl() {
        getSource("main");
    }

    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {

        connection.close();
    }

    @Override
    public Connection getAnyConnection() throws SQLException {
        //return this.dataSource.getConnection();
        log.info("get eny connection return main");
        return getSource("jhipster").getConnection();
    }

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        log.info("Tenatd is:" + tenantIdentifier);

        return getSource(tenantIdentifier).getConnection();
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {

        log.info("releaseConnection " + tenantIdentifier);
        connection.close();
    }

    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }

    @Override
    public boolean isUnwrappableAs(Class unwrapType) {
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> unwrapType) {
        return null;
    }

    @Override
    public void injectServices(ServiceRegistryImplementor serviceRegistry) {
        Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();
        DataSource localDs =  (DataSource) lSettings.get("hibernate.connection.datasource");
        dataSource = localDs;
    }

    public DataSource getSource(String tentant) {
        if(dataSourceMap.containsKey(tentant)){
            return dataSourceMap.get(tentant);
        } else {
            DataSource ds = dataSource(tentant);
            dataSourceMap.put(tentant,ds);
            return ds;
        }
    }

    public DataSource dataSource(String tentant) {
        log.info("Create Datasource "+tentant);

        HikariConfig config = new HikariConfig();
        config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
        config.addDataSourceProperty("url", "jdbc:mysql://localhost:3306/"+tentant);
        config.addDataSourceProperty("user", "root");
        config.addDataSourceProperty("password", "");

        //MySQL optimizations, see https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
        config.addDataSourceProperty("cachePrepStmts", true);
        config.addDataSourceProperty("prepStmtCacheSize",  "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("useServerPrepStmts", "true");

        return new HikariDataSource(config);
    }
}

我的配置类:

package com.mycompany.myapp.config;
...


@Configuration
@EnableJpaRepositories(basePackages  = {"com.mycompany.myapp.repository"},entityManagerFactoryRef = "entityManagerFactory",transactionManagerRef = "transactionManager")
@EnableJpaAuditing(auditorAwareRef = "springSecurityAuditorAware")
//@EnableTransactionManagement()
//@EnableAutoConfiguration(exclude = HibernateJpaAutoConfiguration.class)
public class DatabaseConfiguration implements EnvironmentAware {

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

    private RelaxedPropertyResolver propertyResolver;

    private Environment env;

    private DataSource dataSource;

    @Autowired(required = false)
    private MetricRegistry metricRegistry;

    @Override
    public void setEnvironment(Environment env) {
        this.env = env;
        this.propertyResolver = new RelaxedPropertyResolver(env, "spring.datasource.");
    }

    @Bean(destroyMethod = "shutdown")
    @ConditionalOnMissingClass(name = "com.mycompany.myapp.config.HerokuDatabaseConfiguration")
    @Profile("!" + Constants.SPRING_PROFILE_CLOUD)
    public DataSource dataSource() {
        log.debug("Configuring Datasource");
        if (propertyResolver.getProperty("url") == null && propertyResolver.getProperty("databaseName") == null) {
            log.error("Your database connection pool configuration is incorrect! The application" +
                    "cannot start. Please check your Spring profile, current profiles are: {}",
                    Arrays.toString(env.getActiveProfiles()));

            throw new ApplicationContextException("Database connection pool is not configured correctly");
        }
        HikariConfig config = new HikariConfig();
        config.setDataSourceClassName(propertyResolver.getProperty("dataSourceClassName"));
        if (propertyResolver.getProperty("url") == null || "".equals(propertyResolver.getProperty("url"))) {
            config.addDataSourceProperty("databaseName", propertyResolver.getProperty("databaseName"));
            config.addDataSourceProperty("serverName", propertyResolver.getProperty("serverName"));
        } else {
            config.addDataSourceProperty("url", propertyResolver.getProperty("url"));
        }
        config.addDataSourceProperty("user", propertyResolver.getProperty("username"));
        config.addDataSourceProperty("password", propertyResolver.getProperty("password"));

        //MySQL optimizations, see https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
        if ("com.mysql.jdbc.jdbc2.optional.MysqlDataSource".equals(propertyResolver.getProperty("dataSourceClassName"))) {
            config.addDataSourceProperty("cachePrepStmts", propertyResolver.getProperty("cachePrepStmts", "true"));
            config.addDataSourceProperty("prepStmtCacheSize", propertyResolver.getProperty("prepStmtCacheSize", "250"));
            config.addDataSourceProperty("prepStmtCacheSqlLimit", propertyResolver.getProperty("prepStmtCacheSqlLimit", "2048"));
            config.addDataSourceProperty("useServerPrepStmts", propertyResolver.getProperty("useServerPrepStmts", "true"));
        }
        if (metricRegistry != null) {
            config.setMetricRegistry(metricRegistry);
        }
        dataSource = new HikariDataSource(config);
        return dataSource;
    }

    @Bean
    public SpringLiquibase liquibase(DataSource dataSource) {
        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setDataSource(dataSource);
        liquibase.setChangeLog("classpath:config/liquibase/master.xml");
        liquibase.setContexts("development, production");
        if (env.acceptsProfiles(Constants.SPRING_PROFILE_FAST)) {
            if ("org.h2.jdbcx.JdbcDataSource".equals(propertyResolver.getProperty("dataSourceClassName"))) {
                liquibase.setShouldRun(true);
                log.warn("Using '{}' profile with H2 database in memory is not optimal, you should consider switching to" +
                    " MySQL or Postgresql to avoid rebuilding your database upon each start.", Constants.SPRING_PROFILE_FAST);
            } else {
                liquibase.setShouldRun(false);
            }
        } else {
            log.debug("Configuring Liquibase");
        }
        return liquibase;
    }

    @Bean
    public MultiTenantSpringLiquibase liquibaseMt(DataSource dataSource) throws SQLException {
        MultiTenantSpringLiquibase multiTenantSpringLiquibase = new MultiTenantSpringLiquibase();
        multiTenantSpringLiquibase.setDataSource(dataSource);

        Statement stmt = null;
        stmt = dataSource.getConnection().createStatement();

        ResultSet rs = stmt.executeQuery("SELECT tu.tentantId FROM t_user tu WHERE tu.tentantId IS NOT NULL");
        ArrayList<String> schemas = new ArrayList<>();
        while(rs.next()) {
            String schemaName = rs.getString("tentantId");
            dataSource.getConnection().createStatement().executeUpdate("CREATE DATABASE IF NOT EXISTS "+schemaName);
            schemas.add(schemaName);
        }

        multiTenantSpringLiquibase.setSchemas(schemas);
        multiTenantSpringLiquibase.setChangeLog("classpath:config/liquibase/mt_master.xml");
        multiTenantSpringLiquibase.setContexts("development, production");
        if (env.acceptsProfiles(Constants.SPRING_PROFILE_FAST)) {
            if ("org.h2.jdbcx.JdbcDataSource".equals(propertyResolver.getProperty("dataSourceClassName"))) {
                multiTenantSpringLiquibase.setShouldRun(true);
                log.warn("Using '{}' profile with H2 database in memory is not optimal, you should consider switching to" +
                    " MySQL or Postgresql to avoid rebuilding your database upon each start.", Constants.SPRING_PROFILE_FAST);
            } else {
                multiTenantSpringLiquibase.setShouldRun(false);
            }
        } else {
            log.debug("Configuring MultiTenantSpringLiquibase");
        }

        return multiTenantSpringLiquibase;
    }

    @Bean
    public Hibernate4Module hibernate4Module() {
        return new Hibernate4Module();
    }
}

和 EntityManagerConfiguration:

package com.mycompany.myapp.config;

....

/**
 * Created by AdamS on 2015-03-31.
 */
@Configuration
@EnableTransactionManagement
public class EntityManagerConfiguration  {
    @Autowired
    private DataSource dataSource;

    @Autowired
    private JpaVendorAdapter jpaVendorAdapter;

    @Bean(name = "entityManagerFactory")
    //@DependsOn("transactionManager")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws Throwable {

        HashMap<String, Object> properties = new HashMap<String, Object>();
        //properties.put("hibernate.transaction.jta.platform", AtomikosJtaPlatform.class.getName());
        //properties.put("javax.persistence.transactionType", "JTA");
        properties.put("hibernate.cache.region.factory_class", "org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory");
        properties.put("hibernate.cache.use_second_level_cache", "false");
        properties.put("hibernate.cache.use_query_cache", "false");
        properties.put("hibernate.generate_statistics", "true");

        properties.put("hibernate.tenant_identifier_resolver", "com.mycompany.myapp.tenancy.hibernate.MyCurrentTenantIdentifierResolver");
        /* MANY DATASOURCES. WORKING SOLUTION */
        //properties.put("hibernate.multi_tenant_connection_provider", "com.mycompany.myapp.tenancy.hibernate.MyMultiTenantConnectionProviderImpl");
        /*SCHEMA CONFIG THAT IS NOT WORKING*/
        properties.put("hibernate.multi_tenant_connection_provider", "com.mycompany.myapp.tenancy.hibernate.SchemaMultiTenantConnectionProviderImpl");
        properties.put("hibernate.multiTenancy", "SCHEMA");

        LocalContainerEntityManagerFactoryBean entityManager = new LocalContainerEntityManagerFactoryBean();
        entityManager.setDataSource(dataSource);
        entityManager.setJpaVendorAdapter(jpaVendorAdapter);
        //entityManager.setPackagesToScan("com.mycompany.myapp.domain");
        entityManager.setPackagesToScan(new String[] { "com.mycompany.myapp.domain","com.mycompany.myapp.tenancy.domain" });

        entityManager.setPersistenceUnitName("persistenceUnit");
        entityManager.setJpaPropertyMap(properties);
        return entityManager;
    }

    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory){
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);

        return transactionManager;
    }
}

有人知道为什么我的 Hibernate 实现可以那样工作吗？您可以在 github 上找到整个项目:

https://github.com/upway/jhipster-multi-tenancy-poc

Answer 1

替换

connection.createStatement().execute("USE " + tenantIdentifier + ";");

与

connection.setCatalog(tenantIdentifier);

代码中的主要问题是(我认为)使用 sql USE ...; 而不是使用 setCatalog() 函数作为 explained in the mysql connector documentation

我遇到了同样的问题。首先，我想到了 hibernate 问题并构建了一个 new jhipster project根据你的代码。即使使用较新的 hibernate 模式，我也重现了这个问题。

一旦我在连接上使用了 setCatalog，它就开始工作了。