MyBatis spring in a multi tenant application

[sudarshan]

Hi needed some help in using MyBatis spring in a multi tenant
application ...

Is it possible ? Especially since i do not see how
"MapperScannerConfigurer" can be configured with sqlSessionFactory at
run time.

Thanks
Sudarshan

[Eduardo]

I am not sure if that can be done. MapperScannerConfigurer is a bean
that works integrated in Spring startup cycle.

[sudarshan]

Could suggest any alternative way to support multi tenancy ?

[Eduardo Macarron]

How would you do it if using JdbcTemplate?

2012/2/25 sudarshan <sudar...@gmail.com>:

[Kesav Kumar Kolla]

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.

-kesav

[sudarshan]

JdbcTemplate will take into its constructor the required Data source,

I am guessing SqlSessionFactoryBean is also capable of taking in the
required Data source.

However what about MapperScannerConfigurer, it does its magic at
spring startup ...

How does one get around this ?

Should i ditch spring-mybatis and move onto just mybatis ??


On Feb 25, 10:53 pm, Eduardo Macarron <eduardo.macar...@gmail.com>
wrote:

> How would you do it if using JdbcTemplate?
>

> 2012/2/25 sudarshan <sudarsha...@gmail.com>:

[Eduardo Macarron]

> 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.

2012/2/26 sudarshan <sudar...@gmail.com>:

[Eduardo Macarron]

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!

2012/2/25 Kesav Kumar Kolla <kesav...@gmail.com>:

[Kesav Kumar Kolla]

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.

[Eduardo Macarron]

Yes, you can. MapperScannerConfigurer can take a sqlSessionFactory or
an sqlSessionFactoryName as parameters (depending on the version).

http://www.mybatis.org/spring/mappers.html#MapperScannerConfigurer

(note the page is for 1.1.0)

2012/2/27 Kesav Kumar Kolla <kesav...@gmail.com>:

[Kesav Kumar Kolla]

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.

-kesav

[Eduardo]

Right Kesav. You can use more than one scanner but not scan the same
package with more than one.

[Rick R]

On Mon, Feb 27, 2012 at 1:20 PM, Kesav Kumar Kolla <kesav...@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 ...

<bean id="transactionManagerOne" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

<property name="dataSource" ref="dataSourceOne"/>

</bean>

<bean id="sqlSessionFactoryOne" class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSourceOne"/>

<property name="typeAliasesPackage" value="net.learntechnology.empmaint.business.dbone.domain"/>

</bean>

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

<property name="basePackage" value="net.learntechnology.empmaint.business.dbone.mapper"/>

<property name="sqlSessionFactory" ref="sqlSessionFactoryOne"/>

</bean> 

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

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

        <property name="basePackage" value="com.foo.media.service.mapper" />

<property name="annotationClass" value="com.foo.media.service.annotation.NocrMCH" />

<property name="sqlSessionFactory" ref="nocrMchSessionFactory"/>

 </bean>

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.

--
Rick R

[sudarshan]

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

http://relation.to/Bloggers/MultitenancyInHibernate


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:

[Eduardo Macarron]

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 {

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.

2012/2/28 sudarshan <sudar...@gmail.com>:

[Jeff Butler]

Spring has built in support for this with the AbstractRoutingDatasource. It worked perfectly with Ibatis , I assume it will work with MyBatis too. 

Jeff Butler

[sudarshan]

Thanks that is what i was looking for :)

"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:

> 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 {
>
>     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.
>

> 2012/2/28 sudarshan <sudarsha...@gmail.com>:

[Eduardo Macarron]

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.

[sudarshan]

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

[sudarshan]

In order to solve my multi tenancy requirement i have started to use 

AbstractRoutingDataSource

and it plays well with both hibernate and mybatis :)

[Kesav Kumar Kolla]

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.