MyBatis - DatabaseIdProvider多数据库类型适配

需求

同一份代码， 后期部署可能会使用不同类型的数据库， 不同语句SQL语法不一致， 应该如何适配多种类型数据源？

配置类

@Configuration
public class DataSourceConfig {
    @Bean
    public DatabaseIdProvider getDatabaseIdProvider(){
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        // 以.setProperty("Oracle","oracle") 为例
        // Properties 中 "Oracle" 为 DatabaseProductName 每种数据库的JDBC都有自己Name, 如果下面列的没有自己的数据库类型， 可以Debug模式下调试VendorDatabaseIdProvider.getDatabaseName()
        // Properties 中 "oracle" 为 databaseId, 属于自定义， 后面匹配SQL时会用到
        properties.setProperty("Oracle","oracle");
        properties.setProperty("MySQL","mysql");
        properties.setProperty("DB2","db2");
        properties.setProperty("Derby","derby");
        properties.setProperty("H2","h2");
        properties.setProperty("HSQL","hsql");
        properties.setProperty("Informix","informix");
        properties.setProperty("MS-SQL","ms-sql");
        properties.setProperty("PostgreSQL","postgresql");
        properties.setProperty("Sybase","sybase");
        properties.setProperty("Hana","hana");
        // 国产达梦和人大金仓 支持postgresql 的SQL语法，这里暂时设置成相同databaseId
        properties.setProperty("DM DBMS","postgresql");
        properties.setProperty("KingbaseES","postgresql");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return new DruidDataSource();
    }
    @Bean
    public PlatformTransactionManager platformTransactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        //指定数据源
        factoryBean.setDataSource(dataSource());
        //指定所有mapper.xml所在路径
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        List<Resource> resources  = new ArrayList<>();
        resources.addAll(Arrays.asList(resolver.getResources("classpath*:mappers/*/*.xml")));
        Resource[] resourceArr = new Resource[resources.size()];
        factoryBean.setMapperLocations(resources.toArray(resourceArr));
        factoryBean.setDatabaseIdProvider(getDatabaseIdProvider());
        return factoryBean.getObject();
    }
}

XML配置

不同语法的sql, 不同类型数据源 根据 databaseId 来执行对应的SQL

  <select id="selectEntityByDirectoryId"  resultMap="BaseResultMap" databaseId="mysql">
    SELECT
      entityid, entityname, entitycode, datasourceid, dataschemaname
    FROM m_entity
    WHERE isdelete=0 and dataschemaname = "test"
    ORDER BY `order` ASC LIMIT 0,10
  </select>
  <select id="selectEntityByDirectoryId"  resultMap="BaseResultMap" databaseId="postgresql">
    SELECT
      entityid, entityname, entitycode, datasourceid, dataschemaname
    FROM m_entity
    WHERE isdelete=0 and dataschemaname = 'test'
    ORDER BY "order" ASC LIMIT 10 offset 0
  </select>

MyBatis 会加载对应 databaseId 和 没有配置 databaseId 属性的所有SQL语句。如果同时找到带有 databaseId 和不带databaseId 的相同语句，则带有 databaseId 的sql 优先级更高