SpringBoot使用Sharding-JDBC分库分表实战

一、准备工作

1.准备两个database、4张table

 

二、引入相关maven

        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.9</version>
        </dependency>
        <!-- sharding-jdbc -->
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>1.5.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

三、启动类注解

package com.wx.wxjob;

import net.hasor.spring.boot.EnableHasor;
import net.hasor.spring.boot.EnableHasorWeb;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@EnableHasor()      // 在Spring 中启用 Hasor
@EnableHasorWeb()   // 将 hasor-web 配置到 Spring 环境中，Dataway 的 UI 是通过 hasor-web 提供服务。
@SpringBootApplication(scanBasePackages = "com.wx.*")
@EnableAspectJAutoProxy
@MapperScan("com.wx.wxjob.dao")
@EnableTransactionManagement(proxyTargetClass = true) // 开启事务
@EnableConfigurationProperties // 加入配置实体（本文没用到）
public class WxJobApplication extends SpringBootServletInitializer {

    public static void main(String[] args) {
        SpringApplication.run(WxJobApplication.class, args);
    }


    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {
        return builder.sources(WxJobApplication.class);
    }
}

四、新建实体类 （这里需要注意，ID不能自增）

package com.wx.wxjob.entity;

import com.baomidou.mybatisplus.activerecord.Model;
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Date;

/**
 * FileName: PCMessage
 * Author: pengyd
 * Date: 2020/5/28
 * function:
 */
@Data
@TableName("PCMessage")
@AllArgsConstructor
@NoArgsConstructor
public class PCMessage extends Model<User> {

    private static final long serialVersionUID = -3682178042573499965L;
    @TableId
    private Long id;

    private String name;

    private String message;

    private Integer sequence;

    private Integer state;

    @TableField("isStick")
    private Integer isStick;

    private Date crtTime;

    private Date updTime;

    private Date uptTime;

    private String creator;

    private String updator;

    @Override
    protected Serializable pkVal() {
        return this.id;
    }
}

五、核心部分

1.设置数据源以及分库分表的策略

package com.wx.wxjob.shardingJDBC;

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.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.mysql.jdbc.Driver;

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

/**
 * FileName: DataSourceConfig
 * Author: pengyd
 * Date: 2020/5/28
 * function:
 */
@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource getDataSource() throws SQLException {
        return buildDataSource();
    }

    private DataSource buildDataSource() throws SQLException {
        //设置分库映射
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        //添加两个数据库ds_0,ds_1到map里
        dataSourceMap.put("ds_0", createDataSource("job"));
        dataSourceMap.put("ds_1", createDataSource("job1"));
        //设置默认db为ds_0，也就是为那些没有配置分库分表策略的指定的默认库
        //如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库，但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据
        DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, "ds_0");

        //设置分表映射，将PCMessage_0和PCMessage_1两个实际的表映射到PCMessage逻辑表
        //0和1两个表是真实的表，PCMessage是个虚拟不存在的表，只是供使用。
        TableRule orderTableRule = TableRule.builder("PCMessage")
                .actualTables(Arrays.asList("PCMessage_0", "PCMessage_1"))
                .dataSourceRule(dataSourceRule)
                .build();

        //具体分库分表策略，按什么规则来分
        ShardingRule shardingRule = ShardingRule.builder()
                .dataSourceRule(dataSourceRule)
                .tableRules(Collections.singletonList(orderTableRule))
                .databaseShardingStrategy(new DatabaseShardingStrategy("id", new ModuloDatabaseShardingAlgorithm()))
                .tableShardingStrategy(new TableShardingStrategy("sequence", new ModuloTableShardingAlgorithm())).build();

        return ShardingDataSourceFactory.createDataSource(shardingRule);
    }

    private static DataSource createDataSource(final String dataSourceName) {
        //使用druid连接数据库
        DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(Driver.class.getName());
        result.setUrl(String.format("jdbc:mysql://localhost:3306/%s?useUnicode=true&characterEncoding=UTF-8&useSSL=false", dataSourceName));
        result.setUsername("root");
        result.setPassword("123456");
        return result;
    }

}

2.具体实现分库策略

package com.wx.wxjob.shardingJDBC;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
import com.google.common.collect.Range;

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

/**
 * FileName: ModuloDatabaseShardingAlgorithm
 * Author: pengyd
 * Date: 2020/5/28
 * function: 分库策略
 */
public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
    @Override
    public String doEqualSharding(Collection<String> collection, ShardingValue<Long> shardingValue) {
        for (String each : collection) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }

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

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

3.具体实现分表策略

package com.wx.wxjob.shardingJDBC;

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;

/**
 * FileName: ModuloTableShardingAlgorithm
 * Author: pengyd
 * Date: 2020/5/28
 * function: 分表策略
 */
public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {
    @Override
    public String doEqualSharding(Collection<String> collection, ShardingValue<Integer> shardingValue) {
        for (String each : collection) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }

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

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

六、测试效果

package com.wx.wxjob;

import com.wx.wxjob.dao.PCMessageDao;
import com.wx.wxjob.entity.PCMessage;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

/**
 * FileName: PCMessageTest
 * Author: pengyd
 * Date: 2020/5/28
 * function:
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class PCMessageTest {


    @Autowired
    private PCMessageDao pcMessageDao;

    @Test
    public void add() {
        PCMessage pcMessage = new PCMessage();
        pcMessage.setIsStick(0);
        pcMessage.setCreator("yourname");
        pcMessage.setUpdator("yourname");
        pcMessage.setState(1);
        pcMessage.setName("Sharding-JDBC分库分表");
        pcMessage.setMessage("Sharding-JDBC分库分表项目实战");

        for (int i = 20; i < 30; i++) {
            pcMessage.setId((long) i);
            pcMessage.setSequence(i+1);
            pcMessageDao.insert(pcMessage);
        }


    }
}

总结

主要看doEqualSharding方法（譬如select * from pcmessage where id = 11就是equal），availableTargetNames就是所有的库名（ds_0,ds_1),shardingValue就是在DataSourceConfig里指定的id,代码就是如果id是偶数就算到ds_0数据库，其他的就放ds_1数据库。而另外的两个方法，doIn和doBetween是用在如where id in (1,23,7)和where id between(1, 6)。