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

    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
        

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44

    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;
	}
}



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    TestMapper
    @Repository
@CacheNamespace
public interface TestMapper {
     /**
     * 需要兼容
     * @return
     */
    public List select();

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

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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    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;
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    TestMapper.xml
    


    
    

    

    
    
    
    
    



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    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();
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    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


    • 1
    • 2
    • 3
    • 4
    • 5

    运行结果

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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    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

    • 1
    • 2
    • 3
    • 4

    运行结果

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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    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;
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    xml中

        <select id="select" resultType="java.lang.String" databaseId="mysql">
        select * from test where 'mysql' = 'mysql'
    select>
    <select id="select" resultType="java.lang.String" databaseId="oracle">
        select * from test where 'oracle' = 'oracle'
    select>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    5.3.没有设置databaseId代表该xmlsql全部数据库都通用
    5.4.分页插件设置

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

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

    • 1

    博客地址

    代码下载

    下面的mybatis-plus-demo3_5_3_1

  • 相关阅读:
    深度解密Go底层Map
    Okapi Framework
    Linux的基本使用（二）文件操作命令
    全面讲解 Handler机制原理解析 （小白必看）
    使用乐鑫 Web IDE 助力物联网开发
    哈啰面试：说说Dubbo运行原理？
    go开发调试之Delve的使用
    尚硅谷Vue系列教程学习笔记（2）
    【区块链 | IPFS】IPFS cluster私有网络集群搭建
    Git【入门】从安装到会用（千字总结●超详细）
  • 原文地址：https://blog.csdn.net/qq_36120342/article/details/134440397