使用 Hibernate 4.2 和 Spring 3.1.1 设置 MultiTenantConnectionProvider [英] Setting up a MultiTenantConnectionProvider using Hibernate 4.2 and Spring 3.1.1
问题描述
我目前正在尝试使用单独的架构方法为多租户设置 Hibernate.
在研究了大约 2 天并浏览了几乎所有可以通过 Google 找到的来源之后，我开始感到非常沮丧.
I am currently trying to set up Hibernate for multi tenancy using the seperate Schema aproach.
After working on it for about 2 days now and browsing nearly every source I could find via Google I am starting to get quite frustrated.
基本上我正在尝试遵循 Hibernate devguide 中提供的指南 http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html_single/#d5e4691
但不幸的是，我无法找到 ConnectionProviderUtils 来构建 ConnectionProvider.目前我想弄清楚 2 点:
Basicaly I am trying to follow the guide provided in the Hibernate devguide http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html_single/#d5e4691
But unfortunately I am not able to find the ConnectionProviderUtils to build the ConnectionProvider.
Currently I am trying to figure out 2 Points:
为什么我的 MSSQLMultiTenantConnectionProvider 的 configure(Properties props) 方法从未被调用.根据我从其他不同 ConnectionProvider 实现的来源和描述中所解释的内容，我假设将调用此方法来初始化 ConnectionProvider.
Why the configure(Properties props) method of my MSSQLMultiTenantConnectionProvider is never called. From what I interpreted from the source of and description of different other ConnectionProvider implementions I am assuming this method is going to be called to initialize the ConnectionProvider.
由于我无法使用 configure(Properties props)，我尝试了其他方法以某种方式获取应用程序上下文和 hibernate.cfg.xml 中指定的休眠属性和数据源.(就像将数据源直接注入ConnectionProvider一样)
Since I am not able to work with the configure(Properties props) I tried out other approaches of somehow obtaining the hibernate properties and DataSource specified in the application Context and the hibernate.cfg.xml. (Like injecting the datasource directly into the ConnectionProvider)
任何指向解决此问题的可能方法的指针(方法、类、教程)
Any pointers to possible ways to solve this (Methods, Classes, Tutorials)
这里是我实现的相关部分:
数据源和 Hibernate.cfg.xml:
So here are the relevant parts of my implementation:
Data Source and Hibernate.cfg.xml:
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
<property name="url" value="jdbc:sqlserver://<host>:<port>;databaseName=<DbName>;" />
<property name="username" value=<username> />
<property name="password" value=<password> />
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<!-- property name="dataSource" ref="dataSource" /-->
<property name="annotatedClasses">
<list>
<value>c.h.utils.hibernate.User</value>
<value>c.h.utils.hibernate.Role</value>
<value>c.h.utils.hibernate.Tenant</value>
</list>
</property>
<property name="hibernateProperties">
<value>
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.show_sql=true
hibernate.multiTenancy=SCHEMA
hibernate.tenant_identifier_resolver=c.h.utils.hibernate.CurrentTenantIdentifierResolver
hibernate.multi_tenant_connection_provider=c.h.utils.hibernate.MSSQLMultiTenantConnectionProviderImpl
</value>
</property>
</bean>
MSSQLMultiTenantConnectionProviderImpl:
package c.hoell.utils.hibernate;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.hibernate.service.UnknownUnwrapTypeException;
import org.hibernate.service.jdbc.connections.spi.ConnectionProvider;
import org.hibernate.service.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class MSSQLMultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider {
private static final long serialVersionUID = 8074002161278796379L;
@Autowired
private DataSource dataSource;
public void configure(Properties props) throws HibernateException {
}
@Override
public Connection getAnyConnection() throws SQLException {
Properties properties = getConnectionProperties(); //method which sets the hibernate properties
DriverManagerConnectionProviderImpl defaultProvider = new DriverManagerConnectionProviderImpl();
defaultProvider.configure(properties);
Connection con = defaultProvider.getConnection();
ResultSet rs = con.createStatement().executeQuery("SELECT * FROM [schema].table");
rs.close(); //the statement and sql is just to test the connection
return defaultProvider.getConnection();
}
@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
<--not sure how to implement this-->
}
@Override
public void releaseAnyConnection(Connection connection) throws SQLException {
connection.close();
}
@Override
public void releaseConnection(String tenantIdentifier, Connection connection){
try {
this.releaseAnyConnection(connection);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public boolean supportsAggressiveRelease() {
return false;
}
@Override
public boolean isUnwrappableAs(Class unwrapType) {
return ConnectionProvider.class.equals( unwrapType ) || MultiTenantConnectionProvider.class.equals( unwrapType ) || MSSQLMultiTenantConnectionProviderImpl.class.isAssignableFrom( unwrapType );
}
@SuppressWarnings("unchecked")
@Override
public <T> T unwrap(Class<T> unwrapType) {
if ( isUnwrappableAs( unwrapType ) ) {
return (T) this;
}
else {
throw new UnknownUnwrapTypeException( unwrapType );
}
}
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
}
现在我看到有两种可能的方法可以从配置文件中获取我需要的配置.要么让 configure() 方法运行，要么以某种方式使数据源的注入成为可能.我想第一个会更好.
Right now there are 2 possible approaches I see to obtaint the configurations i need from the config files. Either get the configure() method to run or somehow make the injection of the DataSource possible. I guess the first one would be the better way.
一个重要的事情是我只为一个租户启动并运行了 Hibernate(意味着不使用 MultiTenantConnectionProvider，使用 Hibernate 使用的标准 ConnectionProvider)
An important thing to mention is that I had Hibernate up and running for only one tenant (means without using the MultiTenantConnectionProvider, using the standard ConnectionProvider used by Hibernate)
非常感谢阅读这篇文章的任何人.期待答案.
Already a big thanks to anyone who is reading this post. Looking forward to the answers.
最好的问候
我对此进行了一些尝试，并将连接详细信息硬编码到我的 MultiTenantConnectionProvider 中(更新了上面的代码).这在 MultiTenantConnectionProvider 方面工作正常.但这仍然不能解决我的问题.现在我的应用程序无法初始化事务管理器:
I have played around with this a bit and hardcoded the connectiondetails into my MultiTenantConnectionProvider (updated the Code above). This is working fine in regards to the MultiTenantConnectionProvider. But this is still not solving my problems. Now my Application fails at initializing the Transaction Manager:
<tx:annotation-driven transaction-manager="txManager" proxy-target-class="true"/>
<bean id="txManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
这是异常堆栈跟踪的顶部:
Caused by: java.lang.NullPointerException atorg.springframework.orm.hibernate4.SessionFactoryUtils.getDataSource(SessionFactoryUtils.java:101)在org.springframework.orm.hibernate4.HibernateTransactionManager.afterPropertiesSet(HibernateTransactionManager.java:264)在org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1514)在org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1452)
Caused by: java.lang.NullPointerException at org.springframework.orm.hibernate4.SessionFactoryUtils.getDataSource(SessionFactoryUtils.java:101) at org.springframework.orm.hibernate4.HibernateTransactionManager.afterPropertiesSet(HibernateTransactionManager.java:264) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1514) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1452)
我在调试模式下追踪了这个问题，发现问题是我的 SessionFactory 以某种方式没有获取数据源.(我是否在 hibernate.cfg.xml 中指定 DataSource 没有区别)但是在初始化 TransactionManager 时，它尝试从 SessionFactory 获取数据源并因此失败并返回 NullPointerException.有没有人暗示 hibernate 内部工作的哪一点失败了?在我看到的所有文档和帖子中，没有迹象表明我需要处理将 DataSource 注入到 SessionFactory 中.现在我只是想我想弄清楚如何将 DataSource 放入所需的位置或如何更改初始化流程.如果有人有更好的主意，我会很高兴.
I traced this issue down in debug mode and found out that the problem is that my SessionFactory is somehow not getting hold of the DataSource. (It makes no difference whether I specify the DataSource in the hibernate.cfg.xml or not) But when initializing the TransactionManager it tries to get the DataSource from the SessionFactory and fails with a NullPointerException as a result. Does anyone have an hint at what point of the inner workings of hibernate this is failing? In all the documentation and posts I have seen there was no indication that I need to handle the injection of the DataSource into the SessionFactory. For now I just guess I try to figure out how to get a DataSource into the needed place or how to change the initializing flow. If anyone has a better idea I would be really happy.
现在也在 Hibernate 论坛中发布了这个:
Also posted this in the Hibernate Forums now:
所以我设法通过将 TransactionManager 中的 autodetectDataSource 属性设置为 false 来解决这个问题:
So I managed to get around this issue by setting the autodetectDataSource property in the TransactionManager to false:
<property name="autodetectDataSource" value="false"/>
我从以下帖子中得到了这个提示 http://forum.springsource.org/showthread.php?123478-SessionFactory-configured-for-multi-tenancy-but-no-tenant-identifier-specified.不幸的是，我现在被困在这个问题上.^^" 但这是另一个主题的问题.(原来这只是早期测试的错误配置 + 一个旧的依赖项)
I got this hint from the following post http://forum.springsource.org/showthread.php?123478-SessionFactory-configured-for-multi-tenancy-but-no-tenant-identifier-specified. Unfortunately I am now stuck at exactly that issue. ^^" But this is a problem for another topic. ( Turns out this was only misconfiguration from earlier testing + one old dependency)
至于这个主题，问题仍然是我希望能够以某种方式重用数据源，无论如何我已经在使用 Spring Security 的配置中为 Hibernate 避免需要配置数据源两个地方.所以问题仍然是如何在我的 MultiTenantConnectionProvider 中集成 DataSource 的使用.有没有人知道在哪里可以找到任何提示?
As for this topic the problem remains that I want to somehow be able to reuse the DataSource, which I already have in the configuration for the use of Spring Security anyway, for Hibernate to avoid the need for having to configure the DataSource in two places. So the question still stands how to integrate the use of the DataSource in my MultiTenantConnectionProvider. Does anyone have an idea on where to find any hints on that?
推荐答案
好的，总结一下，这是我最后得到的结果.我使用了一个简单的 CurrentTenantIdentifierResolver.我没有尝试从其他地方将 DataSource 注入我的 MultiTenantConnectionProviderImpl，而是在 ConnectionProvider 中创建了 DataSource (c3p0 ComboPooledDatasource) 并开始仅使用我的 ConnectionProvider 提供的连接.所以我消除了额外的数据源.为了使数据源的属性易于配置，我选择从属性文件中获取配置数据.
Ok to wrap this up, here is what I ended up with the following. I use a simple CurrentTenantIdentifierResolver. And Instead of trying to inject the DataSource from somewhere else to my MultiTenantConnectionProviderImpl I create the DataSource (c3p0 ComboPooledDatasource) in the ConnectionProvider and started using only the connections provided by the my ConnectionProvider. So I eliminated the extra DataSource. To make the properties of the DataSource easily configurable I opted to get the configuration data from a properties file.
CurrentTenantIdentifierResolverImpl:
public class CurrentTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver {
/**
* The method returns the RequestServerName as tenantidentifier.
* If no FacesContext is available null is returned.
*
* @return String tenantIdentifier
*/
@Override
public String resolveCurrentTenantIdentifier() {
if (FacesContext.getCurrentInstance() != null){
return FacesContext.getCurrentInstance().getExternalContext().getRequestServerName();
} else {
return null;
}
}
@Override
public boolean validateExistingCurrentSessions() {
return true;
}
}
MultiTenantConnectionProviderImpl:
请注意，PropertyUtil 只是一个简单的本地帮助程序类，用于获取我的属性.由于没有什么特别的，我不会包括它以免混淆答案.
Note that the PropertyUtil is just a simple local helper class to fetch my properties. Since it is nothing special I won't include it to not clutter the answer.
public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider {
private static final long serialVersionUID = 8074002161278796379L;
private static Logger log = LoggerFactory.getLogger(MultiTenantConnectionProviderImpl.class );
private ComboPooledDataSource cpds;
private Properties properties;
/**
*
* Constructor. Initializes the ComboPooledDataSource based on the config.properties.
*
* @throws PropertyVetoException
*/
public MultiTenantConnectionProviderImpl() throws PropertyVetoException {
log.info("Initializing Connection Pool!");
properties = new Properties();
try {
properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("config.properties"));
} catch (IOException e) {
throw new RuntimeException(e);
}
cpds = new ComboPooledDataSource("Example");
cpds.setDriverClass(properties.getProperty("jdbc.driver"));
cpds.setJdbcUrl(properties.getProperty("jdbc.url"));
cpds.setUser(properties.getProperty("jdbc.user"));
cpds.setPassword(PropertyUtil.getCredential("jdbc.password"));
log.info("Connection Pool initialised!");
}
@Override
public Connection getAnyConnection() throws SQLException {
log.debug("Get Default Connection:::Number of connections (max: busy - idle): {} : {} - {}",new int[]{cpds.getMaxPoolSize(),cpds.getNumBusyConnectionsAllUsers(),cpds.getNumIdleConnectionsAllUsers()});
if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize()){
log.warn("Maximum number of connections opened");
}
if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize() && cpds.getNumIdleConnectionsAllUsers()==0){
log.error("Connection pool empty!");
}
return cpds.getConnection();
}
@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
log.debug("Get {} Connection:::Number of connections (max: busy - idle): {} : {} - {}",new Object[]{tenantIdentifier, cpds.getMaxPoolSize(),cpds.getNumBusyConnectionsAllUsers(),cpds.getNumIdleConnectionsAllUsers()});
if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize()){
log.warn("Maximum number of connections opened");
}
if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize() && cpds.getNumIdleConnectionsAllUsers()==0){
log.error("Connection pool empty!");
}
return cpds.getConnection(tenantIdentifier, PropertyUtil.getCredential(tenantIdentifier));
}
@Override
public void releaseAnyConnection(Connection connection) throws SQLException {
connection.close();
}
@Override
public void releaseConnection(String tenantIdentifier, Connection connection){
try {
this.releaseAnyConnection(connection);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public boolean supportsAggressiveRelease() {
return false;
}
@SuppressWarnings("rawtypes")
@Override
public boolean isUnwrappableAs(Class unwrapType) {
return ConnectionProvider.class.equals( unwrapType ) || MultiTenantConnectionProvider.class.equals( unwrapType ) || MultiTenantConnectionProviderImpl.class.isAssignableFrom( unwrapType );
}
@SuppressWarnings("unchecked")
@Override
public <T> T unwrap(Class<T> unwrapType) {
if ( isUnwrappableAs( unwrapType ) ) {
return (T) this;
}
else {
throw new UnknownUnwrapTypeException( unwrapType );
}
}
}
c3p0 特定配置取自 c3p0-config.xml:
The c3p0 specific config is taken from the c3p0-config.xml:
<c3p0-config>
<named-config name="Example">
<property name="acquireIncrement">3</property>
<property name="preferredTestQuery">SELECT 1</property>
<property name="checkoutTimeout">2000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">1</property>
<property name="maxIdleTime">18000</property>
<property name="maxPoolSize">30</property>
<property name="minPoolSize">1</property>
<property name="maxStatements">50</property>
<property name="testConnectionOnCheckin">true</property>
</named-config>
</c3p0-config>
并且数据库特定属性由 config.properties 文件提供:
And the db specific properties are provided by a config.properties file:
jdbc.url=<serverUrl>
jdbc.driver=<driverClass>
jdbc.dbName=<dBname>
jdbc.dbowner=<dbo>
jdbc.username=<user>
jdbc.password=<password>
hibernate.dialect=<hibernateDialect>
hibernate.debug=false
以类似方式从另一个文件中获取凭据.
The credentials are fetched in a similar fashion from another file.
感谢任何提供改进的反馈.
Any feedback providing improvements is appreciated.
这篇关于使用 Hibernate 4.2 和 Spring 3.1.1 设置 MultiTenantConnectionProvider的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
