快捷導(dǎo)航

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

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

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

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

常規(guī)方法

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

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

<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和官方文檔的說(shuō)明，都是看得一頭霧水，因?yàn)榍昂鬅o(wú)因果關(guān)系，DB_VENDOR這種約定好的字段也顯得很奇怪，為什么要配置DB_VENDOR？為什么mysql需要寫(xiě)鍵值對(duì)？鍵值對(duì)的key是從那里來(lái)的？全網(wǎng)都沒(méi)有太清晰的說(shuō)明。

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

有沒(méi)有更簡(jiǎn)單的辦法？

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

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

這個(gè)Configuration類就非常顯眼了，點(diǎn)進(jìn)去之后發(fā)現(xiàn)這個(gè)類的成員變量就是可以在application.yml里直接設(shè)置值的變量

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直接設(shè)置值，那么同理，databaseId是不是也可以使用mybatis.configuration.databaseId設(shè)置值？答案是肯定的，而且這樣設(shè)置值，繞過(guò)了databaseIdProvider也可以生效。

最簡(jiǎn)單的方法

如果你的springboot偏向使用application.yml配置或者使用了spring cloud config，又要兼容多數(shù)據(jù)庫(kù)，那么你可以加一條配置

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ù)庫(kù)，不影響其他任何配置，而且也不用糾結(jié)databaseIdProvider里的key應(yīng)該怎么填寫(xiě)了。

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

您可能感興趣的文章: