springboot + mybatis + mysql + sharding-jdbc如何实现分库分表

这篇文章主要介绍springboot + mybatis + mysql + sharding-jdbc如何实现分库分表，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

配置文件

mybatis.config-locations=classpath:mybatis/mybatis-config.xml#datasourcespring.devtools.remote.restart.enabled=false#data source1spring.datasource.test1.driverClassName=com.mysql.jdbc.Driverspring.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg1?serverTimezone=UTCspring.datasource.test1.username=rootspring.datasource.test1.password=123456#data source2spring.datasource.test2.driverClassName=com.mysql.jdbc.Driverspring.datasource.test2.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg2?serverTimezone=UTCspring.datasource.test2.username=rootspring.datasource.test2.password=123456

在数据库添加表结构

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;

数据源配置和Mybatis配置和分库分表规则配置

@Configuration@MapperScan(basePackages = "com.example.shardingjdbc.mapper", sqlSessionTemplateRef  = "test1SqlSessionTemplate")public class DataSourceConfig {    /**     * 配置数据源0，数据源的名称最好要有一定的规则，方便配置分库的计算规则     * @return     */    @Bean(name="dataSource0")    @ConfigurationProperties(prefix = "spring.datasource.test1")    public DataSource dataSource0(){        return DataSourceBuilder.create().build();    }    /**     * 配置数据源1，数据源的名称最好要有一定的规则，方便配置分库的计算规则     * @return     */    @Bean(name="dataSource1")    @ConfigurationProperties(prefix = "spring.datasource.test2")    public DataSource dataSource1(){        return DataSourceBuilder.create().build();    }    /**     * 配置数据源规则，即将多个数据源交给sharding-jdbc管理，并且可以设置默认的数据源，     * 当表没有配置分库规则时会使用默认的数据源     * @param dataSource0     * @param dataSource1     * @return     */    @Bean    public DataSourceRule dataSourceRule(@Qualifier("dataSource0") DataSource dataSource0,                                         @Qualifier("dataSource1") DataSource dataSource1){        Map dataSourceMap = new HashMap<>(); //设置分库映射        dataSourceMap.put("dataSource0", dataSource0);        dataSourceMap.put("dataSource1", dataSource1);        return new DataSourceRule(dataSourceMap, "dataSource0"); //设置默认库，两个库以上时必须设置默认库。默认库的数据源名称必须是dataSourceMap的key之一    }    /**     * 配置数据源策略和表策略，具体策略需要自己实现     * @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();        //绑定表策略，在查询时会使用主表策略计算路由的数据源，因此需要约定绑定表策略的表的规则需要一致，可以一定程度提高效率        List bindingTableRules = new ArrayList();        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();    }    /**     * 创建sharding-jdbc的数据源DataSource，MybatisAutoConfiguration会使用此数据源     * @param shardingRule     * @return     * @throws SQLException     */    @Bean(name="dataSource")    public DataSource shardingDataSource(ShardingRule shardingRule) throws SQLException {        return ShardingDataSourceFactory.createDataSource(shardingRule);    }    /**     * 需要手动配置事务管理器     * @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);    }}

分库规则

public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm {    @Override    public String doEqualSharding(Collection databaseNames, ShardingValue shardingValue) {        for (String each : databaseNames) {            if (each.endsWith(Long.parseLong(shardingValue.getValue().toString()) % 2 + "")) {                return each;            }        }        throw new IllegalArgumentException();    }    @Override    public Collection doInSharding(Collection databaseNames, ShardingValue shardingValue) {        Collection 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 doBetweenSharding(Collection databaseNames, ShardingValue shardingValue) {        Collection result = new LinkedHashSet<>(databaseNames.size());        Range range = (Range) 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;    }}

分表规则

public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm {    @Override    public String doEqualSharding(Collection tableNames, ShardingValue shardingValue) {        for (String each : tableNames) {            if (each.endsWith(shardingValue.getValue() % 2 + "")) {                return each;            }        }        throw new IllegalArgumentException();    }    @Override    public Collection doInSharding(Collection tableNames, ShardingValue shardingValue) {        Collection 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 doBetweenSharding(Collection tableNames, ShardingValue shardingValue) {        Collection result = new LinkedHashSet<>(tableNames.size());        Range range = (Range) 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;    }}

取消自动配置数据源，使用我们这里定义的数据源配置

@SpringBootApplication@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class}) //排除DataSourceConfiguratrion@EnableTransactionManagement(proxyTargetClass = true)   //开启事物管理功能public class ShardingJdbcApplication {    public static void main(String[] args) {        SpringApplication.run(ShardingJdbcApplication.class, args);    }}

使用测试

@Service@RestControllerpublic 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如何实现分库分表"这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注行业资讯频道！