devbox
笔触狂放9
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

starRocks配置两个数据源的多个IP进行访问数据库查询_starrocks jdbc

作者：笔触狂放9 | 2024-05-16 15:55:13

赞

踩

starrocks jdbc

1、yml文件配置

注意mysql为master

  1.   datasource:
  2.     mysql:
  3.       master:
  4.         type: com.alibaba.druid.pool.DruidDataSource
  5.         driver-class-name: com.mysql.cj.jdbc.Driver
  6.         url: jdbc:mysql://192.168.110.1222:3306/sdkjoy-db?characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai
  7.         username: root
  8.         password: 
  9.     starrocks:
  10.       type: com.alibaba.druid.pool.DruidDataSource
  11.       driver-class-name: com.mysql.cj.jdbc.Driver
  12.       url: jdbc:mysql:loadbalance://192.168.110.1111:9030,192.168.110.1222:9030,192.168.110.1333:9030?useUnicode=true&autoReconnect=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
  13.       addr: 192.168.110.1111:9030,192.168.110.12222:9030,192.168.110.1333:9030
  14.       http:
  15.         head: http://
  16.         port: 8030
  17.       username: 
  18.       password:

2.config文件指定StarRocks的IP

因为mysql给定了定时任务，项目启动必须访问要以mysql为master

  1. package com.skyable.monitor.config.datasource;
  2.  
  3. import com.alibaba.druid.pool.DruidDataSource;
  4. import com.skyable.monitor.mapper.mysql.ClusterMapper;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.stereotype.Component;
  7.  
  8. import java.util.ArrayList;
  9. import java.util.HashMap;
  10. import java.util.List;
  11. import java.util.Objects;
  12.  
  13. /**
  14.  * Description:
  15.  *
  16.  * @author 
  17.  * @date: 2023-02-21-13:55
  18.  */
  19. @Component
  20. public class StarrocksDatasource {
  21.   @Autowired private DataSourceUtil dataSourceUtil;
  22.   @Autowired private ClusterMapper clusterMapper;
  23.   /**
  24.    * 连接starRocks数据源
  25.    *
  26.    * @param clusterId
  27.    * @return
  28.    */
  29.   public Boolean datasource(Integer clusterId) {
  30.     HashMap<String, String> map = new HashMap(4);
  31.     List<String> addr = clusterMapper.selectIp(clusterId);
  32.     List<String> result = new ArrayList<>();
  33.     for (String s : addr) {
  34.       StringBuilder stringBuilder = new StringBuilder();
  35.       StringBuilder append = stringBuilder.append(s).append(":").append("9030");
  36.       result.add(append.toString());
  37.     }
  38.     String ip = String.join(",", result);
  39.     map.put("type", "com.alibaba.druid.pool.DruidDataSource");
  40.     map.put("driver-class-name", "com.mysql.cj.jdbc.Driver");
  41.     map.put(
  42.         "url",
  43.         "jdbc:mysql:loadbalance://"
  44.             + ip
  45.             + "?useUnicode=true&autoReconnect=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai");
  46.     map.put("addr", ip);
  47.     map.put("username", "root");
  48.     map.put("password", "");
  49.     DataSourceInfo dataSourceInfo = new DataSourceInfo();
  50.     dataSourceInfo.setUrl(map.get("url"));
  51.     dataSourceInfo.setDatasourceKey(String.valueOf(clusterId));
  52.     dataSourceInfo.setUserName(map.get("username"));
  53.     dataSourceInfo.setPassword(map.get("password"));
  54.     DruidDataSource druidDataSource = dataSourceUtil.createDataSourceConnection(dataSourceInfo);
  55.     if (Objects.nonNull(druidDataSource)) {
  56.       dataSourceUtil.addDefineDynamicDataSource(druidDataSource, dataSourceInfo.getDatasourceKey());
  57.       DynamicDataSourceHolder.setDynamicDataSourceKey(String.valueOf(clusterId));
  58.     }
  59.     return true;
  60.   }
  61. }
  1. package com.skyable.monitor.config.datasource;
  2.  
  3. import org.springframework.beans.factory.InitializingBean;
  4. import org.springframework.jdbc.datasource.AbstractDataSource;
  5. import org.springframework.jdbc.datasource.lookup.DataSourceLookup;
  6. import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;
  7. import org.springframework.lang.Nullable;
  8. import org.springframework.util.Assert;
  9. import org.springframework.util.CollectionUtils;
  10.  
  11. import javax.sql.DataSource;
  12. import java.sql.Connection;
  13. import java.sql.SQLException;
  14. import java.util.Map;
  15.  
  16. public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
  17.         //目标数据源map集合，存储将要切换的多数据源bean信息
  18.         @Nullable
  19.         private Map<Object, Object> targetDataSources;
  20.         //未指定数据源时的默认数据源对象
  21.         @Nullable
  22.         private Object defaultTargetDataSource;
  23.         private boolean lenientFallback = true;
  24.         //数据源查找接口，通过该接口的getDataSource(String dataSourceName)获取数据源信息
  25.         private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
  26.         //解析targetDataSources之后的DataSource的map集合
  27.         @Nullable
  28.         private Map<Object, DataSource> resolvedDataSources;
  29.         @Nullable
  30.         private DataSource resolvedDefaultDataSource;
  31.  
  32.         @Override
  33.         //将targetDataSources的内容转化一下放到resolvedDataSources中，将defaultTargetDataSource转为DataSource赋值给resolvedDefaultDataSource
  34.         public void afterPropertiesSet() {
  35.             //如果目标数据源为空，会抛出异常，在系统配置时应至少传入一个数据源
  36.             if (this.targetDataSources == null) {
  37.                 throw new IllegalArgumentException("Property 'targetDataSources' is required");
  38.             } else {
  39.                 //初始化resolvedDataSources的大小
  40.                 this.resolvedDataSources = CollectionUtils.newHashMap(this.targetDataSources.size());
  41.                 //遍历目标数据源信息map集合，对其中的key，value进行解析
  42.                 this.targetDataSources.forEach((key, value) -> {
  43.                     //resolveSpecifiedLookupKey方法没有做任何处理，只是将key继续返回
  44.                     Object lookupKey = this.resolveSpecifiedLookupKey(key);
  45.                     //将目标数据源map集合中的value值（德鲁伊数据源信息）转为DataSource类型
  46.                     DataSource dataSource = this.resolveSpecifiedDataSource(value);
  47.                     //将解析之后的key，value放入resolvedDataSources集合中
  48.                     this.resolvedDataSources.put(lookupKey, dataSource);
  49.                 });
  50.                 if (this.defaultTargetDataSource != null) {
  51.                     //将默认目标数据源信息解析并赋值给resolvedDefaultDataSource
  52.                     this.resolvedDefaultDataSource = this.resolveSpecifiedDataSource(this.defaultTargetDataSource);
  53.                 }
  54.     
  55.             }
  56.         }
  57.     
  58.         protected Object resolveSpecifiedLookupKey(Object lookupKey) {
  59.             return lookupKey;
  60.         }
  61.     
  62.         protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
  63.             if (dataSource instanceof DataSource) {
  64.                 return (DataSource)dataSource;
  65.             } else if (dataSource instanceof String) {
  66.                 return this.dataSourceLookup.getDataSource((String)dataSource);
  67.             } else {
  68.                 throw new IllegalArgumentException("Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
  69.             }
  70.         }
  71.         @Override
  72.         //因为AbstractRoutingDataSource继承AbstractDataSource，而AbstractDataSource实现了DataSource接口，所有存在获取数据源连接的方法
  73.         public Connection getConnection() throws SQLException {
  74.             return this.determineTargetDataSource().getConnection();
  75.         }
  76.         @Override
  77.         public Connection getConnection(String username, String password) throws SQLException {
  78.             return this.determineTargetDataSource().getConnection(username, password);
  79.         }
  80.     
  81.         protected DataSource determineTargetDataSource() {
  82.             Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
  83.             //调用实现类中重写的determineCurrentLookupKey方法拿到当前线程要使用的数据源的名称
  84.             Object lookupKey = this.determineCurrentLookupKey();
  85.             //去解析之后的数据源信息集合中查询该数据源是否存在，如果没有拿到则使用默认数据源resolvedDefaultDataSource
  86.             DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);
  87.             if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
  88.                 dataSource = this.resolvedDefaultDataSource;
  89.             }
  90.     
  91.             if (dataSource == null) {
  92.                 throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
  93.             } else {
  94.                 return dataSource;
  95.             }
  96.         }
  97.     
  98.         @Nullable
  99.         protected abstract Object determineCurrentLookupKey();
  100.     }

  1. package com.skyable.monitor.config.datasource;
  2.  
  3. /**
  4.  * @Description:   动态数据源常量类
  5.  *
  6.  */
  7. public class CommonConstant {
  8.     /**
  9.      * 默认数据源标识
  10.      */
  11.     public static final String MASTER = "master";
  12. }
  1. package com.skyable.monitor.config.datasource;
  2.  
  3. import lombok.Data;
  4.  
  5. @Data
  6. public class DataSourceInfo {
  7.     private String url;
  8.     private String userName;
  9.     private String password;
  10.     private String datasourceKey;
  11. }
  1. package com.skyable.monitor.config.datasource;
  2.  
  3. import com.alibaba.druid.pool.DruidDataSource;
  4. import lombok.extern.slf4j.Slf4j;
  5. import org.springframework.stereotype.Component;
  6.  
  7. import javax.annotation.Resource;
  8. import java.sql.SQLException;
  9. import java.util.Map;
  10.  
  11. /**
  12.  * @Description:   数据源工具类
  13.  * @Date 2022/8/18 17:20
  14.  *
  15.  */
  16. @Slf4j
  17. @Component
  18. public class DataSourceUtil {
  19.  
  20.     @Resource
  21.     DynamicDataSource dynamicDataSource;
  22.  
  23.     /**
  24.      * @Description: 根据传递的数据源信息测试数据库连接
  25.      * @Author zhangyu
  26.      */
  27.     public DruidDataSource createDataSourceConnection(DataSourceInfo dataSourceInfo) {
  28.         DruidDataSource druidDataSource = new DruidDataSource();
  29.         druidDataSource.setUrl(dataSourceInfo.getUrl());
  30.         druidDataSource.setUsername(dataSourceInfo.getUserName());
  31.         druidDataSource.setPassword(dataSourceInfo.getPassword());
  32.         druidDataSource.setBreakAfterAcquireFailure(true);
  33.         druidDataSource.setConnectionErrorRetryAttempts(0);
  34.         try {
  35.             druidDataSource.getConnection(2000);
  36.             log.info("数据源连接成功");
  37.             return druidDataSource;
  38.         } catch (SQLException throwables) {
  39.             log.error("数据源 {} 连接失败,用户名：{}，密码 {}",dataSourceInfo.getUrl(),dataSourceInfo.getUserName(),dataSourceInfo.getPassword());
  40.             return null;
  41.         }
  42.     }
  43.  
  44.     /**
  45.      * @Description: 将新增的数据源加入到备份数据源map中
  46.      * @Author zhangyu
  47.      */
  48.     public void addDefineDynamicDataSource(DruidDataSource druidDataSource, String dataSourceName){
  49.         Map<Object, Object> defineTargetDataSources = dynamicDataSource.getDefineTargetDataSources();
  50.         defineTargetDataSources.put(dataSourceName, druidDataSource);
  51.         dynamicDataSource.setTargetDataSources(defineTargetDataSources);
  52.         dynamicDataSource.afterPropertiesSet();
  53.     }
  54. }
  1. package com.skyable.monitor.config.datasource;
  2.  
  3. import com.alibaba.druid.pool.DruidDataSource;
  4. import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
  5. import org.mybatis.spring.annotation.MapperScan;
  6. import org.springframework.boot.context.properties.ConfigurationProperties;
  7. import org.springframework.context.annotation.Bean;
  8. import org.springframework.context.annotation.Configuration;
  9. import org.springframework.context.annotation.Primary;
  10.  
  11. import javax.sql.DataSource;
  12. import java.util.HashMap;
  13. import java.util.Map;
  14.  
  15.  
  16. @Configuration
  17. @MapperScan("com.skyable.monitor.mapper")
  18. public class DruidConfig {
  19.  
  20.     @Bean(name = CommonConstant.MASTER)
  21.     @ConfigurationProperties("spring.datasource.mysql.master")
  22.     public DruidDataSource masterDataSource()
  23.     {
  24.         DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
  25.         return dataSource;
  26.     }
  27.  
  28.     @Bean(name = "starrocksDataSource")
  29.     @ConfigurationProperties("spring.datasource.starrocks")
  30.     public DruidDataSource deafultStarRocksDataSource()
  31.     {
  32.         DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
  33.         return dataSource;
  34.     }
  35.  
  36.     @Bean
  37.     @Primary
  38.     public DynamicDataSource dynamicDataSource()
  39.     {
  40.         Map<Object, Object> dataSourceMap = new HashMap<>(2);
  41.         dataSourceMap.put(CommonConstant.MASTER,masterDataSource());
  42.         //设置动态数据源
  43.         DynamicDataSource dynamicDataSource = new DynamicDataSource();
  44.         dynamicDataSource.setDefaultTargetDataSource(masterDataSource());
  45.         dynamicDataSource.setTargetDataSources(dataSourceMap);
  46.         //将数据源信息备份在defineTargetDataSources中
  47.         dynamicDataSource.setDefineTargetDataSources(dataSourceMap);
  48.         return dynamicDataSource;
  49.     }
  50. }
  1. package com.skyable.monitor.config.datasource;
  2.  
  3. import lombok.AllArgsConstructor;
  4. import lombok.Data;
  5. import lombok.NoArgsConstructor;
  6. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
  7.  
  8. import java.util.Map;
  9.  
  10. /**
  11.  * 动态数据源
  12.  * 调用AddDefineDataSource组件的addDefineDynamicDataSource（）方法，获取原来targetdatasources的map，并将新的数据源信息添加到map中，并替换targetdatasources中的map
  13.  * 切换数据源时可以使用@DataSource(value = "数据源名称")，或者DynamicDataSourceContextHolder.setContextKey("数据源名称")
  14.  */
  15. @Data
  16. @AllArgsConstructor
  17. @NoArgsConstructor
  18. public class DynamicDataSource extends AbstractRoutingDataSource {
  19.     //备份所有数据源信息，
  20.     private Map<Object, Object> defineTargetDataSources;
  21.  
  22.     @Override
  23.     protected Object determineCurrentLookupKey() {
  24.         return DynamicDataSourceHolder.getDynamicDataSourceKey();
  25.     }
  26. }

  1. package com.skyable.monitor.config.datasource;
  2.  
  3. import lombok.extern.slf4j.Slf4j;
  4.  
  5. /**
  6.  * 数据源切换处理
  7.  *
  8.  * @author zhangyu
  9.  */
  10. @Slf4j
  11. public class DynamicDataSourceHolder {
  12.     /**
  13.      * 保存动态数据源名称
  14.      */
  15.     private static final ThreadLocal<String> DYNAMIC_DATASOURCE_KEY = new ThreadLocal<>();
  16.  
  17.     /**
  18.      * 设置/切换数据源，决定当前线程使用哪个数据源
  19.      */
  20.     public static void setDynamicDataSourceKey(String key){
  21.         log.info("数据源切换为：{}",key);
  22.         DYNAMIC_DATASOURCE_KEY.set(key);
  23.     }
  24.  
  25.     /**
  26.      * 获取动态数据源名称，默认使用mater数据源
  27.      */
  28.     public static String getDynamicDataSourceKey(){
  29.         String key = DYNAMIC_DATASOURCE_KEY.get();
  30.         return key == null ? CommonConstant.MASTER : key;
  31.     }
  32.  
  33.     /**
  34.      * 移除当前数据源
  35.      */
  36.     public static void removeDynamicDataSourceKey(){
  37.         log.info("移除数据源：{}",DYNAMIC_DATASOURCE_KEY.get());
  38.         DYNAMIC_DATASOURCE_KEY.remove();
  39.     }
  40. }
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/579500
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号