• 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

  • 相关阅读:
    全排列和全组合实现
    (原)关于MEPG-2中的TS流数据格式学习
    linux的PAM认证和shadow文件中密码的加密方式
    vim 撤销 回退操作
    memcached解压报错gzip: stdin: not in gzip format tar: Child returned status 1 tar: Error is not recoverable: exiting now的解决方法
    Linux系统安全之pam后门安装使用详解
    漏洞预警:Linux内核9年高龄的“脏牛”0day漏洞
    linux软链接的创建、删除和更新
    关于“.bash_profile”和“.bashrc”区别的总结
    linux下批量杀死进程
  • 原文地址:https://www.cnblogs.com/yrjns/p/13646216.html
Copyright © 2020-2023  润新知