springboot利用Mybatis中兼容多数据源的databaseId（databaseIdProvider） ...

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

在全网搜索databaseIdProvider的利用方法，大概有两种：
1.在mybatis的xml中配置，大多数人都能搜到这个效果：

1. <databaseIdProvider type="DB_VENDOR">
2.   <property name="MySQL" value="mysql"/>
3.   <property name="Oracle" value="oracle" />
4. </databaseIdProvider>

复制代码

然后在mapper中：

1. <select id="selectStudent" databaseId="mysql">
2.     select * from student where name = #{name} limit 1
3. </select>
4. <select id="selectStudent" databaseId="oracle">
5.     select * from student where name = #{name} and rownum < 2
6. </select>

复制代码

2.创建mybatis的配置类：

1. import org.apache.ibatis.mapping.DatabaseIdProvider;
2. import org.apache.ibatis.mapping.VendorDatabaseIdProvider;
3. import org.apache.ibatis.session.SqlSessionFactory;
4. import org.mybatis.spring.SqlSessionFactoryBean;
5. import org.springframework.context.annotation.Bean;
6. import org.springframework.context.annotation.Configuration;
7. import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
9. import javax.sql.DataSource;
10. import java.util.Properties;
12. @Configuration
13. public class MyBatisConfig {
14.   @Bean
15.   public DatabaseIdProvider databaseIdProvider() {
16.     VendorDatabaseIdProvider provider = new VendorDatabaseIdProvider();
17.     Properties props = new Properties();
18.     props.setProperty("Oracle", "oracle");
19.     props.setProperty("MySQL", "mysql");
20.     props.setProperty("PostgreSQL", "postgresql");
21.     props.setProperty("DB2", "db2");
22.     props.setProperty("SQL Server", "sqlserver");
23.     provider.setProperties(props);
24.     return provider;
25.   }
26.   
27.   @Bean
28.   public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
29.     SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
30.     factoryBean.setDataSource(dataSource);
31.     factoryBean.setMapperLocations(
32.         new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*Mapper.xml"));
33.     factoryBean.setDatabaseIdProvider(databaseIdProvider());
34.     return factoryBean.getObject();
35.   }
36. }

复制代码

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

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

1.   public SqlSessionFactory build(Configuration config) {
2.     return new DefaultSqlSessionFactory(config);
3.   }

复制代码

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

1. public class Configuration {
2.   protected Environment environment;
3.   protected boolean safeRowBoundsEnabled;
4.   protected boolean safeResultHandlerEnabled;
5.   protected boolean mapUnderscoreToCamelCase;
6.   protected boolean aggressiveLazyLoading;
7.   protected boolean multipleResultSetsEnabled;
8.   protected boolean useGeneratedKeys;
9.   protected boolean useColumnLabel;
10.   protected boolean cacheEnabled;
11.   protected boolean callSettersOnNulls;
12.   protected boolean useActualParamName;
13.   protected boolean returnInstanceForEmptyRow;
14.   protected String logPrefix;
15.   protected Class<? extends Log> logImpl;
16.   protected Class<? extends VFS> vfsImpl;
17.   protected LocalCacheScope localCacheScope;
18.   protected JdbcType jdbcTypeForNull;
19.   protected Set<String> lazyLoadTriggerMethods;
20.   protected Integer defaultStatementTimeout;
21.   protected Integer defaultFetchSize;
22.   protected ResultSetType defaultResultSetType;
23.   protected ExecutorType defaultExecutorType;
24.   protected AutoMappingBehavior autoMappingBehavior;
25.   protected AutoMappingUnknownColumnBehavior autoMappingUnknownColumnBehavior;
26.   protected Properties variables;
27.   protected ReflectorFactory reflectorFactory;
28.   protected ObjectFactory objectFactory;
29.   protected ObjectWrapperFactory objectWrapperFactory;
30.   protected boolean lazyLoadingEnabled;
31.   protected ProxyFactory proxyFactory;
32.   protected String databaseId;
33.   protected Class<?> configurationFactory;
34.   protected final MapperRegistry mapperRegistry;
35.   protected final InterceptorChain interceptorChain;
36.   protected final TypeHandlerRegistry typeHandlerRegistry;
37.   protected final TypeAliasRegistry typeAliasRegistry;
38.   protected final LanguageDriverRegistry languageRegistry;
39.   protected final Map<String, MappedStatement> mappedStatements;
40.   protected final Map<String, Cache> caches;
41.   protected final Map<String, ResultMap> resultMaps;
42.   protected final Map<String, ParameterMap> parameterMaps;
43.   protected final Map<String, KeyGenerator> keyGenerators;
44.   protected final Set<String> loadedResources;
45.   protected final Map<String, XNode> sqlFragments;
46.   protected final Collection<XMLStatementBuilder> incompleteStatements;
47.   protected final Collection<CacheRefResolver> incompleteCacheRefs;
48.   protected final Collection<ResultMapResolver> incompleteResultMaps;
49.   protected final Collection<MethodResolver> incompleteMethods;
50.   protected final Map<String, String> cacheRefMap;
51. ……

复制代码

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

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

1. mybatis.configuration.database-id: mysql
2. 或者
3. mybatis.configuration.database-id: orcale

复制代码

然后在你的mapper中

1. <select id="selectStudent" databaseId="mysql">
2.     select * from student where name = #{name} limit 1
3. </select>
4. <select id="selectStudent" databaseId="oracle">
5.     select * from student where name = #{name} and rownum < 2
6. </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应该怎么填写了。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。