dynamic-datasource + Greenplum + MySQL 多数据源配置

布满荆棘的人生 2024-03-30 12:20 67阅读 0赞

<dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
                <version>3.1.0</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>8.0.18</scope>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
    
            <dependency>
                <groupId>org.postgresql</groupId>
                <artifactId>postgresql</artifactId>
                <version>42.5.1</version>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.1.2</version>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.24</version>
                <optional>true</optional>
            </dependency>
    
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.14</version>
            </dependency>
        </dependencies>

#### **配置文件** ####

spring:
      autoconfigure:
        # 排除 Druid 自动配置
        exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
      datasource:
        dynamic:
          # 设置默认的数据源或者数据源组,默认值即为master
          primary: mysql
          # 严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
          strict: false
          datasource:
            mysql:
              driverClassName: com.mysql.cj.jdbc.Driver
              url: jdbc:mysql://localhsot:3306/acquisition?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
              username: program
              password: z#Mshkk5
            greenplum:
              driverClassName: org.postgresql.Driver
              url: jdbc:postgresql://localhsot:5432/scada
              username: gpadmin
              password: 123456
    
    mybatis-plus:
      mapper-locations: classpath:/mapper/**/*Mapper.xml
      typeAliasesPackage: com.yang.**.entity
      configuration:
        mapUnderscoreToCamelCase: true
      global-config:
        db-config:
          db-column-underline: true

#### 应用代码 ####

使用 **@DS** 切换数据源。

**@DS** 可以注解在方法上或类上，**同时存在就近原则 方法上注解 优先于 类上注解QL**

@Service
    @DS("greenplum")
    public class ScadaDataServiceImpl extends ServiceImpl<ScadaDataMapper, ScadaData> implements ScadaDataService {
    
        @Override
        public List<ScadaData> pageQuery() {
            List<String> code = baseMapper.getDeviceCode();
            code.forEach(System.out::println);
    
            String deviceCode = "D202210180064";
            return list(Wrappers.<ScadaData>lambdaQuery()
                    .eq(ScadaData::getDeviceCode, deviceCode));
        }
    }

@Mapper
    public interface ScadaDataMapper extends BaseMapper<ScadaData> {
    
        List<String> getDeviceCode();
    }

<?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.yang.mapper.ScadaDataMapper">
    
        <select id="getDeviceCode" resultType="java.lang.String">
            SELECT
                DISTINCT
                device_code
            FROM t_scada_data
        </select>
    </mapper>

@Service
    public class AlarmActionServiceImpl extends ServiceImpl<AlarmActionMapper, AlarmAction> implements AlarmActionService {
    
        private final ScadaDataService scadaDataService;
    
        @Autowired
        public AlarmActionServiceImpl(ScadaDataService scadaDataService) {
            this.scadaDataService = scadaDataService;
        }
    
        @Override
        public void getData() {
            List<ScadaData> scadaData = scadaDataService.pageQuery();
            List<AlarmAction> list = list();
    
            System.out.println(list.size());
            System.out.println(scadaData.size());
        }
    }

#### @DS注解失效常见问题 ####

关于事务：[Mybatis plus的多数据源@DS切换为什么不起作用了，谁的锅，@Transactional\_zmyHow的博客-CSDN博客\_mybatisplus@ds导致事务失败][Mybatis plus_DS_Transactional_zmyHow_-CSDN_mybatisplus_ds]

Shiro框架：[@DS数据源未匹配成功,不管如何设置都是使用默认的primary的库 · Issue \#44 · baomidou/dynamic-datasource-spring-boot-starter · GitHub][DS_primary_ _ Issue _44 _ baomidou_dynamic-datasource-spring-boot-starter _ GitHub]

[mybatis-plus 多数据源无效问题(springboot + shiro)\_容@易的博客-CSDN博客\_basemapper 数据源不生效][mybatis-plus _springboot _ shiro_-CSDN_basemapper]

官方参考文档：

[多数据源 | MyBatis-Plus][_ MyBatis-Plus]

[Mybatis plus_DS_Transactional_zmyHow_-CSDN_mybatisplus_ds]: https://blog.csdn.net/weixin_43846916/article/details/110824726
[DS_primary_ _ Issue _44 _ baomidou_dynamic-datasource-spring-boot-starter _ GitHub]: https://github.com/baomidou/dynamic-datasource-spring-boot-starter/issues/44
[mybatis-plus _springboot _ shiro_-CSDN_basemapper]: https://blog.csdn.net/weixin_43695211/article/details/118404546
[_ MyBatis-Plus]: https://baomidou.com/pages/a61e1b/#%E6%96%87%E6%A1%A3-documentation