sharding-jdbc-4.0.0-RC3版本的使用

最新推荐文章于 2024-08-26 16:39:36 发布

for communication

最新推荐文章于 2024-08-26 16:39:36 发布

阅读量721

点赞数

文章标签： java mysql

本文链接：https://blog.csdn.net/weixin_45580575/article/details/108356499

版权

之前在项目中用到了sharding-proxy作为分表中间件，就顺便去看了下sharding-jdbc怎么结合在项目中使用

使用的版本为

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>4.0.0-RC3</version>
</dependency>

这边是结合mybatis使用的sharding。

首先在properties中配置相应的数据源信息

spring.datasource.wjw.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.wjw.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.wjw.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.datasource.wjw.username=root
spring.datasource.wjw.password=1754
spring.datasource.wjw.initial-size=10
spring.datasource.wjw.max-active=100
spring.datasource.wjw.min-idle=10
spring.datasource.wjw.max-wait=60000
spring.datasource.wjw.pool-prepared-statements=true
spring.datasource.wjw.max-pool-prepared-statement-per-connection-size=20
spring.datasource.wjw.time-between-eviction-runs-millis=60000
spring.datasource.wjw.min-evictable-idle-time-millis=300000
spring.datasource.wjw.validation-query=SELECT 1 FROM DUAL

接着使用java配置数据源，引入sharding

@Configuration
//扫描接口文件
@MapperScan(basePackages = "com.example.demo.mapper.wjw", sqlSessionTemplateRef = "wjwSqlSessionTemplate")
public class WjwDataSourceConfig {

    @ConfigurationProperties(prefix = "spring.datasource.wjw")
    @Bean(name = "wjwDataSource")
    @Primary
    public DruidDataSource druidDataSource(){
        return new DruidDataSource();
    }
    
    //配置sql会话工厂
    @Bean(name = "wjwSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate setSqlSessionTemplate(@Qualifier("wjwSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }

sharding的配置

@Configuration
public class ShardJdbcConfig {

    /**
     * JDBC操作配置
     */
    @Bean(name = "dataOneTemplate")
    public JdbcTemplate dataOneTemplate(@Autowired DruidDataSource dataOneSource) {
        return new JdbcTemplate(dataOneSource);
    }

    /**
     * Shard-JDBC 配置
     */
    @Bean(name = "shardingwjw")
    public DataSource createDataSourceMap(@Autowired WjwDataSourceConfig wjwDataSourceConfig) throws Exception {
        ShardingRuleConfiguration shardJdbcConfig = new ShardingRuleConfiguration();
        //配置分片规则中的     ->表规则getTableRule01
        shardJdbcConfig.getTableRuleConfigs().add(getTableRule01());
        shardJdbcConfig.setDefaultDataSourceName("test");
        //设置分库规则
//        shardJdbcConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id","test.table_one${1..2}"));

        //设置默认分表规则
//        shardJdbcConfig.setDefaultTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "table_one${id % 3}"));
        shardJdbcConfig.setDefaultTableShardingStrategyConfig(new ComplexShardingStrategyConfiguration("id",new ComplexSharding()));
        Map<String, DataSource> dataMap = new LinkedHashMap<>();
        dataMap.put("test", wjwDataSourceConfig.druidDataSource());
        //3、属性配置项，可以为以下属性
        Properties propertie = new Properties();
        //是否打印SQL解析和改写日志
        propertie.setProperty("sql.show", Boolean.TRUE.toString());
        //用于SQL执行的工作线程数量，为零则表示无限制
        propertie.setProperty("executor.size", "4");
        //每个物理数据库为每次查询分配的最大连接数量
        propertie.setProperty("max.connections.size.per.query", "1");
        //是否在启动时检查分表元数据一致性
        propertie.setProperty("check.table.metadata.enabled", "false");
        //dataMap为数据源
        return ShardingDataSourceFactory.createDataSource(dataMap, shardJdbcConfig, propertie);
    }


    /**
     * Shard-JDBC 分表配置
     */
    private static TableRuleConfiguration getTableRule01() {
        // 配置表规则。需要划分的表
        TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration("table_one","test.table_one0,test.table_one1,test.table_one2");
        tableRuleConfiguration.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id"));
//        tableRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "test.table_one${id % 2}"));
        //关系到表名 test.table_one${id % 2}  $取值运算，计算ID模二的值 为实际表名
//        tableRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "test.table_one${id % 3}"));
        tableRuleConfiguration.setTableShardingStrategyConfig(new ComplexShardingStrategyConfiguration("id,phone",new ComplexSharding()));
        return tableRuleConfiguration;
    }

//配置bean工厂并扫描xml文件
    @Bean(name = "wjwSqlSessionFactory")
    @Primary
    public SqlSessionFactory setSqlSessionFactory(@Qualifier("shardingwjw") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:sumMapper/wjwMapper/*.xml"));
        return bean.getObject();
    }

    //配置事务
    @Bean(name = "wjwTransactionManager")
    @Primary
    public DataSourceTransactionManager setTransactionMapper(@Qualifier("shardingwjw") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

复合分片策略的配置

public class ComplexSharding implements ComplexKeysShardingAlgorithm<String>  {
    @Override
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<String> shardingValue) {
        //获取=  in 键的值
        Map<String, Collection<String>> map= shardingValue.getColumnNameAndShardingValuesMap();
        //获取范围 4.0.0-RC3才有这个方法，之前用4.0.0-RC1的时候一直获取不到范围
        Map<String, Range<String>> rangeMap = shardingValue.getColumnNameAndRangeValuesMap();

        Set<String> settable=new HashSet<>();
        System.out.println("map="+map);
        System.out.println("rangeMap="+rangeMap);
        System.out.println("collection="+collection);
 
       
        return settable;
    }