springboot2.x shardingjdbc单库分表_sharding-jdbc-config-spring-CSDN博客

本文链接：https://blog.csdn.net/hhy107107/article/details/90720697

捣鼓了大半天… 终于成功了，亲测可用…

注意依赖版本，我这里的spring boot 是2.x的。如果是1.x就不会有后面的那么多事了（就是不乐意用1.x，所以捣鼓了很久）。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
</dependency>
<dependency>
    <groupId>io.shardingjdbc</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>2.0.3</version>
</dependency>
<dependency>
    <groupId>com.dangdang</groupId>
    <artifactId>sharding-jdbc-config-spring</artifactId>
    <version>1.5.4.1</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.17</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.1.5.RELEASE</version>
</dependency>

一开始，是按照大部分教程，这么配置的

#sharding.jdbc.datasource.ds-master.type=com.alibaba.druid.pool.DruidDataSource
#sharding.jdbc.datasource.ds-master.driver-class-name=com.mysql.jdbc.Driver
#sharding.jdbc.datasource.ds-master.url=jdbc:mysql://xxxxxx:3306/xx?useUnicode=true&characterEncoding=utf8
#sharding.jdbc.datasource.ds-master.username=xx
#sharding.jdbc.datasource.ds-master.password=xx
## 分表配置
#sharding.jdbc.config.sharding.tables.vem_order_info.actual-data-nodes=ds_master.vem_order_info_${0..1}
#sharding.jdbc.config.sharding.tables.vem_order_info.table-strategy.standard.sharding-column=order_sn
#sharding.jdbc.config.sharding.tables.vem_order_info.table-strategy.standard.precise-algorithm-class-name=com.yunzhukj.vending.shardingjdbc.MyPreciseShardingAlgorithm

上面这么配置，加上自己定义的 MyPreciseShardingAlgorithm这个类，在springboot 1.x确实可用。

public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    private Logger logger = LoggerFactory.getLogger(MyPreciseShardingAlgorithm.class);

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        for (String tableName : collection) {
            String value = preciseShardingValue.getValue();
            int length = value.length();
            if (tableName.endsWith(Integer.parseInt(value.substring(length - 2, length - 1)) % 2 + "")) {
                return tableName;
            }
        }
        throw new IllegalArgumentException();
    }
}

如果是1.x,你现在自己在数据库里，建两个表vem_order_info_0 和 vem_order_info_1，正常查询，新增，都能实现分表。而且上面依赖中sharding-jdbc-config-spring这个也不需要。

一开始我想上面这么写，然后开始冒出问题了…

问题1：
Description:

The bean ‘dataSource’, defined in class path resource [io/shardingjdbc/spring/boot/SpringBootConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class] and overriding is disabled.

Action:

Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true

问题2：
Caused by: java.lang.ClassNotFoundException: org.springframework.boot.bind.RelaxedPropertyResolver

问题3：
Failed to configure a DataSource: ‘url’ attribute is not specified and no embedded datasource could be configured.

等等… 冒出各种问题

后来才发现，直接用上面那个配置不行…接下来才是2.x的解决方案…(是的，上面都是废话…)

1、依赖就是一开始发的
2、application.properties

server.port=8080
mybatis-plus.global-config.db-config.column-underline=true
mybatis-plus.mapper-locations=classpath:com/mht/springbootmybatisplus/mapper/xml/*.xml
mybatis-plus.type-aliases-package=com.mht.springbootmybatisplus.entity

sharding.jdbc.max-active=100
sharding.jdbc.url=jdbc:mysql://xxxxxx:3306/xxxxxx?useUnicode=true&characterEncoding=utf8
sharding.jdbc.username=xx
sharding.jdbc.password=xxxxx
sharding.jdbc.driver-class-name=com.mysql.cj.jdbc.Driver

3、新增两个文件 ShardDataSourceProperties （application.properties里sharding.jdbc对应的类） ShardDataSourceConfig（数据源配置，还有分表的配置）

@ConfigurationProperties(prefix = "sharding.jdbc")
public class ShardDataSourceProperties {
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private String filters;
    private int maxActive;
    private int initialSize;
    private int maxWait;
    private int minIdle;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private boolean removeAbandoned;
    private int removeAbandonedTimeout;
    private boolean logAbandoned;
    private List<String> connectionInitSqls;
    private String connectionProperties;
// 省略get 和set
}

import com.alibaba.druid.pool.DruidDataSource;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.yunzhukj.vending.shardingjdbc.UserIdShardingAlgorithm;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@ConditionalOnClass(DruidDataSource.class)
@EnableConfigurationProperties(ShardDataSourceProperties.class)
public class ShardDataSourceConfig {
    private final static String order = "vem_order_info";
    @Autowired
    private ShardDataSourceProperties shardDataSourceProperties;



    @Bean
    public DataSource dataSource() throws SQLException {
        return ShardingDataSourceFactory.createDataSource(shardingRule());
    }


    private DataSource ds() throws SQLException {
        DruidDataSource ds = parentDs();
        return ds;
    }

    private DruidDataSource parentDs() throws SQLException {
        DruidDataSource ds = new DruidDataSource();
        ds.setDriverClassName(shardDataSourceProperties.getDriverClassName());
        ds.setUrl(shardDataSourceProperties.getUrl());
        ds.setUsername(shardDataSourceProperties.getUsername());
        ds.setPassword(shardDataSourceProperties.getPassword());
        ds.setFilters(shardDataSourceProperties.getFilters());
        ds.setMaxActive(shardDataSourceProperties.getMaxActive());
        ds.setInitialSize(shardDataSourceProperties.getInitialSize());
        ds.setMaxWait(shardDataSourceProperties.getMaxWait());
        ds.setMinIdle(shardDataSourceProperties.getMinIdle());
        ds.setTimeBetweenEvictionRunsMillis(shardDataSourceProperties.getTimeBetweenEvictionRunsMillis());
        ds.setMinEvictableIdleTimeMillis(shardDataSourceProperties.getMinEvictableIdleTimeMillis());
        ds.setValidationQuery(shardDataSourceProperties.getValidationQuery());
        ds.setTestWhileIdle(shardDataSourceProperties.isTestWhileIdle());
        ds.setTestOnBorrow(shardDataSourceProperties.isTestOnBorrow());
        ds.setTestOnReturn(shardDataSourceProperties.isTestOnReturn());
        ds.setPoolPreparedStatements(shardDataSourceProperties.isPoolPreparedStatements());
        ds.setMaxPoolPreparedStatementPerConnectionSize(
                shardDataSourceProperties.getMaxPoolPreparedStatementPerConnectionSize());
        ds.setRemoveAbandoned(shardDataSourceProperties.isRemoveAbandoned());
        ds.setRemoveAbandonedTimeout(shardDataSourceProperties.getRemoveAbandonedTimeout());
        ds.setLogAbandoned(shardDataSourceProperties.isLogAbandoned());
        ds.setConnectionInitSqls(shardDataSourceProperties.getConnectionInitSqls());
        ds.setConnectionProperties(shardDataSourceProperties.getConnectionProperties());
        return ds;
    }

    private DataSourceRule getDataSourceRule() throws SQLException {
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        dataSourceMap.put("ds", ds());
        DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap);
        return dataSourceRule;
    }

    private TableRule getOrderTableRule() throws SQLException {
        String[] uns = new String[2];
        for (int i = 0; i < 2; i++) {
            uns[i] = order.concat("_").concat(String.valueOf(i));
        }
        TableRule tableRule = TableRule.builder(order)
                .actualTables(Arrays.asList(uns))
                .dataSourceRule(getDataSourceRule())
                .tableShardingStrategy(new TableShardingStrategy("order_sn", new OrderShardingAlgorithm()))
                .build();
        return tableRule;
    }
    private ShardingRule shardingRule() throws SQLException {
        ShardingRule shardingRule = ShardingRule.builder()
                .dataSourceRule(getDataSourceRule())
                .tableRules(Arrays.asList(getOrderTableRule())).build();
        return shardingRule;
    }
}

4、OrderShardingAlgorithm 分表策略


import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;

import java.util.Collection;
import java.util.LinkedHashSet;

public class OrderShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {

    @Override
    public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
        for (String each : tableNames) {
            if (each.endsWith(shardingValue.getValue()%2+"")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }


    @Override
    public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(tableNames.size());
        for (Integer value : shardingValue.getValues()) {
            for (String tableName : tableNames) {
                if (tableName.endsWith(value%2+"")) {
                    result.add(tableName);
                }
            }
        }
        return result;
    }


    @Override
    public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(tableNames.size());
        Range<Integer> range = shardingValue.getValueRange();
        for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : tableNames) {
                if (each.endsWith(i%2+"")) {
                    result.add(each);
                }
            }
        }
        return result;
    }
}