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 Class<? extends Log> logImpl;
protected Class<? extends VFS> vfsImpl;
protected LocalCacheScope localCacheScope;
protected JdbcType jdbcTypeForNull;
protected Set<String> 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<String, MappedStatement> mappedStatements;
protected final Map<String, Cache> caches;
protected final Map<String, ResultMap> resultMaps;
protected final Map<String, ParameterMap> parameterMaps;
protected final Map<String, KeyGenerator> keyGenerators;
protected final Set<String> loadedResources;
protected final Map<String, XNode> sqlFragments;
protected final Collection<XMLStatementBuilder> incompleteStatements;
protected final Collection<CacheRefResolver> incompleteCacheRefs;
protected final Collection<ResultMapResolver> incompleteResultMaps;
protected final Collection<MethodResolver> incompleteMethods;
protected final Map<String, String> 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应该怎么填写了。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]