仅改2处配置, 令mybatis兼容多种数据库(非专业方案, 源自官方支持)

爱上mybatis是因为自由自在的sql映射, SQL在手, 一切我有的那种感觉!
然而由于其非完整ORM框架原因, 导致我们掌握了SQL的自由, 却失去了ORM兼容多库的天然特性
本文章告诉你, 如何使 mybatis 轻量支持数据库兼容?

Beacuse of some reason , I write this line , just like because of some reason , you read this line !

温馨提示: 由于此方法过于简单粗暴, 所以面世较晚 , 当你的mybatis版本高于3.1时,才可以用哦!

实现方式

第一处

applicationContext.xml or mybatis-config.xml

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
                        http://www.springframework.org/schema/tx
                        http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
                        http://www.springframework.org/schema/context
                        http://www.springframework.org/schema/context/spring-context-4.0.xsd">

    <context:property-placeholder location="classpath*:jdbc.properties" ignore-unresolvable="true"/>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>


    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="databaseIdProvider" ref="databaseIdProvider" />
        <property name="configLocation" value="classpath:SqlMapConfig.xml" />
        <property name="mapperLocations" value="classpath*:sqlmap/*.xml" />
    </bean>

    <bean id="vendorProperties"
          class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="properties">
            <props>
                <prop key="Oracle">oracle</prop>
                <prop key="MySQL">mysql</prop>
                <prop key="DB2">db2</prop>
                <prop key="Adaptive Server Enterprise">sybase</prop>
                <prop key="SQL Server">sqlserver</prop>
            </props>
        </property>
    </bean>

    <bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
        <property name="properties" ref="vendorProperties" />
    </bean>


    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.xxx" />
        <property name="markerInterface" value="com.xxx.SqlMapper"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
    </bean>

    <bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <tx:annotation-driven transaction-manager="transactionManager"/>

</beans>

先配置一个vendorProperties, 存储productName和其对应的别名, productName为key, 别名为value , 然后配置一个databaseIdProvider, 关联vendorProperties, 使其拿到配置. 最后再sqlSessionFactory中添加
<property name="databaseIdProvider" ref="databaseIdProvider" />即可

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD SQL MAP Config 3.1//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="callSettersOnNulls" value="true"/>
        <setting name="jdbcTypeForNull" value="NULL"/>
    </settings>

    <databaseIdProvider type="DB_VENDOR">
        <property name="MySQL" value="mysql"/>
        <property name="Oracle" value="oracle" />
        <property name="SQL Server" value="sqlserver"/>
        <property name="DB2" value="DB2" />
    </databaseIdProvider>

</configuration>

直接加个<databaseIdProvider type="DB_VENDOR"> 节点就行了

友情提示, 使用mybatis-config.xml方式时, 必须确保改配置文件中定义了environments及其transactionManager和dataSource，否则databaseIdProvider将不生效, 也就是说, 使用spring管理mybatis时, 此方式失效!

第二处

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.UserMapper">

    <resultMap id="result" type="com.xxx.UserModel"/>

    <select id="findAll" resultMap="result" databaseId="mysql">
        SELECT *
        FROM users
        ORDER BY id ASC
    </select>

    <select id="findAll" resultMap="result" databaseId="oracle">
        SELECT *
        FROM users
        ORDER BY id ASC
    </select>

</mapper>

经过本人实测, mybatis查找 statement 的逻辑是 : 先找有databaseId的, 是否有对应的, [1] 若有, 则直接调用(友情提示:别强行写2个相同的databaseId且statement id也相同), [1] 若无, 则查找是否有 未指定databaseId的 statement , [2] 若有, 则调用, [2] 再没有, 就报错了 !
也就是说, 你完全可以以一种数据库为主, 如MySQL为主, Oracle则是某些模块使用, 即写xml时, 一般都直接不写databaseId, 唯有Oracle那个模块才写上 databaseId=”oracle” .比如楼主公司, 就是这种情况! 而另一种情况则是, 整个项目都需要兼容, 那修改量就相对大一些, 需要2个statement分别标明不同的databaseId.

原理简单剖析

经过本人不辞辛苦的查看源码: SqlSessionFactoryBean, 对应的databaseIdProvider代码, 最终发现这么一段代码:

Environment var29 = new Environment(this.environment, this.transactionFactory, this.dataSource);
configuration.setEnvironment(var29);
if(this.databaseIdProvider != null) {
    try {
        configuration.setDatabaseId(this.databaseIdProvider.getDatabaseId(this.dataSource));
    } catch (SQLException var22) {
        throw new NestedIOException("Failed getting a databaseId", var22);
    }
}

这段代码是将dataSource传给databaseIdProvider,让databaseIdProvider获取databaseId,并设置到mybatis中!

下面看databaseIdProvider如何获取databaseId

private String getDatabaseProductName(DataSource dataSource) throws SQLException {
        Connection con = null;

        String var4;
        try {
            con = dataSource.getConnection();
            DatabaseMetaData metaData = con.getMetaData();
            var4 = metaData.getDatabaseProductName();
        } finally {
            if(con != null) {
                try {
                    con.close();
                } catch (SQLException var11) {
                    ;
                }
            }

        }

        return var4;
    }

原来最终时通过connection.getMetaData.getDatabaseProductName()方法得到一个东东,就是配置中properties的 key , 然后看下面这段代码:

private String getDatabaseName(DataSource dataSource) throws SQLException {
        String productName = this.getDatabaseProductName(dataSource);
        if(this.properties != null) {
            Iterator i$ = this.properties.entrySet().iterator();

            Entry property;
            do {
                if(!i$.hasNext()) {
                    return null;
                }

                property = (Entry)i$.next();
            } while(!productName.contains((String)property.getKey()));

            return (String)property.getValue();
        } else {
            return productName;
        }
    }

根据connection获得了当前数据源真实的productName, 如MySQL, Oracle .而后通过配置进来的properties判断是否包含这个 key , 若有进行返回! 无则直接返回productName !

以上的源码都是分析databaseId时如何获取, 如何被设置到mybatis中的, 至于mybatis如何根据databaseId判断对应的statement, 那还不是小意思, 不过本人小白一个, 没有看过mybatis源码, 对其不熟悉, 要找这块源码太费时间, 浮躁的社会我也不能幸免 !