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

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

背景

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

解决问题

1.不同数据库分页不同

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

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

实现流程

1.代码结构

在这里插入图片描述

2.引入依赖

 <!-- Tag-单元测试 junit-->
        <!-- Tag-mybatis plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>
        <!-- Tag-mybatis plus-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.28</version>
        </dependency>



        <!-- https://mvnrepository.com/artifact/org.opengauss/opengauss-jdbc -->
        <dependency>
            <groupId>org.opengauss</groupId>
            <artifactId>opengauss-jdbc</artifactId>
            <version>3.0.0</version>
        </dependency>
        <!--kingbase 最低可支持JDK1.8 -->
        <dependency>
            <groupId>cn.com.kingbase</groupId>
            <artifactId>kingbase8</artifactId>
            <version>8.6.0</version>
        </dependency>
        <!--  达梦-->
        <dependency>
            <groupId>com.dameng</groupId>
            <artifactId>DmJdbcDriver18</artifactId>
            <version>8.1.1.193</version>
        </dependency>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8-production</artifactId>
            <version>19.7.0.0</version>
        </dependency>

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<String> select();

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

    /**
     * 测试分页
     * @param page
     * @return
     */
    IPage<String> selectPage(Page<String> 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
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.liuhm.dao.mapper.TestMapper">
    <cache-ref namespace="com.liuhm.dao.mapper.TestMapper"/>
    <select id="selectCommon" resultType="java.lang.String">
        SELECT * FROM test;
    </select>

    <select id="selectPage" resultType="java.lang.String">
        select * from test
    </select>

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

</mapper>

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
<==      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]

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
<==      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]

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 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>
5.3.没有设置databaseId代表该xmlsql全部数据库都通用
5.4.分页插件设置

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

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

博客地址

代码下载

下面的mybatis-plus-demo3_5_3_1

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.mfbz.cn/a/152580.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Spi机制的必要性

SpringBoot 为啥单独加载类路径下spring.factories文件中的类&#xff1f; SpringBoot 应用运行过程中存在两种类型的类初始化&#xff1a;一部分为已经提前装载到IOC容器中的bean&#xff0c;另一部分则为实时new的bean。 IOC容器中的bean包含&#xff1a;启动类所在包路径下…

Docker的3主3从redis集群配置(扩容和缩容配置)

3主3从redis集群配置 1、关闭防火墙启动docker后台服务 systemctl start docker2、新建6个docker容器redis实例 docker run -d --name redis-node-1 --net host --privilegedtrue -v /data/redis/share/redis-node-1:/data redis:6.0.8 --cluster-enabled yes --appendonly …

基于Vue+SpringBoot的城市桥梁道路管理系统 开源项目

目录 一、摘要1.1 项目介绍1.2 项目录屏 二、功能模块三、系统展示四、核心代码4.1 查询城市桥梁4.2 新增城市桥梁4.3 编辑城市桥梁4.4 删除城市桥梁4.5 查询单个城市桥梁 五、免责说明 一、摘要 1.1 项目介绍 基于VueSpringBootMySQL的城市桥梁道路管理系统&#xff0c;支持…

小型机房380V断电报警门磁开关状态检测远程控制RTU

在现代社会中&#xff0c;小型机房起到了至关重要的作用&#xff0c;为各种系统和设备提供稳定的电力供应。然而&#xff0c;由于各种原因&#xff0c;如供电故障、设备故障或非法侵入等&#xff0c;机房的正常运行可能会受到威胁。为了保障机房的安全和可靠性&#xff0c;我们…

了解七大经典排序算法,看这一篇就足够了!!!

✏️✏️✏️好&#xff0c;那么今天给大家分享一下七大经典排序算法&#xff01; 清风的CSDN博客 &#x1f61b;&#x1f61b;&#x1f61b;希望我的文章能对你有所帮助&#xff0c;有不足的地方还请各位看官多多指教&#xff0c;大家一起学习交流&#xff01; 动动你们发财的…

java Could not resolve placeholder

1、参考&#xff1a;https://blog.csdn.net/yu1812531/article/details/123466616 2、配置文件: 3、在application.properties中设置要使用的配置文件

最简单的测试Jquery-jquery是否正常工作的代码

01-运行后在页面上显示“jQuery is working!” 代码如下&#xff1a; <!DOCTYPE html> <html> <head><meta charset"UTF-8"><title>it is title</title><meta name"viewport" content"widthdevice-width,in…

小程序游戏、App游戏与H5游戏:三种不同的游戏开发与体验方式

在当今数字化的时代&#xff0c;游戏开发者面临着多种选择&#xff0c;以满足不同用户群体的需求。小程序游戏、App游戏和H5游戏是三种流行的游戏开发和发布方式&#xff0c;它们各自具有独特的特点和适用场景。 小程序游戏&#xff1a;轻巧便捷的社交体验 小程序游戏是近年来…

小米手环8pro重新和手机配对解决办法

如果更换了手机&#xff0c;那么小米手环8pro是无法和新手机自动连接的。 但是在新手机上直接连接又连接不上&#xff0c;搜索蓝牙根本找不到手环的蓝牙。 解决办法就是&#xff1a; 把手环恢复出厂&#xff01;&#xff01;&#xff01;&#xff01;&#xff01; 是的&…

骨传导蓝牙耳机排行榜,精选五款骨传导耳机推荐!

目前市面上的骨传导耳机大多是传统挂耳式&#xff0c;虽然佩戴更稳固&#xff0c;但是也限制住了其使用场景&#xff0c; 但近两年&#xff0c;有一款名为骨传导耳机的品类进入了大众的视野&#xff0c;它以独特的款式和超乎以往的佩戴舒适性迅速圈粉无数&#xff0c;并成为当下…

使用Rust编写爬虫代码来抓取精美的图片

目录 一、引言 二、Rust爬虫框架介绍 三、爬虫代码实现 1、创建Scrapy项目 2、创建Spider 3、定义Item对象 4、修改settings.py文件 5、运行爬虫程序 四、图片抓取与存储 五、优化爬虫性能 六、注意事项 总结 一、引言 网络爬虫是一种自动化的网页访问工具&#x…

高德地图系列(四):vue项目利用高德地图实现车辆的路线规划

目录 第一章 效果图 第二章 源代码 第一章 效果图 小编该案例主要实现的两个点的思路&#xff1a;1、有两个正常的经纬度就可以在地图中绘制出汽车从起点到终点的路线规划&#xff1b;2、当用户经纬度发生变化时&#xff0c;用户可以通过某个操作&#xff0c;或者程序员通过…

【Attack】针对GNN-based假新闻检测器

Attacking Fake News Detectors via Manipulating News Social Engagement AbstractMotivationContributions FormulationMethodologyAttacker Capability&#xff08;针对挑战1&#xff09;Agent Configuration&#xff08;针对挑战3&#xff09; WWW’23, April 30-May 4, 20…

单点车流量与饱和度的计算思考

sat&#xff1a;饱和度 v&#xff1a;平均车速 d(v)&#xff1a;车速为v情况下的安全车距&#xff08;车距车身长&#xff0c;平均值&#xff09; l&#xff1a;车道数 f&#xff1a;单位时间监测流量&#xff08;车/min&#xff09; 饱和度计算公式&#xff1a; 推导过程…

【23真题】魔都高校真题!刷一刷!

今天分享的是23年上海海事大学806的信号与系统试题及解析。 本套试卷难度分析&#xff1a;22年上海海事大学806考研真题&#xff0c;我也发布过&#xff0c;若有需要&#xff0c;戳这里自取&#xff01;本套试题内容难度适中&#xff0c;题量适中&#xff0c;考察的知识点不难…

插件漏洞导致 60 万个 WordPress 网站遭受攻击

WordPress 插件 WP Fastest Cache 容易受到 SQL 注入漏洞的攻击&#xff0c;该漏洞可能允许未经身份验证的攻击者读取站点数据库的内容。 WP Fastest Cache 是一个缓存插件&#xff0c;用于加速页面加载、改善访问者体验并提高网站在 Google 搜索上的排名。 根据 WordPress.o…

网站高性能架构设计——高性能NOSQL与缓存

从公众号转载&#xff0c;关注微信公众号掌握更多技术动态 --------------------------------------------------------------- 一、NOSQL简介 1.关系数据库存在如下缺点 (1)关系数据库存储的是行记录&#xff0c;无法存储数据结构 以微博的关注关系为例&#xff0c;“我关注…

HT81696 立体声D类音频功率放大器应用领域

HT81696 立体声D类音频功率放大器应用领域于&#xff1a;・智N音响 ・无线音响 ・便携式音箱 ・2.1声道小音箱・拉杆音箱 ・便携式游戏机等等。 HT81696内部集成免滤波器调制技术&#xff0c;能够直接驱动扬声器&#xff0c;内置的关断功能使待机电流Z小化&#xff0c;还集成了…

「Python编程基础」第3章:控制流

文章目录 一、用“炒菜”简单介绍下“控制流”二、布尔值三、比较运算符四、 和 操作符的区别五、布尔操作符六、混合布尔和比较操作符七、代码块是什么&#xff1f;八、控制流语句1. if 语句2. else语句3. elif语句4. 总结 九、while循环语句十、break语句十一、continue语句…

[读论文]DiT Scalable Diffusion Models with Transformers

论文翻译Scalable Diffusion Models with Transformers-CSDN博客 论文地址&#xff1a;https://arxiv.org/pdf/2212.09748.pdf 项目地址&#xff1a;GitHub - facebookresearch/DiT: Official PyTorch Implementation of "Scalable Diffusion Models with Transformers&qu…