Sharding中怎么使用Sphere实现数据分库分表操作）

本篇文章给大家分享的是有关Sharding中怎么使用Sphere实现数据分库分表操作），小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

引入依赖的jar

    org.apache.shardingsphere    sharding-jdbc-core    ${sharding-sphere.version}        org.apache.shardingsphere        sharding-jdbc-spring-boot-starter         ${sharding-sphere.version}                     

我这里用的是最新的版本

4.0.0-RC3-SNAPSHOT

库表脚本

当前测试例子是2库各10表，外加默认库的t_user表。结果如下：

创建数据库稍微注意一点，命名不能带下划线。 sharding0库脚本如下：

/* Navicat Premium Data Transfer Source Server         :  Source Server Type    : MySQL Source Server Version : 50727 Source Host           :  Source Schema         : sharding0 Target Server Type    : MySQL Target Server Version : 50727 File Encoding         : 65001 Date: 17/09/2019 10:43:09*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for t_order0-- ----------------------------DROP TABLE IF EXISTS `t_order0`;CREATE TABLE `t_order0`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order1-- ----------------------------DROP TABLE IF EXISTS `t_order1`;CREATE TABLE `t_order1`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order2-- ----------------------------DROP TABLE IF EXISTS `t_order2`;CREATE TABLE `t_order2`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order3-- ----------------------------DROP TABLE IF EXISTS `t_order3`;CREATE TABLE `t_order3`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order4-- ----------------------------DROP TABLE IF EXISTS `t_order4`;CREATE TABLE `t_order4`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order5-- ----------------------------DROP TABLE IF EXISTS `t_order5`;CREATE TABLE `t_order5`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order6-- ----------------------------DROP TABLE IF EXISTS `t_order6`;CREATE TABLE `t_order6`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order7-- ----------------------------DROP TABLE IF EXISTS `t_order7`;CREATE TABLE `t_order7`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order8-- ----------------------------DROP TABLE IF EXISTS `t_order8`;CREATE TABLE `t_order8`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order9-- ----------------------------DROP TABLE IF EXISTS `t_order9`;CREATE TABLE `t_order9`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_user-- ----------------------------DROP TABLE IF EXISTS `t_user`;CREATE TABLE `t_user`  (  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,  `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `age` int(11) NULL DEFAULT NULL,  `sex` int(4) NULL DEFAULT NULL,  `phone` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`user_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;

sharding1数据库脚本如下：

/* Navicat Premium Data Transfer Source Server         : 139.196.229.195 Source Server Type    : MySQL Source Server Version : 50727 Source Host           : 139.196.229.195:3306 Source Schema         : sharding1 Target Server Type    : MySQL Target Server Version : 50727 File Encoding         : 65001 Date: 17/09/2019 10:43:20*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for t_order0-- ----------------------------DROP TABLE IF EXISTS `t_order0`;CREATE TABLE `t_order0`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order1-- ----------------------------DROP TABLE IF EXISTS `t_order1`;CREATE TABLE `t_order1`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order2-- ----------------------------DROP TABLE IF EXISTS `t_order2`;CREATE TABLE `t_order2`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order3-- ----------------------------DROP TABLE IF EXISTS `t_order3`;CREATE TABLE `t_order3`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order4-- ----------------------------DROP TABLE IF EXISTS `t_order4`;CREATE TABLE `t_order4`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order5-- ----------------------------DROP TABLE IF EXISTS `t_order5`;CREATE TABLE `t_order5`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order6-- ----------------------------DROP TABLE IF EXISTS `t_order6`;CREATE TABLE `t_order6`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order7-- ----------------------------DROP TABLE IF EXISTS `t_order7`;CREATE TABLE `t_order7`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order8-- ----------------------------DROP TABLE IF EXISTS `t_order8`;CREATE TABLE `t_order8`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order9-- ----------------------------DROP TABLE IF EXISTS `t_order9`;CREATE TABLE `t_order9`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `user_id` bigint(32) NULL DEFAULT NULL,  `order_id` bigint(32) NULL DEFAULT NULL,  `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  `update_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;

默认分库分表策略

Springboot配置

#数据源名称，多数据源以逗号分隔spring.shardingsphere.datasource.names=sharding0,sharding1#sharding0是数据源名spring.shardingsphere.datasource.sharding0.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.sharding0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.sharding0.url=jdbc:mysql://***:3306/sharding0?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULLspring.shardingsphere.datasource.sharding0.username=rootspring.shardingsphere.datasource.sharding0.password=***# spring.shardingsphere.datasource..xxx= #数据库连接池的其它属性# sharding1 是数据源名称spring.shardingsphere.datasource.sharding1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.sharding1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.sharding1.url=jdbc:mysql://***:3306/sharding1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULLspring.shardingsphere.datasource.sharding1.username=rootspring.shardingsphere.datasource.sharding1.password=***# 默认数据源，没有分片的走这个数据源spring.shardingsphere.sharding.default-data-source-name=sharding0spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=sharding$->{user_id % 2}# t_order是表明spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=sharding$->{0..1}.t_order$->{0..9}spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_idspring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 10}# 分布式主键 内置的支持这三种 SNOWFLAKE/UUID/LEAF_SEGMENTspring.shardingsphere.sharding.tables.t_order.key-generator.column=idspring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

读写操作

因为上面是根据 user_id、order_id 两个字段分库分表的，所以必须插入查询都得带上user_id、order_id两个字段。

单表插入（支持）

  @Test        public void testSharding(){                Order order = new Order();                order.setUserId(1l);                order.setOrderId(1l);                order.setTitle("测试，userId:"+order.getUserId() + " orderId:" + order.getOrderId());                order.setContent(order.getTitle());                Assert.assertEquals(1,orderMapper.insert(order));        }

查看数据库，确实插入了

单表查询（支持）

      @Test        public void testShardingRead(){                OrderExample ex = new OrderExample();                ex.createCriteria().andUserIdEqualTo(1l).andOrderIdEqualTo(1l);                List orders = orderMapper.selectByExample(ex);                Assert.assertNotNull(orders);                Assert.assertEquals(1, orders.size());                orders.stream().forEach(o->{                        System.out.println("userId:"+o.getUserId() + " orderId:" + o.getOrderId());                });        }

默认库表插入（支持）

        @Test        public void testUserWrite(){                User user = new User();                user.setAge(10);                user.setName("张三");                user.setPhone("15157181986");                user.setSex(1);                user.setUpdateTime(new Date());                user.setCreateTime(new Date());                userMapper.insert(user);                System.out.println("userId:"+user.getUserId());        }

单表聚合查询

count(支持)

   @Test        public void testShardingReadCount(){                OrderExample ex = new OrderExample();                long count =  orderMapper.countByExample(ex);                System.out.println("count:"+count);        }

order by，limit（支持）

 @Test        public void testShardingReadLimit(){                OrderExample ex = new OrderExample();                ex.setLimit(2);                ex.setOffset(2l);                ex.setOrderByClause(" user_id desc ");                List orders = orderMapper.selectByExample(ex);                orders.stream().forEach(o->{                        System.out.println("userId:"+o.getUserId() + " orderId:" + o.getOrderId());                });        }

group by（支持）

@Getter@Setterpublic class GroupResult implements Serializable {        private Long userId;        private Integer cnt;}                    select user_id  , count(1) as cnt  from t_order group by user_id          @Test        public void testShardingReadGroupBy(){                List userOrderCounts = orderMapper.getUserOrderCount();                userOrderCounts.forEach(u->System.out.println("userId:"+u.getUserId() + " count:" + u.getCnt()));        }

单表与分表关联查询（仅支持广播表跟分表的关联查询）

单表：不做分库分表的逻辑表 （如本示例中的t_user） 分表：做分库分表的逻辑表（如本示例中的 t_order） 广播表：指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。（这个概念参考：广播表）

这里 单表 跟 分表关联查询，必须每一个库中有一份单表（也就是广播表），否则不能关联查询。如下面的语句，本示例中是不能执行的。因为本示例 t_order 逻辑表分散在两个库中，t_user表必须要在两个库中都有一份才能关联查询。

      select     id, u.user_id as userId, order_id as orderId , title, content, u.create_time as createTime , u.update_time as updateTime , `name`, age, sex, phone    from t_user u join t_order o on u.user_id = o.user_id     where u.age > #{age,jdbcType=INTEGER}  

以上就是Sharding中怎么使用Sphere实现数据分库分表操作），小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。