> Could suggest any alternative way to support multi tenancy ?
> On Feb 25, 2:00 am, Eduardo <eduardo.macar...@gmail.com> wrote: >> I am not sure if that can be done. MapperScannerConfigurer is a bean >> that works integrated in Spring startup cycle.
I've same scenario where I need to query different databases based on what user chooses in UI. I have both prod database and dev database but one single UI app. In the UI if user choses dev then in runtime I should connect to dev database. Currently the datasource assignment is happening in MapperConfiguration there is no control what soever at runtime. Regular mybatis has support of environments but in spring integration there is no notion of environments.
> > Could suggest any alternative way to support multi tenancy ?
> > On Feb 25, 2:00 am, Eduardo <eduardo.macar...@gmail.com> wrote:
> >> I am not sure if that can be done. MapperScannerConfigurer is a bean
> >> that works integrated in Spring startup cycle.
> JdbcTemplate will take into its constructor the required Data source,
You got it.
JdbcTemplate can be constructed with a datasource but you cannot inject that datasource during during startup. You must create JdbcTemplate instances by hand, thus not using spring as a factory.
MyBatis can work in the same way. You can build SqlSessionFactories from a datasource using the Java api.
But MapperScannerConfigurer is built to run during spring startup. It is not a bean you can instantiate with a new() but a piece that spring will call during startup.
This does not apply to all applications that have more than one datasource, just to those where you have to decide with datasource you will connect to based on some other info.
So I would say that MapperScannerConfigurer does not fit. Don't know if you will be able to use other parts of MyBatis-Spring. It will be great if you could post your results if you do any investigation on this topic.
>> > Could suggest any alternative way to support multi tenancy ?
>> > On Feb 25, 2:00 am, Eduardo <eduardo.macar...@gmail.com> wrote: >> >> I am not sure if that can be done. MapperScannerConfigurer is a bean >> >> that works integrated in Spring startup cycle.
I am afraid that both Spring environments and MyBatis environments are resolved during startup.
You can inject different datasources in Spring using new Spring 3.1 environments. I have not used them yet but my understanding is that profiles cannot be activated after startup.
The same happens with MyBatis environments. An SqlSessionFactory can just use one and the environment is selected during startup.
But, as said before, you can always create an SqlSessionFactory out of a datasource at any time.
Anyway, there is always room for inprovement so comments, ideas (and mostly patches :) ) are always appreciated!
> I've same scenario where I need to query different databases based on what > user chooses in UI. I have both prod database and dev database but one > single UI app. In the UI if user choses dev then in runtime I should > connect to dev database. Currently the datasource assignment is happening > in MapperConfiguration there is no control what soever at runtime. Regular > mybatis has support of environments but in spring integration there is no > notion of environments.
Is there any way we can configure multiple mapperscannerconfigurer each with different sqlsessionfactory? That might solve the problem. I tried to configure multiple scannaer in spring but couldn't find any difference.
> Is there any way we can configure multiple mapperscannerconfigurer each with > different sqlsessionfactory? That might solve the problem. I tried to > configure multiple scannaer in spring but couldn't find any difference.
I don't think this will work. As MapperScannerConfigurer if it finds the bean with the same name then it probably will not register another bean. So configuring multiple scanners with different session factories will not solve the problem.
> I don't think this will work. As MapperScannerConfigurer if it finds the
> bean with the same name then it probably will not register another bean.
> So configuring multiple scanners with different session factories will not
> solve the problem.
On Mon, Feb 27, 2012 at 1:20 PM, Kesav Kumar Kolla <kesavko...@gmail.com>wrote:
> Is there any way we can configure multiple mapperscannerconfigurer each > with different sqlsessionfactory? That might solve the problem. I tried > to configure multiple scannaer in spring but couldn't find any difference.
Using this approach you can keep all your mappers in one package and just annotate them at the top - this seems wasteful though since I bet each MapperScannerConfigurer will then have to scan over the entire package each time (assuming you put them all in the same package.)
It seems best to just take the first approach I've shown and as long as you organize your domain by the db you'd be ok.
I thought the OPs issue was dealing with it at runtime though? That's an issue I've never had to tackle. It's easy as shown above to use multiple DBs within the app - but configuring a single Mapper to be used against a different DB at run time - no clue there.
> On Mon, Feb 27, 2012 at 1:20 PM, Kesav Kumar Kolla <kesavko...@gmail.com>wrote:
> > Is there any way we can configure multiple mapperscannerconfigurer each
> > with different sqlsessionfactory? That might solve the problem. I tried
> > to configure multiple scannaer in spring but couldn't find any difference.
> Yes this is easy and it's what I'm doing now ...
> Using this approach you can keep all your mappers in one package and just
> annotate them at the top - this seems wasteful though since I bet each
> MapperScannerConfigurer will then have to scan over the entire package each
> time (assuming you put them all in the same package.)
> It seems best to just take the first approach I've shown and as long as you
> organize your domain by the db you'd be ok.
> I thought the OPs issue was dealing with it at runtime though? That's an
> issue I've never had to tackle. It's easy as shown above to use multiple
> DBs within the app - but configuring a single Mapper to be used against a
> different DB at run time - no clue there.
You may do that quite easily implementing your own datasource that wraps the real datasources and switches between them base on a ThreadLocal variable.
Something like
public class MultiTenantDataSource extends DataSource {
private Map<DataSource> delegates;
public Connection getConnection() throws SQLException { final String tenantId = TenantContext.getTenantId() DataSource tenantDataSource = delegates.get(tenantId); return tenantDataSource.getConnection(); }
}
Note that this must be done at Spring level, not at MyBatis level. I mean, both spring & mybatis must see the same datasource (in this case an instance of MultiTenantDataSource).
Be careful with transactions, make sure you do not mix two datasources in the same transaction.
> Hibernate 4 goes a step further and provides a explicit API for the > same, would like to know your thoughts on the above ?
> Similar to Hibernate , It would be nice if Mybatis did not have to > create multiple sessionfactories, as the schema for all the DB's are > the same.
> On Feb 28, 2:43 am, Rick R <ric...@gmail.com> wrote: >> On Mon, Feb 27, 2012 at 1:20 PM, Kesav Kumar Kolla <kesavko...@gmail.com>wrote:
>> > Is there any way we can configure multiple mapperscannerconfigurer each >> > with different sqlsessionfactory? That might solve the problem. I tried >> > to configure multiple scannaer in spring but couldn't find any difference.
>> Yes this is easy and it's what I'm doing now ...
>> Using this approach you can keep all your mappers in one package and just >> annotate them at the top - this seems wasteful though since I bet each >> MapperScannerConfigurer will then have to scan over the entire package each >> time (assuming you put them all in the same package.)
>> It seems best to just take the first approach I've shown and as long as you >> organize your domain by the db you'd be ok.
>> I thought the OPs issue was dealing with it at runtime though? That's an >> issue I've never had to tackle. It's easy as shown above to use multiple >> DBs within the app - but configuring a single Mapper to be used against a >> different DB at run time - no clue there.
On Tuesday, February 28, 2012, Eduardo Macarron wrote: > Well, that is not exactly buiit in support :)
> You may do that quite easily implementing your own datasource that > wraps the real datasources and switches between them base on a > ThreadLocal variable.
> Something like
> public class MultiTenantDataSource extends DataSource {
> Note that this must be done at Spring level, not at MyBatis level. I > mean, both spring & mybatis must see the same datasource (in this case > an instance of MultiTenantDataSource).
> Be careful with transactions, make sure you do not mix two datasources > in the same transaction.
> 2012/2/28 sudarshan <sudarsha...@gmail.com <javascript:;>>: > > Thanks for the replies :)
> > My issue is mainly with Multi Tenancy, when a tenant logs in, i need > > to be able to redirect him to a DB specific to him.
> > Hibernate provides a mechanism for this, more details can be seen here
> > Hibernate 4 goes a step further and provides a explicit API for the > > same, would like to know your thoughts on the above ?
> > Similar to Hibernate , It would be nice if Mybatis did not have to > > create multiple sessionfactories, as the schema for all the DB's are > > the same.
> > On Feb 28, 2:43 am, Rick R <ric...@gmail.com <javascript:;>> wrote: > >> On Mon, Feb 27, 2012 at 1:20 PM, Kesav Kumar Kolla < > kesavko...@gmail.com <javascript:;>>wrote:
> >> > Is there any way we can configure multiple mapperscannerconfigurer > each > >> > with different sqlsessionfactory? That might solve the problem. I > tried > >> > to configure multiple scannaer in spring but couldn't find any > difference.
> >> Yes this is easy and it's what I'm doing now ...
> >> As long as you organize by package you'll be fine. You could also > register > >> an annotation which is how I did it in the past... > >> for example
> >> Using this approach you can keep all your mappers in one package and > just > >> annotate them at the top - this seems wasteful though since I bet each > >> MapperScannerConfigurer will then have to scan over the entire package > each > >> time (assuming you put them all in the same package.)
> >> It seems best to just take the first approach I've shown and as long as > you > >> organize your domain by the db you'd be ok.
> >> I thought the OPs issue was dealing with it at runtime though? That's an > >> issue I've never had to tackle. It's easy as shown above to use multiple > >> DBs within the app - but configuring a single Mapper to be used against > a > >> different DB at run time - no clue there.
"Be careful with transactions, make sure you do not mix two
datasources
in the same transaction. "
I am not a 100% sure, however I think i am doing exactly what you
outlined above too :)
I use Hibernate for all my writes and Mybatis for all my reads.
Within a transaction, if there is a write (hibernate) and then read
(mybatis) , Mybatis does not seem to be able to read the stuff written
by hibernate in that transaction
However once the transaction in committed it can do the read.
"Be careful with transactions, make sure you do not mix two
datasources
in the same transaction. "
Can you elaborate a little more on the consequences ?, does it have
anything to do with the "TransactionSynchronizationManager" ?
Any tips on how to get around it ...
On Feb 28, 12:41 pm, Eduardo Macarron <eduardo.macar...@gmail.com>
wrote:
> You may do that quite easily implementing your own datasource that
> wraps the real datasources and switches between them base on a
> ThreadLocal variable.
> Something like
> public class MultiTenantDataSource extends DataSource {
> Note that this must be done at Spring level, not at MyBatis level. I
> mean, both spring & mybatis must see the same datasource (in this case
> an instance of MultiTenantDataSource).
> Be careful with transactions, make sure you do not mix two datasources
> in the same transaction.
> > Hibernate 4 goes a step further and provides a explicit API for the
> > same, would like to know your thoughts on the above ?
> > Similar to Hibernate , It would be nice if Mybatis did not have to
> > create multiple sessionfactories, as the schema for all the DB's are
> > the same.
> > On Feb 28, 2:43 am, Rick R <ric...@gmail.com> wrote:
> >> On Mon, Feb 27, 2012 at 1:20 PM, Kesav Kumar Kolla <kesavko...@gmail.com>wrote:
> >> > Is there any way we can configure multiple mapperscannerconfigurer each
> >> > with different sqlsessionfactory? That might solve the problem. I tried
> >> > to configure multiple scannaer in spring but couldn't find any difference.
> >> Yes this is easy and it's what I'm doing now ...
> >> As long as you organize by package you'll be fine. You could also register
> >> an annotation which is how I did it in the past...
> >> for example
> >> Using this approach you can keep all your mappers in one package and just
> >> annotate them at the top - this seems wasteful though since I bet each
> >> MapperScannerConfigurer will then have to scan over the entire package each
> >> time (assuming you put them all in the same package.)
> >> It seems best to just take the first approach I've shown and as long as you
> >> organize your domain by the db you'd be ok.
> >> I thought the OPs issue was dealing with it at runtime though? That's an
> >> issue I've never had to tackle. It's easy as shown above to use multiple
> >> DBs within the app - but configuring a single Mapper to be used against a
> >> different DB at run time - no clue there.
Each time a transaction is created the connection and the mybatis session are stored in springs transaction manager.
If you are using database1 to read but switch to database2 when committing, neither spring nor mybatis will note that, but you will have inconsistences and connection leaks.
This gets far far more complex if you are mixing two technologies in the same transaction (hibernate and mybatis). It may work, but honestly, I have no idea! You will have to try it.
If it does not work. The first workaround is to take MyBatis reads out of transactions (no @Transaction annotations). But note that this may cause db inconsistences because you will be losing read locks.
The second is taking a decission... all in MyBatis or all in Hibernate.
I not that much of a adrenaline junkie :) I guess i put up my requirement in a confusing fashion.
The talk about multiple databases is only for Multi Tenancy,so when a user logs in based on the login info I should be able to point to a particular database.
Now once I have pointed to the correct database I use Hibernate to do all writes and Mybatis to do all reads.
I basically do not use Hibernate API's and the write side domain model to read of the DB.
Both writes and reads will happen on same DB, Just that the DB should change based on Login
On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
> Sudarshan, you like living on the edge!! ;)
> Each time a transaction is created the connection and the mybatis > session are stored in springs transaction manager.
> If you are using database1 to read but switch to database2 when > committing, neither spring nor mybatis will note that, but you will > have inconsistences and connection leaks.
> This gets far far more complex if you are mixing two technologies in > the same transaction (hibernate and mybatis). It may work, but > honestly, I have no idea! You will have to try it.
> If it does not work. The first workaround is to take MyBatis reads out > of transactions (no @Transaction annotations). But note that this may > cause db inconsistences because you will be losing read locks.
> The second is taking a decission... all in MyBatis or all in Hibernate.
On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
> Sudarshan, you like living on the edge!! ;)
> Each time a transaction is created the connection and the mybatis > session are stored in springs transaction manager.
> If you are using database1 to read but switch to database2 when > committing, neither spring nor mybatis will note that, but you will > have inconsistences and connection leaks.
> This gets far far more complex if you are mixing two technologies in > the same transaction (hibernate and mybatis). It may work, but > honestly, I have no idea! You will have to try it.
> If it does not work. The first workaround is to take MyBatis reads out > of transactions (no @Transaction annotations). But note that this may > cause db inconsistences because you will be losing read locks.
> The second is taking a decission... all in MyBatis or all in Hibernate.
On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
> Sudarshan, you like living on the edge!! ;)
> Each time a transaction is created the connection and the mybatis > session are stored in springs transaction manager.
> If you are using database1 to read but switch to database2 when > committing, neither spring nor mybatis will note that, but you will > have inconsistences and connection leaks.
> This gets far far more complex if you are mixing two technologies in > the same transaction (hibernate and mybatis). It may work, but > honestly, I have no idea! You will have to try it.
> If it does not work. The first workaround is to take MyBatis reads out > of transactions (no @Transaction annotations). But note that this may > cause db inconsistences because you will be losing read locks.
> The second is taking a decission... all in MyBatis or all in Hibernate.
On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
> Sudarshan, you like living on the edge!! ;)
> Each time a transaction is created the connection and the mybatis > session are stored in springs transaction manager.
> If you are using database1 to read but switch to database2 when > committing, neither spring nor mybatis will note that, but you will > have inconsistences and connection leaks.
> This gets far far more complex if you are mixing two technologies in > the same transaction (hibernate and mybatis). It may work, but > honestly, I have no idea! You will have to try it.
> If it does not work. The first workaround is to take MyBatis reads out > of transactions (no @Transaction annotations). But note that this may > cause db inconsistences because you will be losing read locks.
> The second is taking a decission... all in MyBatis or all in Hibernate.
On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
> Sudarshan, you like living on the edge!! ;)
> Each time a transaction is created the connection and the mybatis > session are stored in springs transaction manager.
> If you are using database1 to read but switch to database2 when > committing, neither spring nor mybatis will note that, but you will > have inconsistences and connection leaks.
> This gets far far more complex if you are mixing two technologies in > the same transaction (hibernate and mybatis). It may work, but > honestly, I have no idea! You will have to try it.
> If it does not work. The first workaround is to take MyBatis reads out > of transactions (no @Transaction annotations). But note that this may > cause db inconsistences because you will be losing read locks.
> The second is taking a decission... all in MyBatis or all in Hibernate.
On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
> Sudarshan, you like living on the edge!! ;)
> Each time a transaction is created the connection and the mybatis > session are stored in springs transaction manager.
> If you are using database1 to read but switch to database2 when > committing, neither spring nor mybatis will note that, but you will > have inconsistences and connection leaks.
> This gets far far more complex if you are mixing two technologies in > the same transaction (hibernate and mybatis). It may work, but > honestly, I have no idea! You will have to try it.
> If it does not work. The first workaround is to take MyBatis reads out > of transactions (no @Transaction annotations). But note that this may > cause db inconsistences because you will be losing read locks.
> The second is taking a decission... all in MyBatis or all in Hibernate.
On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
> Sudarshan, you like living on the edge!! ;)
> Each time a transaction is created the connection and the mybatis > session are stored in springs transaction manager.
> If you are using database1 to read but switch to database2 when > committing, neither spring nor mybatis will note that, but you will > have inconsistences and connection leaks.
> This gets far far more complex if you are mixing two technologies in > the same transaction (hibernate and mybatis). It may work, but > honestly, I have no idea! You will have to try it.
> If it does not work. The first workaround is to take MyBatis reads out > of transactions (no @Transaction annotations). But note that this may > cause db inconsistences because you will be losing read locks.
> The second is taking a decission... all in MyBatis or all in Hibernate.
On Wednesday, 29 February 2012 12:33:04 UTC+5:30, sudarshan wrote:
> I not that much of a adrenaline junkie :) I guess i put up my requirement > in a confusing fashion.
> The talk about multiple databases is only for Multi Tenancy,so when a user > logs in based on the login info I should be able to point to a particular > database.
> Now once I have pointed to the correct database I use Hibernate to do all > writes and Mybatis to do all reads.
> I basically do not use Hibernate API's and the write side domain model to > read of the DB.
> Both writes and reads will happen on same DB, Just that the DB should > change based on Login
> On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
>> Sudarshan, you like living on the edge!! ;)
>> Each time a transaction is created the connection and the mybatis >> session are stored in springs transaction manager.
>> If you are using database1 to read but switch to database2 when >> committing, neither spring nor mybatis will note that, but you will >> have inconsistences and connection leaks.
>> This gets far far more complex if you are mixing two technologies in >> the same transaction (hibernate and mybatis). It may work, but >> honestly, I have no idea! You will have to try it.
>> If it does not work. The first workaround is to take MyBatis reads out >> of transactions (no @Transaction annotations). But note that this may >> cause db inconsistences because you will be losing read locks.
>> The second is taking a decission... all in MyBatis or all in Hibernate.
> On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
>> Sudarshan, you like living on the edge!! ;)
>> Each time a transaction is created the connection and the mybatis >> session are stored in springs transaction manager.
>> If you are using database1 to read but switch to database2 when >> committing, neither spring nor mybatis will note that, but you will >> have inconsistences and connection leaks.
>> This gets far far more complex if you are mixing two technologies in >> the same transaction (hibernate and mybatis). It may work, but >> honestly, I have no idea! You will have to try it.
>> If it does not work. The first workaround is to take MyBatis reads out >> of transactions (no @Transaction annotations). But note that this may >> cause db inconsistences because you will be losing read locks.
>> The second is taking a decission... all in MyBatis or all in Hibernate.
> On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
>> Sudarshan, you like living on the edge!! ;)
>> Each time a transaction is created the connection and the mybatis >> session are stored in springs transaction manager.
>> If you are using database1 to read but switch to database2 when >> committing, neither spring nor mybatis will note that, but you will >> have inconsistences and connection leaks.
>> This gets far far more complex if you are mixing two technologies in >> the same transaction (hibernate and mybatis). It may work, but >> honestly, I have no idea! You will have to try it.
>> If it does not work. The first workaround is to take MyBatis reads out >> of transactions (no @Transaction annotations). But note that this may >> cause db inconsistences because you will be losing read locks.
>> The second is taking a decission... all in MyBatis or all in Hibernate.
> On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
>> Sudarshan, you like living on the edge!! ;)
>> Each time a transaction is created the connection and the mybatis >> session are stored in springs transaction manager.
>> If you are using database1 to read but switch to database2 when >> committing, neither spring nor mybatis will note that, but you will >> have inconsistences and connection leaks.
>> This gets far far more complex if you are mixing two technologies in >> the same transaction (hibernate and mybatis). It may work, but >> honestly, I have no idea! You will have to try it.
>> If it does not work. The first workaround is to take MyBatis reads out >> of transactions (no @Transaction annotations). But note that this may >> cause db inconsistences because you will be losing read locks.
>> The second is taking a decission... all in MyBatis or all in Hibernate.
> On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
>> Sudarshan, you like living on the edge!! ;)
>> Each time a transaction is created the connection and the mybatis >> session are stored in springs transaction manager.
>> If you are using database1 to read but switch to database2 when >> committing, neither spring nor mybatis will note that, but you will >> have inconsistences and connection leaks.
>> This gets far far more complex if you are mixing two technologies in >> the same transaction (hibernate and mybatis). It may work, but >> honestly, I have no idea! You will have to try it.
>> If it does not work. The first workaround is to take MyBatis reads out >> of transactions (no @Transaction annotations). But note that this may >> cause db inconsistences because you will be losing read locks.
>> The second is taking a decission... all in MyBatis or all in Hibernate.
> On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
>> Sudarshan, you like living on the edge!! ;)
>> Each time a transaction is created the connection and the mybatis >> session are stored in springs transaction manager.
>> If you are using database1 to read but switch to database2 when >> committing, neither spring nor mybatis will note that, but you will >> have inconsistences and connection leaks.
>> This gets far far more complex if you are mixing two technologies in >> the same transaction (hibernate and mybatis). It may work, but >> honestly, I have no idea! You will have to try it.
>> If it does not work. The first workaround is to take MyBatis reads out >> of transactions (no @Transaction annotations). But note that this may >> cause db inconsistences because you will be losing read locks.
>> The second is taking a decission... all in MyBatis or all in Hibernate.
> On Wednesday, 29 February 2012 11:29:22 UTC+5:30, Eduardo wrote:
>> Sudarshan, you like living on the edge!! ;)
>> Each time a transaction is created the connection and the mybatis >> session are stored in springs transaction manager.
>> If you are using database1 to read but switch to database2 when >> committing, neither spring nor mybatis will note that, but you will >> have inconsistences and connection leaks.
>> This gets far far more complex if you are mixing two technologies in >> the same transaction (hibernate and mybatis). It may work, but >> honestly, I have no idea! You will have to try it.
>> If it does not work. The first workaround is to take MyBatis reads out >> of transactions (no @Transaction annotations). But note that this may >> cause db inconsistences because you will be losing read locks.
>> The second is taking a decission... all in MyBatis or all in Hibernate.
Yup this solved my issue of connecting to different datasource at runtime. I implemented a threadlocal based routing datasource and that solved my problem. Thanks for pointing this out.
> Yup this solved my issue of connecting to different datasource at runtime. > I implemented a threadlocal based routing datasource and that solved my > problem.
> Thanks for pointing this out.
I ran into the spring initialization problem because of the Autowired final methods in SqlSessionDaoSupport (setSqlSessionFactory and setSqlSessionTemplate)
It's beyond me why these are final. I ended up re-building the spring-mybatis jar and modified SqlSessionDaoSupport so those aren't final so I can override them in my Mapper subclass. They're still Autowired but now I can specify qualifiers so it can find just the one I want.
