获取配置的shardingsphere分库分表的数据库链接，并连接每个分库分表执行数据归档逻辑

bxp1321 bxp1321 博客等级 墨衍会员 · AI 创作全网分发 墨衍智能分发 本文由作者通过墨衍一键同步至各平台 1分发平台 800+累计阅读 885平均单平台 已同步平台 优快云 微信公众号 微博 知乎 掘金 百家号 博客园 抖音 小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网 我也要 码龄17年
关注
原创 2024-10-10 17:21:36 885 阅读 AI 写同款· GEO 优化 ›

前言

随着分库分表的数据量越来越大，一些无用的数据，比如取消的订单需要删除以减少数据库成本(特殊场景，95%的订单都是已取消的无效订单)。但是shardingsphere自带的查询需要分片键才能出查询对应的分库分表。做数据归档删除的时候没有分片键，直接查询会查询所有的分库分表，性能较差。

shardingsphere配置信息

spring:
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql.show: true
    # 配置数据源
    datasource:
      names: ds1,ds2,ds3,ds4,ds5,ds6,ds7,ds8,ds9,ds10,ds11,ds12,ds13,ds14,ds15,ds16
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_3?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds4:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_4?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds5:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_5?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds6:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_6?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds7:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_7?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds8:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_8?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds9:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_9?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds10:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_10?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds11:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_11?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds12:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_12?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds13:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_13?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds14:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_14?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds15:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_15?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds16:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_16?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true

引入的包

        <!-- 依赖sharding 分库分表 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-core-common</artifactId>
            <version>4.1.1</version>
        </dependency>

ShardingSphereDataSourceConfig配置类，获取分库分表的配置信息



import com.alibaba.fastjson.JSON;
import lombok.Data;
import org.apache.shardingsphere.spring.boot.util.PropertyUtil;
import org.apache.shardingsphere.underlying.common.config.inline.InlineExpressionParser;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.core.env.StandardEnvironment;

import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Configuration
public class ShardingSphereDataSourceConfig  implements EnvironmentAware {
    private List<String> names;
    private Map<String, DataSourceProperties> datasource=new HashMap<>();


    public Map<String, DataSourceProperties> getDatasource() {
        return datasource;
    }

    public List<String> getNames() {
        return names;
    }

    @Override
    public void setEnvironment(Environment environment) {
        String prefix = "spring.shardingsphere.datasource.";
        names=this.getDataSourceNames(environment, prefix);
        for (String dataSourceName:
                names) {
            Map<String, Object> dataSourceProps = (Map) PropertyUtil.handle(environment, prefix + dataSourceName.trim(), Map.class);
            DataSourceProperties dataSourceProperties= JSON.parseObject(JSON.toJSONString(dataSourceProps),DataSourceProperties.class);
            datasource.put(dataSourceName,dataSourceProperties);
        }
    }
    private List<String> getDataSourceNames(Environment environment, String prefix) {
        StandardEnvironment standardEnv = (StandardEnvironment)environment;
        standardEnv.setIgnoreUnresolvableNestedPlaceholders(true);
        return null == standardEnv.getProperty(prefix + "name") ? (new InlineExpressionParser(standardEnv.getProperty(prefix + "names"))).splitAndEvaluate() : Collections.singletonList(standardEnv.getProperty(prefix + "name"));
    }
    // 内部类来封装DataSource的配置
    public static class DataSourceProperties {
        private String type;
        private String driverClassName;
        private String url;
        private String username;
        private String password;
        private DruidProperties druid;

        // Getter and Setter
        public String getType() {
            return type;
        }

        public void setType(String type) {
            this.type = type;
        }

        public String getDriverClassName() {
            return driverClassName;
        }

        public void setDriverClassName(String driverClassName) {
            this.driverClassName = driverClassName;
        }

        public String getUrl() {
            return url;
        }

        public void setUrl(String url) {
            this.url = url;
        }

        public String getUsername() {
            return username;
        }

        public void setUsername(String username) {
            this.username = username;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }

        public DruidProperties getDruid() {
            return druid;
        }

        public void setDruid(DruidProperties druid) {
            this.druid = druid;
        }

        // 内部类来封装Druid的配置
        public static class DruidProperties {
            private int initialSize;
            private int maxActive;
            private int minIdle;
            private int maxWait;
            private long minEvictableIdleTimeMillis;
            private boolean useGlobalDataSourceStat;
            private long timeBetweenEvictionRunsMillis;
            private String validationQuery;
            private boolean testOnBorrow;
            private boolean testOnReturn;
            private boolean testWhileIdle;

            // Getter and Setter
            public int getInitialSize() {
                return initialSize;
            }

            public void setInitialSize(int initialSize) {
                this.initialSize = initialSize;
            }

            public int getMaxActive() {
                return maxActive;
            }

            public void setMaxActive(int maxActive) {
                this.maxActive = maxActive;
            }

            public int getMinIdle() {
                return minIdle;
            }

            public void setMinIdle(int minIdle) {
                this.minIdle = minIdle;
            }

            public int getMaxWait() {
                return maxWait;
            }

            public void setMaxWait(int maxWait) {
                this.maxWait = maxWait;
            }

            public long getMinEvictableIdleTimeMillis() {
                return minEvictableIdleTimeMillis;
            }

            public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) {
                this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
            }

            public boolean isUseGlobalDataSourceStat() {
                return useGlobalDataSourceStat;
            }

            public void setUseGlobalDataSourceStat(boolean useGlobalDataSourceStat) {
                this.useGlobalDataSourceStat = useGlobalDataSourceStat;
            }

            public long getTimeBetweenEvictionRunsMillis() {
                return timeBetweenEvictionRunsMillis;
            }

            public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {
                this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
            }

            public String getValidationQuery() {
                return validationQuery;
            }

            public void setValidationQuery(String validationQuery) {
                this.validationQuery = validationQuery;
            }

            public boolean isTestOnBorrow() {
                return testOnBorrow;
            }

            public void setTestOnBorrow(boolean testOnBorrow) {
                this.testOnBorrow = testOnBorrow;
            }

            public boolean isTestOnReturn() {
                return testOnReturn;
            }

            public void setTestOnReturn(boolean testOnReturn) {
                this.testOnReturn = testOnReturn;
            }

            public boolean isTestWhileIdle() {
                return testWhileIdle;
            }

            public void setTestWhileIdle(boolean testWhileIdle) {
                this.testWhileIdle = testWhileIdle;
            }
        }
    }
}

核心代码

@Autowired
    ShardingSphereDataSourceConfig shardingSphereDataSourceConfig;

    //每次执行每个库删除一次
    @Override
    public BaseResponse deleteHis() {
        for (String dataSourceName :
                shardingSphereDataSourceConfig.getNames()) {
            ShardingSphereDataSourceConfig.DataSourceProperties dataSourceProperties = shardingSphereDataSourceConfig.getDatasource().get(dataSourceName);
            try (Connection conn = DriverManager.getConnection(dataSourceProperties.getUrl(), dataSourceProperties.getUsername(), dataSourceProperties.getPassword());
                 Statement stmt = conn.createStatement();) {
                for (int i = 1; i < 16; i++) {
                    String sql = "SELECT order_id FROM order_info_" + i + " WHERE status =10   limit 10" ;
                    ResultSet rs = stmt.executeQuery(sql);
                    StringBuilder sb = new StringBuilder();
                    while (rs.next()) {
                        Long orderId = rs.getLong(1);
                        sb.append(",").append(orderId);
                    }
                    if (sb.length() == 0) {
                        log.info("deleteOrderInfoHis dataSourceName:{},order_info_{},no data need delete", dataSourceName, i);
                        continue;
                    }
                    String orderIds = sb.substring(1);
                    String sqlDeleteOrderInfo = "delete from order_info_" + i + " WHERE order_id in (" + orderIds + ")";
                    stmt.executeUpdate(sqlDeleteOrderInfo);
                    log.info("deleteOrderInfoHis dataSourceName:{},order_info_{},deleteOrderInfoCount:{},orderIds:{}", dataSourceName, i, deleteOrderInfoCount,  orderIds);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
                log.error(ex.getMessage(), ex);
                return ResponseUtils.fail(ex.getMessage());
            } finally {

            }
        }
        return ResponseUtils.success();

    }

总结

这里重点是获取配置文件中分库分表的配置。然后调用服务的时候拿到链接配置遍历链接和分表，实现每个分库分表的查询和数据的处理。

标签
#数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
上一篇： python爬取网页接口数据，以yearning为例
下一篇： python查询日志，并组装sql，修复缺失的数据
点赞 3
评论 0
书签 书签 2
bxp1321
bxp1321
博客等级 码龄17年 146粉丝 86原创
+ 关注
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值