mybatis-plus3.5.3.1 支持不同数据源sql适配

背景

最近公司要求支持国产数据库达梦，人大金仓，高斯等数据库，这些数据库与mysql的语法有一些差异，需要做一些兼容操作。

解决问题

1.不同数据库分页不同

2.支持通过参数控制执行不同的sql

3.没有特殊sql执行默认sql

实现流程

1.代码结构

在这里插入图片描述

2.引入依赖

 
        
        
            com.baomidou
            mybatis-plus-boot-starter
            3.5.3.1
        
        
        
            mysql
            mysql-connector-java
            5.1.47
        
        
            org.projectlombok
            lombok
            1.18.28
        



        
        
            org.opengauss
            opengauss-jdbc
            3.0.0
        
        
        
            cn.com.kingbase
            kingbase8
            8.6.0
        
        
        
            com.dameng
            DmJdbcDriver18
            8.1.1.193
        
        
            com.oracle.database.jdbc
            ojdbc8-production
            19.7.0.0

3.代码

MybatisAutoConfiguration

@Configuration
@MapperScan("com.liuhm.dao.mapper*")
public class MybatisAutoConfiguration {
	@Autowired
	private DataSource dataSource;
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor() {
		MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
		String driverClassName = ((HikariDataSource) dataSource).getDriverClassName();
		// 分页插件
		mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DatabaseIdEnums.getDbTypeByDriver(driverClassName)));

		return mybatisPlusInterceptor;
	}
	@Bean
	public DatabaseIdProvider databaseIdProvider() {
		VendorDatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
		Properties properties = new Properties();
		// 设置数据库厂商和databaseid别名
		for (DatabaseIdEnums databaseIdEnum : DatabaseIdEnums.values()) {
			// key=value
			// key     数据库厂商的databaseid
			// value   别名，和xml里面对应
			properties.put(databaseIdEnum.getName(),databaseIdEnum.getName());
		}
		databaseIdProvider.setProperties(properties);
		return databaseIdProvider;
	}
}

TestMapper

@Repository
@CacheNamespace
public interface TestMapper {
     /**
     * 需要兼容
     * @return
     */
    public List select();

    /**
     * 公共的方法 不兼容
     * @return
     */
    public List selectCommon();

    /**
     * 测试分页
     * @param page
     * @return
     */
    IPage selectPage(Page page);
}

DatabaseIdEnums

@AllArgsConstructor
@Getter
public enum DatabaseIdEnums {
    MYSQL("MySQL", "mysql", "com.mysql.jdbc.Driver",DbType.MYSQL),
    KINGBASEESV8("KingbaseES","kingbaseesv8","com.kingbase8.Driver", com.baomidou.mybatisplus.annotation.DbType.KINGBASE_ES),
    OPENGAUSS3("PostgreSQL", "opengauss3"," org.opengauss.Driver",DbType.OPENGAUSS),
    ORACLE("Oracle", "oracle","oracle.jdbc.driver.OracleDriver",DbType.ORACLE),
    DM("DM DBMS", "dm","dm.jdbc.driver.DmDriver",DbType.DM)
    ;

    /**
     * 数据库名
     */
    private String name;
    /**
     * 数据库名 别名
     */
    private String nameAlias;
    /**
     * 数据库名 别名
     */
    private String driver;
    /**
     * 分页插件名
     */
    private DbType dbType;

    /**
     * 通过数据名获取对应的分页插件
     * @param driver
     * @return
     */
    public static DbType getDbTypeByDriver(String driver) {
        for (DatabaseIdEnums databaseIdEnum : DatabaseIdEnums.values()) {
            if(Objects.equals(databaseIdEnum.getDriver(),driver)){
                return databaseIdEnum.dbType;
            }
        }
        return null;
    }
}

TestMapper.xml



    
    
        SELECT * FROM test;
    

    
        select * from test
    

    
        select * from test where 'mysql' = 'mysql'
    
    
        select * from test where 'kingbaseesv8' = 'kingbaseesv8'
    
    
        select * from test where 'opengauss3' = 'opengauss3'
    
    
        select * from test where 'oracle' = 'oracle'
    
    
        select * from test where 'dm' = 'dm'

4.测试

测试要求

对应数据库导入测试sql
测试公共的方法是否正确
分页测试是否正确
特殊兼容sql测试是否正确

测试代码：

    @Test
	public void Test(){
		testMapper.selectCommon();
		System.out.println("------------------------------");
		testMapper.selectPage(new Page(1,5));
		System.out.println("------------------------------");
		testMapper.selectPage(new Page(2,5));
		System.out.println("------------------------------");
		testMapper.select();
	}

4.1.mysql测试

使用yml配置

    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: Cobbler1234!
    url: jdbc:mysql://192.168.0.229:43306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false

运行结果

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5a05dd30] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1590202270 wrapping com.mysql.jdbc.JDBC4Connection@7a04f730] will not be managed by Spring
==>  Preparing: SELECT * FROM test;
==> Parameters: 
<==    Columns: name
<==        Row: 测试数据mysql 1
<==        Row: 测试数据mysql 2
<==        Row: 测试数据mysql 3
<==        Row: 测试数据mysql 4
<==        Row: 测试数据mysql 5
<==        Row: 测试数据mysql 6
<==        Row: 测试数据mysql 7
  Preparing: SELECT COUNT(*) AS total FROM test
==> Parameters: 
<==    Columns: total
<==        Row: 7
  Preparing: select * from test LIMIT ?
==> Parameters: 5(Long)
<==    Columns: name
<==        Row: 测试数据mysql 1
<==        Row: 测试数据mysql 2
<==        Row: 测试数据mysql 3
<==        Row: 测试数据mysql 4
<==        Row: 测试数据mysql 5
  Preparing: SELECT COUNT(*) AS total FROM test
==> Parameters: 
<==    Columns: total
<==        Row: 7
  Preparing: select * from test LIMIT ?,?
==> Parameters: 5(Long), 5(Long)
<==    Columns: name
<==        Row: 测试数据mysql 6
<==        Row: 测试数据mysql 7
  Preparing: select * from test where 'mysql' = 'mysql'
==> Parameters: 
<==    Columns: name
<==        Row: 测试数据mysql 1
<==        Row: 测试数据mysql 2
<==        Row: 测试数据mysql 3
<==        Row: 测试数据mysql 4
<==        Row: 测试数据mysql 5
<==        Row: 测试数据mysql 6
<==        Row: 测试数据mysql 7
<==      Total: 7
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@615db358]

4.2.kingbaseesv8测试

使用yml配置

    driver-class-name: com.kingbase8.Driver
    username: system
    password: hcloud1234
    url: jdbc:kingbase8://192.168.0.248:54321/kingbase?currentSchema=mcp_manager

运行结果

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1a2ac487] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@629092599 wrapping com.kingbase8.jdbc.KbConnection@3fde2209] will not be managed by Spring
==>  Preparing: SELECT * FROM test;
==> Parameters: 
<==    Columns: name
<==        Row: 测试数据kingbaseesv8 1
<==        Row: 测试数据kingbaseesv8 2
<==        Row: 测试数据kingbaseesv8 3
<==        Row: 测试数据kingbaseesv8 4
<==        Row: 测试数据kingbaseesv8 5
<==        Row: 测试数据kingbaseesv8 6
<==        Row: 测试数据kingbaseesv8 7
  Preparing: SELECT COUNT(*) AS total FROM test
==> Parameters: 
<==    Columns: total
<==        Row: 7
  Preparing: select * from test LIMIT ?
==> Parameters: 5(Long)
<==    Columns: name
<==        Row: 测试数据kingbaseesv8 1
<==        Row: 测试数据kingbaseesv8 2
<==        Row: 测试数据kingbaseesv8 3
<==        Row: 测试数据kingbaseesv8 4
<==        Row: 测试数据kingbaseesv8 5
  Preparing: SELECT COUNT(*) AS total FROM test
==> Parameters: 
<==    Columns: total
<==        Row: 7
  Preparing: select * from test LIMIT ? OFFSET ?
==> Parameters: 5(Long), 5(Long)
<==    Columns: name
<==        Row: 测试数据kingbaseesv8 6
<==        Row: 测试数据kingbaseesv8 7
  Preparing: select * from test where 'kingbaseesv8' = 'kingbaseesv8'
==> Parameters: 
<==    Columns: name
<==        Row: 测试数据kingbaseesv8 1
<==        Row: 测试数据kingbaseesv8 2
<==        Row: 测试数据kingbaseesv8 3
<==        Row: 测试数据kingbaseesv8 4
<==        Row: 测试数据kingbaseesv8 5
<==        Row: 测试数据kingbaseesv8 6
<==        Row: 测试数据kingbaseesv8 7
<==      Total: 7
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79df80a4]

4.3.其余数据库自测

5.注意

5.1.查找对应的数据库名

通过查询 java.sql.DatabaseMetaData接口中getDatabaseProductName方法，找到对应是实现类

在这里插入图片描述

5.2.databaseId对应的是设置的别名

    @Bean
    public DatabaseIdProvider databaseIdProvider() {
        VendorDatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.put("Oracle","oracle");
        properties.put("MySQL","mysql");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }

xml中

    
        select * from test where 'mysql' = 'mysql'
    
    
        select * from test where 'oracle' = 'oracle'

5.3.没有设置databaseId代表该xmlsql全部数据库都通用

5.4.分页插件设置

通过定义的驱动找到对应的DbType

	mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DatabaseIdEnums.getDbTypeByDriver(driverClassName)));

5.5 xml中sql使用顺序

当前数据库有databaseId设置参数，优先使用对应的sql

没有就使用默认的sql

如当前有sql

    
        select * from test
    
    
        select * from test where 'oracle' = 'oracle'

当前运行的是oracle，那就使用databaseId为oracle的sql

当前运行的是达梦，那就使用 select * from test

当前运行的是mysql，那就使用 select * from test

博客地址

代码下载

下面的mybatis-plus-demo3_5_3_1

本文来自网络，不代表协通编程立场，如若转载，请注明出处：https://net2asp.com/66bac2f79a.html

mybatis-plus3.5.3.1 支持不同数据源sql适配

mybatis-plus3.5.3.1 支持不同数据源sql适配

背景

解决问题

实现流程

1.代码结构

2.引入依赖

3.代码

MybatisAutoConfiguration

TestMapper

DatabaseIdEnums

TestMapper.xml

4.测试

4.1.mysql测试

4.2.kingbaseesv8测试

4.3.其余数据库自测

5.注意

5.1.查找对应的数据库名

5.2.databaseId对应的是设置的别名

5.3.没有设置databaseId代表该xmlsql全部数据库都通用

5.4.分页插件设置

5.5 xml中sql使用顺序

相关推荐