springboot使用Mybatis中兼容多数据源的databaseId（databaseIdProvider）的简单使用方法

最近有兼容多数据库的需求，原有数据库使用的mysql，现在需要同时兼容mysql和pgsql，后期可能会兼容更多。

mysql和pgsql很多语法和函数不同，所以有些sql需要写两份，于是在全网搜索如何在mapper中sql不通用的情况下兼容多数据库，中文网络下，能搜到的解决方案大概有两种：1.使用@DS注解的动态数据源；2.使用数据库厂商标识，即databaseIdProvider。第一种多用来同时连接多个数据源，且配置复杂，暂不考虑。第二种明显符合需求，只需要指定sql对应的数据库即可，不指定的即为通用sql。

常规方法

在全网搜索databaseIdProvider的使用方法，大概有两种：

1.在mybatis的xml中配置，大多数人都能搜到这个结果：

<databaseIdProvider type="DB_VENDOR">
  <property name="MySQL" value="mysql"/>
  <property name="Oracle" value="oracle" />
databaseIdProvider>

然后在mapper中：

<select id="selectStudent" databaseId="mysql">
    select * from student where name = #{name} limit 1
select>
<select id="selectStudent" databaseId="oracle">
    select * from student where name = #{name} and rownum < 2
select>

2.创建mybatis的配置类：

import org.apache.ibatis.mapping.DatabaseIdProvider;
import org.apache.ibatis.mapping.VendorDatabaseIdProvider;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
 
import javax.sql.DataSource;
import java.util.Properties;
 
@Configuration
public class MyBatisConfig {
  @Bean
  public DatabaseIdProvider databaseIdProvider() {
    VendorDatabaseIdProvider provider = new VendorDatabaseIdProvider();
    Properties props = new Properties();
    props.setProperty("Oracle", "oracle");
    props.setProperty("MySQL", "mysql");
    props.setProperty("PostgreSQL", "postgresql");
    props.setProperty("DB2", "db2");
    props.setProperty("SQL Server", "sqlserver");
    provider.setProperties(props);
    return provider;
  }
  
  @Bean
  public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
    factoryBean.setDataSource(dataSource);
    factoryBean.setMapperLocations(
        new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*Mapper.xml"));
    factoryBean.setDatabaseIdProvider(databaseIdProvider());
    return factoryBean.getObject();
  }
}

这两种方法，包括在mybatis的github和官方文档的说明，都是看得一头雾水，因为前后无因果关系，DB_VENDOR这种约定好的字段也显得很奇怪，为什么要配置DB_VENDOR？为什么mysql需要写键值对？键值对的key是从那里来的？全网都没有太清晰的说明。

一些发现

有没有更简单的办法？

mybatis的入口是SqlSessionFactory，如果要了解mybatis的运行原理，从这个类入手是最合适的，于是顺藤摸瓜找到了SqlSessionFactoryBuilder类，这个类有很多build方法，打断点之后发现当前配置走的是

  public SqlSessionFactory build(Configuration config) {
    return new DefaultSqlSessionFactory(config);
  }

这个Configuration类就非常显眼了，点进去之后发现这个类的成员变量就是可以在application.yml里直接设置值的变量

public class Configuration {
  protected Environment environment;
  protected boolean safeRowBoundsEnabled;
  protected boolean safeResultHandlerEnabled;
  protected boolean mapUnderscoreToCamelCase;
  protected boolean aggressiveLazyLoading;
  protected boolean multipleResultSetsEnabled;
  protected boolean useGeneratedKeys;
  protected boolean useColumnLabel;
  protected boolean cacheEnabled;
  protected boolean callSettersOnNulls;
  protected boolean useActualParamName;
  protected boolean returnInstanceForEmptyRow;
  protected String logPrefix;
  protected Classextends Log> logImpl;
  protected Classextends VFS> vfsImpl;
  protected LocalCacheScope localCacheScope;
  protected JdbcType jdbcTypeForNull;
  protected Set lazyLoadTriggerMethods;
  protected Integer defaultStatementTimeout;
  protected Integer defaultFetchSize;
  protected ResultSetType defaultResultSetType;
  protected ExecutorType defaultExecutorType;
  protected AutoMappingBehavior autoMappingBehavior;
  protected AutoMappingUnknownColumnBehavior autoMappingUnknownColumnBehavior;
  protected Properties variables;
  protected ReflectorFactory reflectorFactory;
  protected ObjectFactory objectFactory;
  protected ObjectWrapperFactory objectWrapperFactory;
  protected boolean lazyLoadingEnabled;
  protected ProxyFactory proxyFactory;
  protected String databaseId;
  protected Class configurationFactory;
  protected final MapperRegistry mapperRegistry;
  protected final InterceptorChain interceptorChain;
  protected final TypeHandlerRegistry typeHandlerRegistry;
  protected final TypeAliasRegistry typeAliasRegistry;
  protected final LanguageDriverRegistry languageRegistry;
  protected final Map mappedStatements;
  protected final Map caches;
  protected final Map resultMaps;
  protected final Map parameterMaps;
  protected final Map keyGenerators;
  protected final Set loadedResources;
  protected final Map sqlFragments;
  protected final Collection incompleteStatements;
  protected final Collection incompleteCacheRefs;
  protected final Collection incompleteResultMaps;
  protected final Collection incompleteMethods;
  protected final Map cacheRefMap;
……

这里面的配置有些非常眼熟，比如logImpl，可以使用mybatis.configuration.log-impl直接设置值，那么同理，databaseId是不是也可以使用mybatis.configuration.databaseId设置值？答案是肯定的，而且这样设置值，绕过了databaseIdProvider也可以生效。

最简单的方法

如果你的springboot偏向使用application.yml配置或者使用了spring cloud config，又要兼容多数据库，那么你可以加一条配置

mybatis.configuration.database-id: mysql
或者
mybatis.configuration.database-id: orcale

然后在你的mapper中

<select id="selectStudent" databaseId="mysql">
    select * from student where name = #{name} limit 1
select>
<select id="selectStudent" databaseId="oracle">
    select * from student where name = #{name} and rownum < 2
select>
或者
<select id="selectStudent">
    select * from student where 
    <if test="_databaseId=='mysql'">
        name = #{name} limit 1
    if>
    <if test="_databaseId=='oracle'">
        name = #{name} and rownum < 2
    if>
select>

即可切换数据库，不影响其他任何配置，而且也不用纠结databaseIdProvider里的key应该怎么填写了。