We use mybatis interceptor to intercept prepare() of statementHandler，then we got the Connection object from args[0] in the interceptor method for a Statement object could be got from the Connecton, after that, we use the Statement to execute "use xxx" to switch another schema for the next sql execution would be run in that scope. But problem is the "switch to another schema" not wotk sometims (tested in different server, some server work, but some failed), that run into the previous setted schema, which means that the sql execution in last interceptor was take effect in current interceptor (here the sql we execute is the same except "use xxx")!? That is related mybatis problem or mysql database or both of them? P.s. We also close the mybatis level 1, 2 cache，but still not work.

Comment From: harawata

I am not sure what is going on, but MyBatis does not change the schema automatically.

Besides, to change the schema or catalog, you should use the JDBC API instead of executing USE statement. In case of MySQL, calling Connection#setCatalog() changes the target database. Please try changing your interceptor and let us know the result.

Comment From: lax678

hi harawata,
First, I dont understand why cant use "Statement" object in mybatis to execut "use xxx" before run real requested SQL? Here is our code to use new schema in mybatis interceptor: " connection.getStatement().execute("use xxx"); " And we have tested the impl could work on local and some online environment, but that failed on other online environment. Could you please expain the reason why cant use this way to switch schema?

Secondly, I have tried use "connection.setCatelog(xxx)" in mybatis interceptor to change current schema. That works in current thread. But we need to revert the catelog to original one for next use, the problem is we cant find a correct way to REVERT the catelog to original one after run "invoke.process()". That is the connection always use the new catelog in the next invocaton if dont revert it. We have tried set back the catelog after the "invoke.process()", but the real execution point of sql seems after the connection reverted sometimes. So, the original schema is used again instead of the new one. could you please provide any idea to fix this problem?

Sorry for the long description. In fact, our problem is based on "multi tenant function" supporting using mybatis. I know mybatis support that function on field level in database table. But we need supprt that in schema level to isolate different tenant.

Comment From: harawata

First, I dont understand why cant use "Statement" object in mybatis to execut "use xxx" before run real requested SQL?

Because the doc says so :D

Always use the Connection.setCatalog() method to specify the desired database in JDBC applications, rather than the USE database statement.

the connection always use the new catelog in the next invocaton if dont revert it.

The idea is to call setCatalog on every invocation of prepare method. Reverting the current database sounds problematic especially if you use lazy loading, etc..

We have tried set back the catelog after the "invoke.process()", but the real execution point of sql seems after the connection reverted sometimes.

The javadoc says that the timing is 'implementation defined' (= not guaranteed).

Calling setCatalog has no effect on previously created or prepared Statement objects. It is implementation defined whether a DBMS prepare operation takes place immediately when the Connection method prepareStatement or prepareCall is invoked. For maximum portability, setCatalog should be called before a Statement is created or prepared.

Comment From: lax678

Ok，got that. Thanks for your kindly support!

This problem seems not related mybatis.

And we will use other way to implement the interceptor.