Mybatis中兼容多數(shù)據(jù)源的databaseId(databaseIdProvider)的簡單使用方法

更新時間：2024年07月08日 08:43:07 作者：Variazioni

本文主要介紹了Mybatis中兼容多數(shù)據(jù)源的databaseId(databaseIdProvider)的簡單使用方法,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

mysql和pgsql很多語法和函數(shù)不同，所以有些sql需要寫兩份，于是在全網(wǎng)搜索如何在mapper中sql不通用的情況下兼容多數(shù)據(jù)庫，中文網(wǎng)絡下，能搜到的解決方案大概有兩種：1.使用@DS注解的動態(tài)數(shù)據(jù)源；2.使用數(shù)據(jù)庫廠商標識，即databaseIdProvider。第一種多用來同時連接多個數(shù)據(jù)源，且配置復雜，暫不考慮。第二種明顯符合需求，只需要指定sql對應的數(shù)據(jù)庫即可，不指定的即為通用sql。

常規(guī)方法

在全網(wǎng)搜索databaseIdProvider的使用方法，大概有兩種：

1.在mybatis的xml中配置，大多數(shù)人都能搜到這個結果：

<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.創(chuàng)建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是從那里來的？全網(wǎng)都沒有太清晰的說明。

一些發(fā)現(xiàn)

有沒有更簡單的辦法？

mybatis的入口是SqlSessionFactory，如果要了解mybatis的運行原理，從這個類入手是最合適的，于是順藤摸瓜找到了SqlSessionFactoryBuilder類，這個類有很多build方法，打斷點之后發(fā)現(xiàn)當前配置走的是

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

這個Configuration類就非常顯眼了，點進去之后發(fā)現(xiàn)這個類的成員變量就是可以在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，又要兼容多數(shù)據(jù)庫，那么你可以加一條配置

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>

即可切換數(shù)據(jù)庫，不影響其他任何配置，而且也不用糾結databaseIdProvider里的key應該怎么填寫了。

到此這篇關于Mybatis中兼容多數(shù)據(jù)源的databaseId(databaseIdProvider)的簡單使用方法的文章就介紹到這了,更多相關Mybatis兼容多數(shù)據(jù)源內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: