Add built-in multi DB vendor support about mybatis HOT 23 CLOSED

I got to this issue searching for the word "Spring" :) I think this could be a 
really insteresting feature for building muti-db configurable apps. 

But I don´t like the idea to build it at "mybatis-spring" level.

I don´t know well mubatis 3 internals but I wonder if a new attribute 
"dataBase" could be added

<mapper namespace="com.acme.banking.persistence.dao.ITransactionTypeDao">
<resultMap id="get-transactionType-result"
type="TransactionType">
<result property="code" column="code"/>
<result property="title" column="title"/>
</resultMap>
<select id="getAll" dataBase="db2 resultType="TransactionType">
SELECT code, title
FROM transaction_type
</select>
<select id="getAll" dataBase="oracle" resultType="TransactionType">
SELECT title, code
FROM transaction_type
</select>
<select id="getAll" resultType="TransactionType">
SELECT title, code
FROM transaction_type
</select>
</mapper>

Then, when a mapper is executed, the proper xml snippet should be used for 
execution by inspecting SqlSession inner database metatadata information.

The best way to do this is to use a separate set of files and a separate 
mapper. It would probably take you an hour to create something to satisfy this 
need.  In addition, it keeps your mapper files from growing with every database 
you add.

But in that case you should have to repackage you app or control that by code. 

I mean: if you have a MyDB2Mapper and MyOracleMapper both on the same jar you 
should control by code which one you call and thats an awful solution. 
Otherwise you should have different jars with the sampe mappers DB2Mappers.jar 
that holds MyMapper and OracleMappers.jar that holds again MyMapper both 
mappers with different xml files or different annotations but the same 
signature. This way you should make a new package for each database. 

That will indeed work but what Russ Jackson wants to do is to auto-detect with 
database are you connecting to and transparently use the right annotation or 
the right pice of xml. I would say that there is no way to do that with mappers 
unless you make your own version of the binding package (MapperProxy & 
MapperMethod).

After thinking about that, maybe Spring can do it. It seems easy for mappers:
http://groups.google.com/group/mybatis-user/browse_thread/thread/defa83a5b65cd81
b

This code will load different mapper interfaces based on the datasource 
metadata. All them will extend the same mapper interface so business code will 
see just one data access layer although there will be as many versions as 
databases are supported.

This can also be done in the SqlSessionTemplate (similar to SqlSessionManager), 
changing statement names instead of whole mappers.

I don´t know if this should go in MyBatis-Spring or will be better let users 
do their extensions.

This is a working version of the MapperFactoryBean that can be used if someone 
needs this feature. 

The idea Eduardo suggested is good, but this seems like it's general enough and 
useful enough that we would want it to be in base MyBatis, not just the Spring 
integration.

What I would really like to see is the following:
1) Same namespace, no matter the DB
2) Same interface, no matter the DB
3) Base mapper xml file for "common" elements
4) Mapper xml for statements that only apply to one DB

In other words, if I have a com.example.mapper.UserMapper interface, I would 
expect the namespace to be com.example.mapper.UserMapper. These should not 
change just because I am using a different DB. But for different databases, I 
think having the namespace define the "base package" is reasonable. For 
example, the mapper xml file for MySql would be in 
com/example/mapper/mysql/UserMapper and the Oracle mapper xml file would be in 
com/example/mapper/oracle/UserMapper.

I think this can be supported very easily with a change to 
org.apache.ibatis.builder.annotation.MapperAnnotationBuilder.loadXmlResource(). 
That function could check the Environment.id and attempt to load a resource 
based on the environment id and fallback to the base if not found. This allows 
different mapper xml files for different Environment ids but it doesn't allow 
merging of multiple xml files.

How big of a change would it be to support merging mapper xml files? Is this 
worth doing?

Note, the idea of using the environment id came from this discussion thread: 
http://groups.google.com/group/mybatis-user/browse_thread/thread/defa83a5b65cd81
b/9bac913bf96aac9a?lnk=gst&q=properties#9bac913bf96aac9a

In addition to merging xml files, it would also be nice to support the 
environment id as a built-in property that could be used for substitution in 
the xml file. This would allow minor changes (i.e. things like JDBCType, casts, 
function names) to be put into the base xml file using <if> for different 
databases.

Attached a simple patch that is consistent with Hunter objectives:
1) Same namespace, no matter the DB
2) Same interface, no matter the DB
3) Base mapper xml file for "common" elements
4) Mapper xml for statements that only apply to one DB

The good point about this patch is that is quite easy and small and add almost 
no complexity to MyBatis.

Basically it is just adding a databaseId attribute to xml statements. So a 
mapper file will look like this:

  <select id="select1" resultType="string">
    select '1-common' from names
  </select>

  <select id="select1" databaseId="hsql" resultType="string">
    select '1-hsql' from names
  </select>

In the case the datasource's vendor is hsql the second statement will be used, 
otherwise the first one will be used.

There are three problems with this patch:

1.- It will fail if MyBatis is configured with (for example) an Oracle 
datasource and SqlSession is created out of a DB2 connection using the same 
SqlSessionFactory. (The user will probably deserve the error he will get ;) )

2.- If there are repeated statements MyBatis will fire an exception depending 
on their order. Imagine we are using an Oracle datasource.

This will work:

  <select id="ambiguousSelect" databaseId="Oracle" resultType="string">
    ...
  </select>
  <select id="ambiguousSelect" resultType="string">
    ...
  </select>
  <select id="ambiguousSelect" resultType="string">
    ...
  </select>

but this will fail:

  <select id="ambiguousSelect" resultType="string">
    ...
  </select>
  <select id="ambiguousSelect" resultType="string">
    ...
  </select>
  <select id="ambiguousSelect" databaseId="Oracle" resultType="string">
    ...
  </select>

This is because once the specific vendor statement has been read, all 
no-specific vendor statements are ignored.

3.- I will not work with annotated mappers. This just works for xml files.

[deleted comment]

Hi there, the patch has been applied but the status is still accepted. Does it 
mean that it is not production ready ? Thanks

Oh, sorry that was wrong. That was removed from 3.0.x relese and should be 
available on 3.1. Do you need it?

Everybody wants it. It's a killer feature ! When is it planned to release 3.1 ?

:) Good! There is not a release date yet, but is not going to be soon.

hi, i was creating a select query and found this feature (databaseId) from 
http://mybatis.org/dtd/mybatis-3-mapper.dtd...  but then using last release 
3.0.6 was not working, it was removed from trunk.

why you rollbacked this feature? is great!

thanks

does this feature is available in myBatis 3.1 snapshop?
does any doc will present this feature?

yes, have a look at www.mybatis.org/core/

Here's the direct link... 
http://www.mybatis.org/core/configuration.html#databaseIdProvider

[deleted comment]