Setting up a MultiTenantConnectionProvider using Hibernate 4.2 and Spring 3.1.1 -
i trying set hibernate multi tenancy using seperate schema aproach.
after working on 2 days , browsing every source find via google starting quite frustrated.
basicaly trying follow guide provided in hibernate devguide http://docs.jboss.org/hibernate/orm/4.1/devguide/en-us/html_single/#d5e4691
unfortunately not able find connectionproviderutils build connectionprovider. trying figure out 2 points:
why configure(properties props) method of mssqlmultitenantconnectionprovider never called. interpreted source of , description of different other connectionprovider implementions assuming method going called initialize connectionprovider.
since not able work configure(properties props) tried out other approaches of somehow obtaining hibernate properties , datasource specified in application context , hibernate.cfg.xml. (like injecting datasource directly connectionprovider)
any pointers possible ways solve (methods, classes, tutorials)
so here relevant parts of implementation:
data source , 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 sets hibernate properties drivermanagerconnectionproviderimpl defaultprovider = new drivermanagerconnectionproviderimpl(); defaultprovider.configure(properties); connection con = defaultprovider.getconnection(); resultset rs = con.createstatement().executequery("select * [schema].table"); rs.close(); //the statement , sql test connection return defaultprovider.getconnection(); } @override public connection getconnection(string tenantidentifier) throws sqlexception { <--not sure how 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; } } right there 2 possible approaches see obtaint configurations need config files. either configure() method run or somehow make injection of datasource possible. guess first 1 better way.
an important thing mention had hibernate , running 1 tenant (means without using multitenantconnectionprovider, using standard connectionprovider used hibernate)
already big reading post. looking forward answers.
best regards
update 1:
i have played around bit , hardcoded connectiondetails multitenantconnectionprovider (updated code above). working fine in regards multitenantconnectionprovider. still not solving problems. application fails @ initializing 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> this top of exception stacktrace:
caused by: java.lang.nullpointerexception @ org.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)
i traced issue down in debug mode , found out problem sessionfactory somehow not getting hold of datasource. (it makes no difference whether specify datasource in hibernate.cfg.xml or not) when initializing transactionmanager tries datasource sessionfactory , fails nullpointerexception result. have hint @ point of inner workings of hibernate failing? in documentation , posts have seen there no indication need handle injection of datasource sessionfactory. guess try figure out how datasource needed place or how change initializing flow. if has better idea happy.
edit: posted in hibernate forums now:
update 2:
so managed around issue setting autodetectdatasource property in transactionmanager false:
<property name="autodetectdatasource" value="false"/> i got hint following post http://forum.springsource.org/showthread.php?123478-sessionfactory-configured-for-multi-tenancy-but-no-tenant-identifier-specified. unfortunately stuck @ issue. ^^" problem topic. (edit: turns out misconfiguration earlier testing + 1 old dependency)
as topic problem remains want somehow able reuse datasource, have in configuration use of spring security anyway, hibernate avoid need having configure datasource in 2 places. question still stands how integrate use of datasource in multitenantconnectionprovider. have idea on find hints on that?
according steve ebersole's comments on jira issue referred 1 of question's commenters (hhh-8752):
well first, not true hibernate "instantiates classes referred ... multi_tenant_connection_provider , multi_tenant_identifier_resolver". hibernate first tries treat these settings objects of intended types, (multitenantconnectionprovider multi_tenant_connection_provider , currenttenantidentifierresolver multi_tenant_identifier_resolver.
so pass beans in directly, configured want.
i followed suggestion , managed make work.
this currenttenantidentifierresolver defined spring bean:
@component @scope(value = "request", proxymode = scopedproxymode.target_class) public class requesturitenantidentifierresolver implements currenttenantidentifierresolver { @autowired private httpservletrequest request; @override public string resolvecurrenttenantidentifier() { string[] pathelements = request.getrequesturi().split("/"); string tenant = pathelements[1]; return tenant; } @override public boolean validateexistingcurrentsessions() { return true; } } this multitenantconnectionprovider defined spring bean:
@component public class schemapertenantconnectionproviderimpl implements multitenantconnectionprovider { @autowired private datasource datasource; @override public connection getanyconnection() throws sqlexception { return datasource.getconnection(); } @override public void releaseanyconnection(final connection connection) throws sqlexception { connection.close(); } @override public connection getconnection(final string tenantidentifier) throws sqlexception { final connection connection = getanyconnection(); try { connection.createstatement().execute("use " + tenantidentifier); } catch (sqlexception e) { throw new hibernateexception("could not alter jdbc connection specified schema [" + tenantidentifier + "]", e); } return connection; } @override public void releaseconnection(final string tenantidentifier, final connection connection) throws sqlexception { try { connection.createstatement().execute("use dummy"); } catch (sqlexception e) { // on error, throw exception make sure connection not returned pool. // requirements may differ throw new hibernateexception( "could not alter jdbc connection specified schema [" + tenantidentifier + "]", e ); } connection.close(); } @override public boolean supportsaggressiverelease() { return true; } @override public boolean isunwrappableas(class aclass) { return false; } @override public <t> t unwrap(class<t> aclass) { return null; } } and finally, localcontainerentitymanagerfactorybean wired make use of 2 components above:
@configuration public class hibernateconfig { @bean public jpavendoradapter jpavendoradapter() { return new hibernatejpavendoradapter(); } @bean public localcontainerentitymanagerfactorybean entitymanagerfactory(datasource datasource, multitenantconnectionprovider multitenantconnectionprovider, currenttenantidentifierresolver tenantidentifierresolver) { localcontainerentitymanagerfactorybean emfbean = new localcontainerentitymanagerfactorybean(); emfbean.setdatasource(datasource); emfbean.setpackagestoscan(vistojobsapplication.class.getpackage().getname()); emfbean.setjpavendoradapter(jpavendoradapter()); map<string, object> jpaproperties = new hashmap<>(); jpaproperties.put(org.hibernate.cfg.environment.multi_tenant, multitenancystrategy.schema); jpaproperties.put(org.hibernate.cfg.environment.multi_tenant_connection_provider, multitenantconnectionprovider); jpaproperties.put(org.hibernate.cfg.environment.multi_tenant_identifier_resolver, tenantidentifierresolver); emfbean.setjpapropertymap(jpaproperties); return emfbean; } } the data source i'm using made available automatically spring boot.
i hope helps.
