当前位置： 代码网 > it编程>编程语言>Java > mybatis-plus3.5.3.1 支持不同数据源sql适配

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

2024年08月03日 • Java •我要评论
最近公司要求支持国产数据库达梦，人大金仓，高斯等数据库，这些数据库与mysql的语法有一些差异，需要做一些兼容操作。

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.查找对应的数据库名
databaseid  通过 vendordatabaseidprovider.getdatabaseid 然后通过驱动获取当前连接的databaseproductname ，再和当前定义的databaseidprovider对比

通过查询 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)));
5.5 xml中sql使用顺序

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

没有就使用默认的sql

如当前有sql

    <select id="select" resulttype="java.lang.string" >
        select * from test
    </select>
    <select id="select" resulttype="java.lang.string" databaseid="oracle">
        select * from test where 'oracle' = 'oracle'
    </select>

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

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

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

代码下载

下面的mybatis-plus-demo3_5_3_1

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com