• 手搭手Mybatis-Plus多数据源异构数据迁移案例

    环境介绍

    技术栈

    springboot+mybatis-plus+druid+baomidou+mysql+oracle+dm

    软件

    版本

    mysql

    8

    IDEA

    IntelliJ IDEA 2022.2.1

    JDK

    1.8

    Spring Boot

    2.7.13

    mybatis

    2.3.1

    配置多数据源依赖
       
            com.baomidou
            dynamic-datasource-spring-boot-starter
            3.5.0
       

    pom.xml所需依赖

    1. <dependencies>
    2.     <dependency>
    3.         <groupId>org.springframework.boot</groupId>
    4.         <artifactId>spring-boot-starter-web</artifactId>
    5.     </dependency>
    6.     <dependency>
    7.         <groupId>com.baomidou</groupId>
    8.         <artifactId>mybatis-plus-boot-starter</artifactId>
    9.         <version>3.5.3.2</version>
    10.     </dependency>
    11.     <dependency>
    12.         <groupId>com.baomidou</groupId>
    13.         <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    14.         <version>3.5.0</version>
    15.     </dependency>
    16.     <dependency>
    17.         <groupId>com.alibaba</groupId>
    18.         <artifactId>druid-spring-boot-starter</artifactId>
    19.         <version>1.1.14</version>
    20.     </dependency>
    21.     <dependency>
    22.         <groupId>com.mysql</groupId>
    23.         <artifactId>mysql-connector-j</artifactId>
    24.         <scope>runtime</scope>
    25.     </dependency>
    26.     <dependency>
    27.         <groupId>com.alibaba</groupId>
    28.         <artifactId>druid</artifactId>
    29.         <version>1.2.15</version>
    30.     </dependency>
    31.     <dependency>
    32.         <groupId>org.projectlombok</groupId>
    33.         <artifactId>lombok</artifactId>
    34.         <optional>true</optional>
    35.     </dependency>
    36.     <dependency>
    37.         <groupId>org.springframework.boot</groupId>
    38.         <artifactId>spring-boot-starter-test</artifactId>
    39.         <scope>test</scope>
    40.     </dependency>
    41.  
    42.     <dependency>
    43.         <groupId>com.dameng</groupId>
    44.         <artifactId>Dm8JdbcDriver18</artifactId>
    45.         <version>8.1.1.49</version>
    46.     </dependency>
    47.     <dependency>
    48.         <groupId>com.oracle.database.jdbc</groupId>
    49.         <artifactId>ojdbc8</artifactId>
    50.         <scope>runtime</scope>
    51.     </dependency>
    52. </dependencies>
    53.  
    54. <build>
    55.     <plugins>
    56.         <plugin>
    57.             <groupId>org.springframework.boot</groupId>
    58.             <artifactId>spring-boot-maven-plugin</artifactId>
    59.             <configuration>
    60.                 <excludes>
    61.                     <exclude>
    62.                         <groupId>org.projectlombok</groupId>
    63.                         <artifactId>lombok</artifactId>
    64.                     </exclude>
    65.                 </excludes>
    66.             </configuration>
    67.         </plugin>
    68.     </plugins>
    69. </build>

    MybatisX逆向工程

    逆向工程：通过数据库表接口，逆向生成java工程结构

    实体类、mapper接口、mapper映射文件、Service接口、service实现类

    application.yml

    1. spring:
    2.   datasource:
    3.     dynamic:
    4.       primary:  #设置默认的数据源或者数据源组,默认值即为master
    5.       strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
    6.       datasource:
    7.         mysql1:
    8.           username: root
    9.           password: PWD
    10.           url: jdbc:mysql://IP:3306/mysql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8
    11.           driver-class-name: com.mysql.cj.jdbc.Driver
    12.           #          driver-class-name: com.mysql.jdbc.Driver
    13.         mysql2:
    14.           url: jdbc:mysql://IP:3306/sys?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8
    15.           username: root
    16.           password: PWD
    17.           driver-class-name: com.mysql.cj.jdbc.Driver
    18.         oracle:
    19.           username: system
    20.           password: PWD
    21.           url: jdbc:oracle:thin:@IP:1521:orcl
    22.           driver-class-name: oracle.jdbc.driver.OracleDriver
    23.           #          driver-class-name: com.mysql.jdbc.Driver
    24.         dm:
    25.           url: jdbc:dm://127.0.0.1:5236/demo?
    26.           username: SYSDBA
    27.           password: SYSDBA
    28.           driver-class-name: dm.jdbc.driver.DmDriver
    29.         #          driver-class-name: com.mysql.jdbc.Driver
    30.  
    31. server:
    32.   port: 8089
    33.  
    34. mybatis-plus:
    35.   configuration:
    36.     #输出日志
    37.     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    38.     #配置映射规则
    39.     map-underscore-to-camel-case: true #表示支持下划线到驼蜂的映射
    40.     #隐藏mybatis图标
    41.   global-config:
    42.     banner: false
    43.     db-config:
    44.       logic-delete-field: status
    45.       logic-not-delete-value: 1
    46.       logic-delete-value: 0

    spy.properties

    1. #3.2.1以上使用
    2. modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
    3. #3.2.1以下使用或者不配置
    4. #modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
    5. # 自定义日志打印
    6. logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
    7. #日志输出到控制台
    8. appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
    9. # 使用日志系统记录 sql
    10. #appender=com.p6spy.engine.spy.appender.Slf4JLogger
    11. # 设置 p6spy driver 代理
    12. deregisterdrivers=true
    13. # 取消JDBC URL前缀
    14. useprefix=true
    15. # 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
    16. excludecategories=info,debug,result,commit,resultset
    17. # 日期格式
    18. dateformat=yyyy-MM-dd HH:mm:ss
    19. # 实际驱动可多个
    20. #driverlist=org.h2.Driver
    21. # 是否开启慢SQL记录
    22. outagedetection=true
    23. # 慢SQL记录标准 2 秒
    24. outagedetectioninterval=2

    MybatisX逆向工程只是一个便携的工具，自定义接口和实现类还是要自己写

    mapper(dao层)

    1. @Mapper
    2. public interface UserMapper extends BaseMapper<User> {
    3. }

    1. @Mapper
    2. public interface TUserMapper extends BaseMapper<TUser> {
    3. }

    1. @Mapper
    2. public interface TestMapper extends BaseMapper<Test> {
    3.     List<Test> selectTestAll();
    4. }

    1. @Mapper
    2. public interface DmuserMapper extends BaseMapper<Dmuser> {
    3.     List selectDmUserAll();
    4.     Integer addDmUser(Dmuser dmuser);
    5. }

    service接口层

    1. public interface UserService extends IService<User> {
    2. }
    1. public interface TUserService extends IService<TUser> {
    2. }
    1. public interface TestService extends IService<Test> {
    2.     List<Test> selectTestAll();
    3. }
    1. public interface DmuserService extends IService<Dmuser> {
    2.     List selectDmUserAll();
    3.     Integer addDmUser(Dmuser dmuser);
    4. }

    mapper映射文件

    1. <mapper namespace="com.example.mapper.UserMapper">
    2.     <resultMap id="BaseResultMap" type="com.example.domain.User">
    3.             <id property="id" column="id" jdbcType="VARCHAR"/>
    4.             <result property="name" column="name" jdbcType="VARCHAR"/>
    5.             <result property="age" column="age" jdbcType="INTEGER"/>
    6.             <result property="email" column="email" jdbcType="VARCHAR"/>
    7.             <result property="status" column="status" jdbcType="INTEGER"/>
    8.             <result property="gender" column="gender" jdbcType="INTEGER"/>
    9.             <result property="contact" column="contact" jdbcType="VARCHAR"/>
    10.             <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
    11.             <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
    12.     </resultMap>
    13.  
    14.     <sql id="Base_Column_List">
    15.         id,name,age,
    16.         email,status,gender,
    17.         contact,create_time,update_time
    18.     </sql>
    19. </mapper>

    1. <mapper namespace="com.example.mapper.TUserMapper">
    2.  
    3.     <resultMap id="BaseResultMap" type="com.example.domain.TUser">
    4.             <id property="id" column="id" jdbcType="BIGINT"/>
    5.             <result property="name" column="name" jdbcType="VARCHAR"/>
    6.             <result property="age" column="age" jdbcType="INTEGER"/>
    7.             <result property="email" column="email" jdbcType="VARCHAR"/>
    8.     </resultMap>
    9.  
    10.     <sql id="Base_Column_List">
    11.         id,name,age,
    12.         email
    13.     </sql>
    14. </mapper>

    1. <mapper namespace="com.example.mapper.TestMapper">
    2.  
    3.     <resultMap id="BaseResultMap" type="com.example.domain.Test">
    4.             <id property="id" column="id" jdbcType="DECIMAL"/>
    5.             <result property="name" column="name" jdbcType="VARCHAR"/>
    6.     </resultMap>
    7.  
    8.     <sql id="Base_Column_List">
    9.         id,name
    10.     </sql>
    11.  
    12.     <select id="selectTestAll" resultType="com.example.domain.Test">select * from "test"</select>
    13. </mapper>

    1. <mapper namespace="com.example.mapper.DmuserMapper">
    2.  
    3.     <resultMap id="BaseResultMap" type="com.example.domain.Dmuser">
    4.             <id property="id" column="id" jdbcType="INTEGER"/>
    5.             <result property="name" column="name" jdbcType="CHAR"/>
    6.             <result property="address" column="address" jdbcType="CHAR"/>
    7.     </resultMap>
    8.  
    9.     <sql id="Base_Column_List">
    10.         id,name,address
    11.     </sql>
    12.     <select id="selectDmUserAll" resultType="com.example.domain.Dmuser">select "id","name","address"from "SYSDBA"."DMUser";</select>
    13.     <insert id="addDmUser">
    14.         insert into "SYSDBA"."DMUser"("id", "name", "address") VALUES(#{id},#{name},#{address});
    15.     </insert>
    16. </mapper>

    serviceimpl类

    1. @Service
    2. @DS("mysql2")
    3. public class UserServiceImpl extends ServiceImpl, User>
    4.     implements UserService{
    5. }
    1. @Service
    2. @DS("mysql1")
    3. public class TUserServiceImpl extends ServiceImpl, TUser>
    4.     implements TUserService{
    5. }

    1. @Service
    2. @DS("oracle")
    3. public class TestServiceImpl extends ServiceImpl, Test>
    4.     implements TestService{
    5.     @Autowired
    6.     private TestMapper testMapper;
    7.     @Override
    8.     public List<Test> selectTestAll() {
    9.         return testMapper.selectTestAll();
    10.     }
    11. }

    1. @Service
    2. @DS("dm")
    3. public class DmuserServiceImpl extends ServiceImpl, Dmuser>
    4.     implements DmuserService{
    5.     @Autowired
    6.     private DmuserMapper dmuserMapper;
    7.     @Override
    8.     public List<Dmuser> selectDmUserAll() {
    9.         return dmuserMapper.selectDmUserAll();
    10.     }
    11.     @Override
    12.     public Integer addDmUser(Dmuser dmuser) {
    13.         return dmuserMapper.addDmUser(dmuser);
    14.     }
    15. }

    测试类

    1. @SpringBootTest
    2. class DatasourcesApplicationTests {
    3.  
    4.     @Autowired
    5.     private TUserService tUserService;
    6.     @Autowired
    7.     private UserService userService;
    8.     @Autowired
    9.     private TestService testService;
    10.     @Autowired
    11.     private DmuserService dmuserService;
    12.  
    13.     @Test
    14.     void sys(){
    15.         userService.list();
    16.     }
    17.     @Test
    18.     void oracleDemo(){
    19.         testService.selectTestAll();
    20.     }
    21.     @Test
    22.     void dmDemo(){
    23.         dmuserService.selectDmUserAll();
    24.     }
    25.     @Test
    26.     void oracleToDMTest(){
    27.         Listexample.domain.Test> oracles =testService.selectTestAll();
    28.         for (com.example.domain.Test oracle : oracles) {
    29.             Dmuser dmuser =new Dmuser();
    30.             dmuser.setId(Math.toIntExact(oracle.getId()));
    31.             dmuser.setName(oracle.getName());
    32.             dmuser.setAddress("null");
    33.             dmuserService.addDmUser(dmuser);
    34.             System.out.println(""+oracle);
    35.         }
    36.     }
    37.     @Test
    38.     void mysqlToDMTest(){
    39.         List<TUser> mysqls =tUserService.list();
    40.         for (TUser mysql : mysqls) {
    41.             Dmuser dmuser =new Dmuser();
    42.             dmuser.setId(Math.toIntExact(mysql.getId()));
    43.             dmuser.setName(mysql.getName());
    44.             dmuser.setAddress(mysql.getEmail());
    45.             dmuserService.addDmUser(dmuser);
    46.             System.out.println(""+mysql);
    47.         }
    48.     }
    49.  
    50. }

    Mybatis-Plus介绍

    https://baomidou.com/

    为简化开发而生

    MyBatis-Plus(opens new window)（简称 MP）是一个 MyBatis (opens new window) 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

    特性

    • 无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑
    • 损耗小：启动即会自动注入基本 CURD，性能基本无损耗，直接面向对象操作
    • 强大的 CRUD 操作：内置通用 Mapper、通用 Service，仅仅通过少量配置即可实现单表大部分 CRUD 操作，更有强大的条件构造器，满足各类使用需求
    • 支持 Lambda 形式调用：通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错
    • 支持主键自动生成：支持多达 4 种主键策略（内含分布式唯一 ID 生成器 - Sequence），可自由配置，完美解决主键问题
    • 支持 ActiveRecord 模式：支持 ActiveRecord 形式调用，实体类只需继承 Model 类即可进行强大的 CRUD 操作
    • 支持自定义全局通用操作：支持全局通用方法注入（ Write once, use anywhere ）
    • 内置代码生成器：采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码，支持模板引擎，更有超多自定义配置等您来使用
    • 内置分页插件：基于 MyBatis 物理分页，开发者无需关心具体操作，配置好插件之后，写分页等同于普通 List 查询
    • 分页插件支持多种数据库：支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 等多种数据库
    • 内置性能分析插件：可输出 SQL 语句以及其执行时间，建议开发测试时启用该功能，能快速揪出慢查询
    • 内置全局拦截插件：提供全表 delete 、 update 操作智能分析阻断，也可自定义拦截规则，预防误操作
  • 相关阅读:
    五大架构风格
    使用.NET简单实现一个Redis的高性能克隆版（四、五）
    新学期，我的FLAG不能倒~
    【华为机试真题 JAVA】工号不够用了怎么办-100
    golang及beego框架单元测试小结
    Nginx转发丢失cookie表现形式以及解决方案
    SMART S7-200PLC串行自由口通讯（耐压测试仪）
    第05章 Tableau数据可视化
    Go语言开源13周年啦，看看负责人说了啥
    论文阅读笔记---Image Inpainting with Local and Global Refinement
  • 原文地址：https://blog.csdn.net/weixin_47268883/article/details/133549307