springboot + mybatis + mysql + sharding-jdbc如何實(shí)現(xiàn)分庫分表

這篇文章主要介紹springboot + mybatis + mysql + sharding-jdbc如何實(shí)現(xiàn)分庫分表，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

配置文件

mybatis.config-locations=classpath:mybatis/mybatis-config.xml

#datasource
spring.devtools.remote.restart.enabled=false

#data source1
spring.datasource.test1.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg1?serverTimezone=UTC
spring.datasource.test1.username=root
spring.datasource.test1.password=123456

#data source2
spring.datasource.test2.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test2.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg2?serverTimezone=UTC
spring.datasource.test2.username=root
spring.datasource.test2.password=123456

在數(shù)據(jù)庫添加表結(jié)構(gòu)

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_order_0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
  `order_id` varchar(32) DEFAULT NULL COMMENT '順序編號',
  `user_id` varchar(32) DEFAULT NULL COMMENT '用戶編號',
  `userName` varchar(32) DEFAULT NULL COMMENT '用戶名',
  `passWord` varchar(32) DEFAULT NULL COMMENT '密碼',
  `nick_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_order_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
  `order_id` varchar(32) DEFAULT NULL COMMENT '順序編號',
  `user_id` varchar(32) DEFAULT NULL COMMENT '用戶編號',
  `userName` varchar(32) DEFAULT NULL COMMENT '用戶名',
  `passWord` varchar(32) DEFAULT NULL COMMENT '密碼',
  `nick_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

數(shù)據(jù)源配置和Mybatis配置和分庫分表規(guī)則配置

@Configuration
@MapperScan(basePackages = "com.example.shardingjdbc.mapper", sqlSessionTemplateRef  = "test1SqlSessionTemplate")
public class DataSourceConfig {

    /**
     * 配置數(shù)據(jù)源0，數(shù)據(jù)源的名稱最好要有一定的規(guī)則，方便配置分庫的計算規(guī)則
     * @return
     */
    @Bean(name="dataSource0")
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    public DataSource dataSource0(){
        return DataSourceBuilder.create().build();
    }
    /**
     * 配置數(shù)據(jù)源1，數(shù)據(jù)源的名稱最好要有一定的規(guī)則，方便配置分庫的計算規(guī)則
     * @return
     */
    @Bean(name="dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
    public DataSource dataSource1(){
        return DataSourceBuilder.create().build();
    }

    /**
     * 配置數(shù)據(jù)源規(guī)則，即將多個數(shù)據(jù)源交給sharding-jdbc管理，并且可以設(shè)置默認(rèn)的數(shù)據(jù)源，
     * 當(dāng)表沒有配置分庫規(guī)則時會使用默認(rèn)的數(shù)據(jù)源
     * @param dataSource0
     * @param dataSource1
     * @return
     */
    @Bean
    public DataSourceRule dataSourceRule(@Qualifier("dataSource0") DataSource dataSource0,
                                         @Qualifier("dataSource1") DataSource dataSource1){
        Map<String, DataSource> dataSourceMap = new HashMap<>(); //設(shè)置分庫映射
        dataSourceMap.put("dataSource0", dataSource0);
        dataSourceMap.put("dataSource1", dataSource1);
        return new DataSourceRule(dataSourceMap, "dataSource0"); //設(shè)置默認(rèn)庫，兩個庫以上時必須設(shè)置默認(rèn)庫。默認(rèn)庫的數(shù)據(jù)源名稱必須是dataSourceMap的key之一
    }

    /**
     * 配置數(shù)據(jù)源策略和表策略，具體策略需要自己實(shí)現(xiàn)
     * @param dataSourceRule
     * @return
     */
    @Bean
    public ShardingRule shardingRule(DataSourceRule dataSourceRule){
        //具體分庫分表策略
        TableRule orderTableRule = TableRule.builder("t_order")
                .actualTables(Arrays.asList("t_order_0", "t_order_1"))
                .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm()))
                .dataSourceRule(dataSourceRule)
                .build();

        //綁定表策略，在查詢時會使用主表策略計算路由的數(shù)據(jù)源，因此需要約定綁定表策略的表的規(guī)則需要一致，可以一定程度提高效率
        List<BindingTableRule> bindingTableRules = new ArrayList<BindingTableRule>();
        bindingTableRules.add(new BindingTableRule(Arrays.asList(orderTableRule)));
        return ShardingRule.builder()
                .dataSourceRule(dataSourceRule)
                .tableRules(Arrays.asList(orderTableRule))
                .bindingTableRules(bindingTableRules)
                .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))
                .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm()))
                .build();
    }

    /**
     * 創(chuàng)建sharding-jdbc的數(shù)據(jù)源DataSource，MybatisAutoConfiguration會使用此數(shù)據(jù)源
     * @param shardingRule
     * @return
     * @throws SQLException
     */
    @Bean(name="dataSource")
    public DataSource shardingDataSource(ShardingRule shardingRule) throws SQLException {
        return ShardingDataSourceFactory.createDataSource(shardingRule);
    }

    /**
     * 需要手動配置事務(wù)管理器
     * @param dataSource
     * @return
     */
    @Bean
    public DataSourceTransactionManager transactitonManager(@Qualifier("dataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "test1SqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "test1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

分庫規(guī)則

public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {

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

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

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

分表規(guī)則

public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {

    @Override
    public String doEqualSharding(Collection<String> tableNames, ShardingValue<Long> 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<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(tableNames.size());
        for (Long 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<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(tableNames.size());
        Range<Long> range = (Range<Long>) shardingValue.getValueRange();
        for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : tableNames) {
                if (each.endsWith(i % 2 + "")) {
                    result.add(each);
                }
            }
        }
        return result;
    }
}

取消自動配置數(shù)據(jù)源，使用我們這里定義的數(shù)據(jù)源配置

@SpringBootApplication
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class}) //排除DataSourceConfiguratrion
@EnableTransactionManagement(proxyTargetClass = true)   //開啟事物管理功能
public class ShardingJdbcApplication {

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

使用測試

@Service
@RestController
public class UserController {

    @Autowired
    private UserService userService;

    /**
     * 測試新增
     * @param id
     * @param user_id
     * @param order_id
     * @param nickName
     * @param passWord
     * @param userName
     * @return
     * http://localhost:8080/update1?id=1&user_id=1&order_id=1&nickName=%E5%BC%A0%E4%B8%89&passWord=123456&userName=%E7%94%A8%E6%88%B71
     */
    @RequestMapping(value="update1")
    public String updateTransactional(@RequestParam(value = "id") Long id,
                                      @RequestParam(value = "user_id") Long user_id,
                                      @RequestParam(value = "order_id") Long order_id,
                                      @RequestParam(value = "nickName") String nickName,
                                      @RequestParam(value = "passWord") String passWord,
                                      @RequestParam(value = "userName") String userName
    ) {
        User user2 = new User();
        user2.setId(id);
        user2.setUser_id(user_id);
        user2.setOrder_id(order_id);
        user2.setNick_name(nickName);
        user2.setPassWord(passWord);
        user2.setUserName(userName);
        userService.insert(user2);
        return "success";
    }
}

以上是“springboot + mybatis + mysql + sharding-jdbc如何實(shí)現(xiàn)分庫分表”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！