springboot 多数据源 实例（sybase、mysql数据库）（中）

接下来见证奇迹的时刻：

首先要借鉴yandype这位大神的总结的代码：

说明：

springboot2.3.x + mybatisplus + druid + sybase + mysql 多数据源整合

1、项目依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <!-- 阿里 druid数据源 -->
<!--        <dependency>-->
<!--            <groupId>com.alibaba</groupId>-->
<!--            <artifactId>druid</artifactId>-->
<!--            <version>1.1.0</version>-->
<!--        </dependency>-->
        <!-- 数据库JDBC3.0连接驱动 -->
        <dependency>
            <groupId>net.sourceforge.jtds</groupId>
            <artifactId>jtds</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-annotations</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>com.sybase</groupId>
            <artifactId>jdbc4.jdbc</artifactId>
            <version>4.0</version>
            <scope>system</scope>
            <systemPath>D:/sybase/jconn-4.0.0.jar</systemPath>
        </dependency>
        <!-- MapStruct实体映射依赖 -->
        <dependency>
            <groupId>org.mapstruct</groupId>
            <artifactId>mapstruct-jdk8</artifactId>
            <version>1.3.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.mapstruct</groupId>
            <artifactId>mapstruct-processor</artifactId>
            <version>1.3.0.Final</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

 

2、配置文件 application.yml

server:
  port: 10001

spring:
  datasource:
    dynamic:
      primary: mysql-adb #设置默认的数据源或者数据源组
      datasource:
        sybase-db:
          type: com.zaxxer.hikari.HikariDataSource
          driver-class-name: com.sybase.jdbc4.jdbc.SybDriver
          url: jdbc:sybase:Tds:127.0.0.1:3306/di?charset=cp936
          username: d
          password: sq
          hikari:
            minimum-idle: 5
            maximum-pool-size: 15
            auto-commit: true
            idle-timeout: 30000
            pool-name: DatebookHikariCP
            max-lifetime: 1800000
            connection-timeout: 30000
            connection-test-query: SELECT 1
        mysql-adb:
          type: com.zaxxer.hikari.HikariDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
          username: root
          password: root
          hikari:
            minimum-idle: 5
            maximum-pool-size: 15
            auto-commit: true
            idle-timeout: 30000
            pool-name: DatebookHikariCP
            max-lifetime: 1800000
            connection-timeout: 30000
            connection-test-query: SELECT 1
        mysql-bdb:
          type: com.zaxxer.hikari.HikariDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
          username: root
          password: root
          hikari:
            minimum-idle: 5
            maximum-pool-size: 15
            auto-commit: true
            idle-timeout: 30000
            pool-name: DatebookHikariCP
            max-lifetime: 1800000
            connection-timeout: 30000
            connection-test-query: SELECT 1

 

3、mybatisplus配置类

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;
 
@Configuration
@MapperScan("com.zhuyun.mapper")
public class MybatisPlusConfig {
}

4、com.yandype.mapper 包下mapper接口

4.1、sybase 数据源 mapper 定义

package com.yandype.mapper;
 
 
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
 
@DS("sybase-db")
public interface SybaseTestUserMapper extends BaseMapper<SybaseTestUser> {
}

4.2、SybaseTestUser entity实体类

package com.yandype.entity;
 
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
 
@Data
@TableName("TB_TEST_USER")
public class SybaseTestUser {
    @TableId("ID")
    private String id;
    @TableField("USERNAME")
    private String username;
    @TableField("PASSWORD")
    private String password;
    @TableField("NAME")
    private String name;
    @TableField("BZ")
    private String bz;
}

4.3、mysql 数据源 mapper 定义

package com.yandype.mapper;
 
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
// @DS 切换数据源 @DS 可以注解在方法上和类上，同时存在方法注解优先于类上注解。
@DS("mysql-db")
public interface MysqlTestUserAcMapper extends BaseMapper<MysqlTestUser> {
}

4.4、MysqlTestUser entity实体类

package com.yandype.entity;
import lombok.Data;
 
@Data
public class MysqlTestUser {
    private Long id;
    private String userName;
    private String userPhone;
}

4.5 service 数据源定义

package com.yandype.service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yandype.convert.TestUserConvert;
import com.yandype.entity.ProductionQueue;
import com.yandype.entity.SybaseTestUser;
import com.yandype.entity.TestUser;
import com.yandype.mapper.ProductionQueueMapper;
import com.yandype.mapper.SybaseTestUserMapper;
import com.yandype.mapper.TestUserAdbMapper;
import com.yandype.mapper.TestUserBdbMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Slf4j
@Service
public class TestUserService extends ServiceImpl<TestUserAdbMapper, TestUser> {

    @Autowired
    private TestUserBdbMapper testUserBdbMapper;
    @Autowired
    SybaseTestUserMapper sybaseTestUserMapper;
    @Autowired
    ProductionQueueMapper productionQueueMapper;

    /**
     * 同步mysql b数据库数据到 mysql a数据库
     */
    public void syncBdbData2Adb(){
        List<TestUser> bdbUsers = testUserBdbMapper.selectList(null);
        log.info("查询Bdb 3383 数据库，\n users={}",bdbUsers);
        boolean adbBool = this.saveOrUpdateBatch(bdbUsers);
        log.info("同步Bdb 数据 到Adb 3382 数据库，\n 同步结果={}",adbBool);
    }
    public void syncSybasedb(){
        List<ProductionQueue> productionQueues = productionQueueMapper.selectList(null);
        log.info("查询sybase 数据库，\n ProductionQueue={}",productionQueues);
    }

    /**
     * 同步sybase 数据库数据到 mysql a数据库
     */
    public void syncSybaseDbData2Adb(){
        SybaseTestUser sybaseTestUser = new SybaseTestUser();
        sybaseTestUser.setUserName("赵");
        sybaseTestUser.setId("125");
        sybaseTestUser.setPassword("赵111");
        int insert = sybaseTestUserMapper.insert(sybaseTestUser);
        log.info("插入Sybase 数据库数据："+insert);
        List<SybaseTestUser> sybaseDbUsers = sybaseTestUserMapper.selectList(null);
        log.info("查询sybase 数据库，\n sybaseDbUsers={}",sybaseDbUsers);
        // 数据对象转换
        List<TestUser> testUsers = TestUserConvert.INSTANCES.sybaseTestUsers2TestUsers(sybaseDbUsers);
        log.info("将sybase 数据库数据转换mysql数据库对象模型，\n 转换结果={}",testUsers);
        boolean adbBool = this.saveOrUpdateBatch(testUsers);
        log.info("同步sybase 数据库数据 到Adb 3382 数据库，\n 同步结果={}",adbBool);
    }
}

单元测试

package com.yandype.mapper;
 
import org.springframework.boot.test.context.SpringBootTest;
import static org.junit.jupiter.api.Assertions.*;
 
@SpringBootTest
public class TestUserAcMapperTest{
 
    @Autowired
    private SybaseTestUserMapper sybaseTestUserMapper;
     @Autowired
    private MysqlTestUserMapper mysqlTestUserMapper;
    
    // sybase 数据源测试
    @Test
    public void sybaseSelectTest(){
        List<SybaseTestUser> userList = sybaseTestUserMapper.selectList(null);
        System.out.println(userList);
    }
    
    // mysql 数据源测试
    @Test
    public void mysqlSelectTest(){
        List<MysqlTestUser> userList = mysqlTestUserMapper.selectList(null);
        System.out.println(userList);
    }

}

 

完整项目代码已提交到码云仓库：

 

 

参考资料：

mybatis-plus官网文档多数据源配置：

https://baomidou.com/guide/dynamic-datasource.html
注意事项 ：

 数据库JDBC3.0连接驱动（net.sourceforge.jtds）

适合sybase 11 -15 版本的数据库

服务启动没问题 第一次查询查询数据为空。再次请求接口 数据时报错如下：

### Error querying database.  Cause: java.sql.SQLException: SQL Anywhere Error -110: Item 'jtds000001' already exists

最终经过八十一难 才找到 ：

 

 

 那这怎么解决呢........................?

找度娘 度爹 甚至 不惜重金 用上了chatgpt ，我也记不得问多少次了。最终找到了解决办法!

修改配置文件如下：

首先下载 jconn4.jar ,地址：

链接：https://pan.baidu.com/s/1q9GdZZqVc6Lxtp2DY_OkEQ?pwd=5211
提取码：5211

在pom 引入本地 jar 

 <dependency>
            <groupId>com.sybase</groupId>
            <artifactId>jconn4</artifactId>
            <version>4.0.0</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/jconn-4.0.0.jar</systemPath>
        </dependency>

如图：

修改配置文件如下：

spring:
  datasource:
    dynamic:
      primary: mysql-adb #设置默认的数据源或者数据源组
      datasource:
        sybase-db:
        #  type: com.alibaba.druid.pool.DruidDataSource
        #  driver-class-name: net.sourceforge.jtds.jdbc.Driver
        #  url: jdbc:jtds:sybase://192.168.70.71:2638/disa_cim_160021
          type: com.zaxxer.hikari.HikariDataSource
          driver-class-name: com.sybase.jdbc4.jdbc.SybDriver
          url: jdbc:sybase:Tds:127.0.0.1:5000/sss;charset=cp936
          username: dba
          password: sql
        mysql-adb:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3307/aaa?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
          username: root
          password: 123
        mysql-bdb:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3307/aaa?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
          username: root
          password: 123
  druid:
    # 连接池的配置信息
    # 初始化大小，最小，最大
    initial-size: 5
    min-idle: 5
    maxActive: 20
    # 配置获取连接等待超时的时间
    maxWait: 60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false

到此终于大功告成！有什么问题欢迎留言，希望能帮到你！奋青们

截图如下：

注意：

假如分两个service 最好在需要在service 层 的实现类 和 mapper 层 都要加上  @DS("XXX")