sharding jdbc(二) 分库分表 - 五色风车

基于SpringBoot+Mybaits+sharding jdbc

由于sharding-jdbc是不支持动态进行建库的SQL，那么就需要一次把需要的数据库和数据表都建好

数据库脚本：

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  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  DEFAULT CHARSET=utf8;

pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.37</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!--sharding-jdbc -->
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>1.5.4</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

View Code

配置文件

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:3336/sharding-jdbc?serverTimezone=UTC
spring.datasource.test1.username=root
spring.datasource.test1.password=

#data source2
spring.datasource.test2.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test2.jdbcUrl=jdbc:mysql://127.0.0.1:3336/sharding-jdbc2?serverTimezone=UTC
spring.datasource.test2.username=root
spring.datasource.test2.password=

View Code

启动类：

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

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

xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.shardingjdbc.mapper.UserMapper" >
    <resultMap id="BaseResultMap" type="com.example.shardingjdbc.entity.User" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="order_id" property="order_id" jdbcType="BIGINT" />
        <result column="user_id" property="user_id" jdbcType="BIGINT" />
        <result column="userName" property="userName" jdbcType="VARCHAR" />
        <result column="passWord" property="passWord" jdbcType="VARCHAR" />
        <result column="nick_name" property="nickName" jdbcType="VARCHAR" />
    </resultMap>

    <sql id="Base_Column_List" >
        id, userName, passWord, user_sex, nick_name
    </sql>

    <insert id="insert" parameterType="com.example.shardingjdbc.entity.User" >
        INSERT INTO
        t_order
        (order_id,user_id,userName,passWord)
        VALUES
        (#{order_id},#{user_id},#{userName}, #{passWord})
    </insert>

</mapper>

View Code

实体类：

public class User {
 
    private Long id;
    private Long order_id;
    private Long user_id;
    private String userName;
    private String passWord;
    private String nickName;
}

service层

import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import com.example.shardingjdbc.entity.User;
import com.example.shardingjdbc.mapper.UserMapper;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
public class UserService {

    @Resource
    private UserMapper userMapper;

    public void insert(User user) {
        int a = 1;
        for (int i = 0; i < 10; i++) {
            for (int j = 0; j < 10; j++) {
                User u = new User();
                u.setUser_id(Long.valueOf(i+""));
                u.setOrder_id(Long.valueOf(a+""));
                u.setUserName("UserName" + a);
                u.setNickName("NickName" + a);
                userMapper.insert(u);
                ++a;
            }
        }
    }

    public Object selectByUserId(Long id) {
        return userMapper.selectByUserId(id);
    }

    public void update(Long orderId){
        userMapper.updateByOrderId(orderId);
    }

}

controller 层

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.example.shardingjdbc.entity.User;
import com.example.shardingjdbc.service.UserService;

@Service
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    //测试
    @RequestMapping(value="/a")// localhost:8080/user/a
    public String updateTransactional() {
        User user2 = new User();

        userService.insert(user2);
        return "success";
    }

    @RequestMapping(value="/select")// localhost:8080/user/a
    public Object selectUser(Integer id) {
        Long idLong = Long.valueOf(id+"");
        return userService.selectByUserId(idLong);
    }

    @RequestMapping(value="/update")// localhost:8080/user/a
    public void updateUser(Long id) {
        userService.update(id);
    }
}

数据源配置和Mybatis配置和分库分表规则（重要）

这里，我们是将多个数据源交给sharding-jdbc进行管理，并且有默认的数据源，当没有设置分库分表规则的时候就可以使用默认的数据源

分表：user_id%2 = 0的数据存储到sharding-jdbc，为1的存储到sharding-jdbc2

分表：order_id%2 = 0的数据存储到 t_order_0，为1的存储到 t_order_1

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

import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.BindingTableRule;
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 com.example.shardingjdbc.strategy.ModuloDatabaseShardingAlgorithm;
import com.example.shardingjdbc.strategy.ModuloTableShardingAlgorithm;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

/**
 * @Auther: Tinko
 * @Date: 2018/12/19 16:27
 * @Description: 数据源配置和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<String, DataSource> 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<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();
    }

    /**
     * 创建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);
    }
}

View Code

分库规则

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

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

/**
 * @Auther: Tinko
 * @Date: 2018/12/19 16:31
 * @Description: 分库规则
 */
public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {

    @Override
    public String doEqualSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
        System.out.println("分库规则    doEqualSharding");
        System.out.println(databaseNames.toString());// [dataSource0, dataSource1]
        // ShardingValue(logicTableName=t_order, columnName=user_id, value=0, values=[], valueRange=null)
        // ShardingValue(logicTableName=t_order, columnName=user_id, value=1, values=[], valueRange=null)
        // ...
        System.out.println(shardingValue.toString());
        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);
                }
            }
        }
        System.out.println("分库规则    doInSharding");
        System.out.println(databaseNames.toString());
        System.out.println(shardingValue.toString());
        System.out.println(result.toString());
        return result;
    }

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

View Code

分表规则

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

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

/**
 * @Auther: Tinko
 * @Date: 2018/12/19 16:30
 * @Description: 分表规则
 */
public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {

    @Override
    public String doEqualSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
        System.out.println("分表规则    doEqualSharding");
        System.out.println(tableNames.toString());// [t_order_0, t_order_1]
        // ShardingValue(logicTableName=t_order, columnName=order_id, value=0, values=[], valueRange=null)
        // ShardingValue(logicTableName=t_order, columnName=order_id, value=1, values=[], valueRange=null)
        // ...
        System.out.println(shardingValue.toString());
        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);
                }
            }
        }
        System.out.println("分表规则    doInSharding");
        System.out.println(tableNames.toString());
        System.out.println(shardingValue.toString());
        System.out.println(result.toString());
        return result;
    }

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

View Code

测试结果：

1) 插入数据

2）查询 http://localhost:8080/user/select?id=1

/*[{"id":6,"order_id":22,"user_id":2,"userName":"UserName22","passWord":null,"nickName":null},
{"id":7,"order_id":24,"user_id":2,"userName":"UserName24","passWord":null,"nickName":null},
{"id":8,"order_id":26,"user_id":2,"userName":"UserName26","passWord":null,"nickName":null},
{"id":9,"order_id":28,"user_id":2,"userName":"UserName28","passWord":null,"nickName":null},
{"id":10,"order_id":30,"user_id":2,"userName":"UserName30","passWord":null,"nickName":null},
{"id":6,"order_id":21,"user_id":2,"userName":"UserName21","passWord":null,"nickName":null},
{"id":7,"order_id":23,"user_id":2,"userName":"UserName23","passWord":null,"nickName":null},
{"id":8,"order_id":25,"user_id":2,"userName":"UserName25","passWord":null,"nickName":null},
{"id":9,"order_id":27,"user_id":2,"userName":"UserName27","passWord":null,"nickName":null},
{"id":10,"order_id":29,"user_id":2,"userName":"UserName29","passWord":null,"nickName":null}]*/

3）修改

http://localhost:8080/user/update?id=1

原文链接： https://blog.csdn.net/a992795427/article/details/85102918?utm_medium=distribute.pc_relevant_right.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param_right&depth_1-utm_source=distribute.pc_relevant_right.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param_right

发表于 2020-09-10 15:56 五色风车阅读(219) 评论(0) 编辑收藏举报