package com.zhz.shardingspheredemo.pojo;
 
 import com.baomidou.mybatisplus.annotation.IdType;
 import com.baomidou.mybatisplus.annotation.TableField;
 import com.baomidou.mybatisplus.annotation.TableId;
 import com.baomidou.mybatisplus.annotation.TableName;
 import lombok.AllArgsConstructor;
 import lombok.Builder;
 import lombok.Data;
 import lombok.NoArgsConstructor;
 
   /**  
     * @Description: 课程实体类
     * 
     * @author zhouhengzhe
     * @date 2021/9/4下午3:24
     * @since 
     */
 @Data
 @Builder
 @AllArgsConstructor
 @NoArgsConstructor
 @TableName(value = "course")
 public class Course {
     @TableId(value = "cid")
     private Long cid;
 
     @TableField(value = "cname")
     private String cname;
 
     @TableField(value = "user_id")
     private Long userId;
 
     @TableField(value = "cstatus")
     private String cstatus;
 }

package com.zhz.shardingspheredemo.mapper;
 
 import com.baomidou.mybatisplus.core.mapper.BaseMapper;
 import com.zhz.shardingspheredemo.pojo.Course;
 
   /**  
     * @Description: 课程mapper数据操作层
     * 
     * @author zhouhengzhe
     * @date 2021/9/4下午3:24
     * @since 
     */
 public interface CourseMapper extends BaseMapper<Course> {
 }

package com.zhz.shardingspheredemo;
 
 import org.mybatis.spring.annotation.MapperScan;
 import org.springframework.boot.SpringApplication;
 import org.springframework.boot.autoconfigure.SpringBootApplication;
 
 /**
  * @author mac
  */
 @SpringBootApplication
 @MapperScan("com.zhz.shardingspheredemo.mapper")
 public class ShardingSphereDemoApplication {
 
     public static void main(String[] args) {
         SpringApplication.run(ShardingSphereDemoApplication.class, args);
     }
 }

package com.zhz.shardingspheredemo;
 
 import com.zhz.shardingspheredemo.mapper.CourseMapper;
 import com.zhz.shardingspheredemo.pojo.Course;
 import org.junit.Test;
 import org.junit.runner.RunWith;
 import org.springframework.boot.test.context.SpringBootTest;
 import org.springframework.test.context.junit4.SpringRunner;
 
 import javax.annotation.Resource;
 
 @RunWith(SpringRunner.class)
 @SpringBootTest
 public class ShardingSphereDemoApplicationTests {
 
     @Resource
     private CourseMapper courseMapper;
 
     @Test
     public void testSharding() {
         for (int i = 0; i < 10; i++) {
             Course course=Course
                     .builder()
                     .cname("shardingSphere")
                     .userId((long)(1000+i))
                     .cstatus("1")
                     .build();
             courseMapper.insert(course);
         }
     }
 }

package com.zhz.shardingspheredemo;
 
 import com.zhz.shardingspheredemo.mapper.CourseMapper;
 import com.zhz.shardingspheredemo.pojo.Course;
 import org.junit.Test;
 import org.junit.runner.RunWith;
 import org.springframework.boot.test.context.SpringBootTest;
 import org.springframework.test.context.junit4.SpringRunner;
 
 import javax.annotation.Resource;
 
 @RunWith(SpringRunner.class)
 @SpringBootTest
 public class ShardingSphereDemoApplicationTests {
 
     @Resource
     private CourseMapper courseMapper;
 
     @Test
     public void testSharding() {
         for (int i = 0; i < 10; i++) {
             Course course=Course
                     .builder()
                     .cname("shardingSphere")
                     .userId((long)(1000+i))
                     .cstatus("1")
                     .build();
             courseMapper.insert(course);
         }
     }
 }

@Test
     public void testQuery(){
         QueryWrapper<Course> queryWrapper=new QueryWrapper<>();
         //成功
 //        queryWrapper.eq("cid",1434444620292460546L);
         //成功
 //        queryWrapper.orderByAsc("cid");
         queryWrapper.between("cid",1434444618337914881L,1434444620539924482L);
         List<Course> courses = courseMapper.selectList(queryWrapper);
         courses.forEach(System.out::println);
     }

package com.zhz.shardingspheredemo.algorithm;
 
 import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
 import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
 
 import java.math.BigInteger;
 import java.util.Collection;
 
 
 /**
  * @Description: 精确的数据库分配算法
  *
  * @author zhouhengzhe
  * @date 2021/9/4下午3:24
  * @since
  */
 
 public class MyPreciseDSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
     //select * from course where cid = ? or cid in (?,?)
     @Override
     public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
         String logicTableName = shardingValue.getLogicTableName();
         String cid = shardingValue.getColumnName();
         Long cidValue = shardingValue.getValue();
         //实现 course_$->{cid%2+1)
         BigInteger shardingValueB = BigInteger.valueOf(cidValue);
         BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1"));
         String key = "m"+resB;
         if(availableTargetNames.contains(key)){
             return key;
         }
         //couse_1, course_2
         throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
     }
 }

package com.zhz.shardingspheredemo.algorithm;
 
 import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
 import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
 
 import java.math.BigInteger;
 import java.util.Collection;
 
 
 /**
  * @Description: 精确的表分配算法
  *
  * @author zhouhengzhe
  * @date 2021/9/4下午3:24
  * @since
  */
 public class MyPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
     //select * from course where cid = ? or cid in (?,?)
     @Override
     public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
         String logicTableName = shardingValue.getLogicTableName();
         String cid = shardingValue.getColumnName();
         Long cidValue = shardingValue.getValue();
         //实现 course_$->{cid%2+1)
         BigInteger shardingValueB = BigInteger.valueOf(cidValue);
         BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1"));
         String key = logicTableName+"_"+resB;
         if(availableTargetNames.contains(key)){
             return key;
         }
         //couse_1, course_2
         throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
     }
 }

package com.zhz.shardingspheredemo.algorithm;
 
 import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
 import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
 
 import java.util.Arrays;
 import java.util.Collection;
 
 
 /**
  * @Description: 区间分配的分配算法（数据库）
  *
  * @author zhouhengzhe
  * @date 2021/9/4下午3:24
  * @since
  */
 public class MyRangeDSShardingAlgorithm implements RangeShardingAlgorithm<Long> {
     @Override
     public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
         //select * from course where cid between 1 and 100;
         Long upperVal = shardingValue.getValueRange().upperEndpoint();//100
         Long lowerVal = shardingValue.getValueRange().lowerEndpoint();//1
 
         String logicTableName = shardingValue.getLogicTableName();
         return Arrays.asList("m1","m2");
     }
 }

package com.zhz.shardingspheredemo.algorithm;
 
 import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
 import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
 import sun.rmi.runtime.Log;
 
 import java.util.Arrays;
 import java.util.Collection;
 
 
 /**
  * @Description: 区间分配的分片算法（表）
  *
  * @author zhouhengzhe
  * @date 2021/9/4下午3:24
  * @since
  */
 public class MyRangeTableShardingAlgorithm implements RangeShardingAlgorithm<Long> {
     @Override
     public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
         //select * from course where cid between 1 and 100;
         Long upperVal = shardingValue.getValueRange().upperEndpoint();//100
         Long lowerVal = shardingValue.getValueRange().lowerEndpoint();//1
 
         String logicTableName = shardingValue.getLogicTableName();
         return Arrays.asList(logicTableName+"_1",logicTableName+"_2");
     }
 }测试类   @Test
     public void queryOrderRange(){
         //select * from course
         QueryWrapper<Course> wrapper = new QueryWrapper<>();
         wrapper.between("cid",1434444618337914881L,1434444620539924482L);
 //        wrapper.in()
         List<Course> courses = courseMapper.selectList(wrapper);
         courses.forEach(course -> System.out.println(course));
     }

package com.zhz.shardingspheredemo.algorithm;
 
 import com.google.common.collect.Range;
 import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
 import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
 
 import java.math.BigInteger;
 import java.util.ArrayList;
 import java.util.Collection;
 import java.util.List;
 import java.util.Map;
 
 /**
  *
  * @author zhouhengzhe
  * @date 2021/9/4下午3:24
  * @since
  */
 public class MyComplexDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
 //    SELECT  cid,cname,user_id,cstatus  FROM course
 //    WHERE  cid BETWEEN ? AND ? AND user_id = ?
     @Override
     public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
         Range<Long> cidRange = shardingValue.getColumnNameAndRangeValuesMap().get("cid");
         Collection<Long> userIdCol = shardingValue.getColumnNameAndShardingValuesMap().get("user_id");
 
         Long upperVal = cidRange.upperEndpoint();
         Long lowerVal = cidRange.lowerEndpoint();
 
         List<String> res = new ArrayList<>();
 
         for(Long userId: userIdCol){
             //course_{userID%2+1}
             BigInteger userIdB = BigInteger.valueOf(userId);
             BigInteger target = (userIdB.mod(new BigInteger("2"))).add(new BigInteger("1"));
 
             res.add("m"+target);
         }
 
         return res;
     }
 }

package com.zhz.shardingspheredemo.algorithm;
 
 import com.google.common.collect.Range;
 import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
 import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
 
 import java.math.BigInteger;
 import java.util.ArrayList;
 import java.util.Collection;
 import java.util.List;
 /**
  *
  * @author zhouhengzhe
  * @date 2021/9/4下午3:24
  * @since
  */
 
 public class MyComplexTableShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
     @Override
     public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
         Range<Long> cidRange = shardingValue.getColumnNameAndRangeValuesMap().get("cid");
         Collection<Long> userIdCol = shardingValue.getColumnNameAndShardingValuesMap().get("user_id");
 
         Long upperVal = cidRange.upperEndpoint();
         Long lowerVal = cidRange.lowerEndpoint();
 
         List<String> res = new ArrayList<>();
 
         for(Long userId: userIdCol){
             //course_{userID%2+1}
             BigInteger userIdB = BigInteger.valueOf(userId);
             BigInteger target = (userIdB.mod(new BigInteger("2"))).add(new BigInteger("1"));
 
             res.add(shardingValue.getLogicTableName()+"_"+target);
         }
 
         return res;
     }
 }

@Test
     public void queryCourseComplex(){
         QueryWrapper<Course> wrapper = new QueryWrapper<>();
         wrapper.between("cid",1434444618337914881L,1434444620539924482L);
         wrapper.eq("user_id",1009L);
 //        wrapper.in()
         List<Course> courses = courseMapper.selectList(wrapper);
         courses.forEach(course -> System.out.println(course));
     }

package com.zhz.shardingspheredemo.algorithm;
 
 import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
 import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;
 
 import java.util.Arrays;
 import java.util.Collection;
 
 /**
  *
  * @author zhouhengzhe
  * @date 2021/9/4下午3:24
  * @since
  */
 
 public class MyHintTableShardingAlgorithm implements HintShardingAlgorithm<Integer> {
     @Override
     public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue) {
         String key = shardingValue.getLogicTableName() + "_" + shardingValue.getValues().toArray()[0];
         if(availableTargetNames.contains(key)){
             return Arrays.asList(key);
         }
         throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
     }
 }

@Test
     public void queryCourseByHint(){
         HintManager hintManager = HintManager.getInstance();
         hintManager.addTableShardingValue("course",2);
         List<Course> courses = courseMapper.selectList(null);
         courses.forEach(course -> System.out.println(course));
         hintManager.close();
     }

@Test
     public void addDict(){
         Dict d1 = new Dict();
         d1.setUstatus("1");
         d1.setUvalue("zc");
         dictMapper.insert(d1);
 
         Dict d2 = new Dict();
         d2.setUstatus("0");
         d2.setUvalue("bzc");
         dictMapper.insert(d2);
      dictMapper.insert(user);
     }

spring.shardingsphere.datasource.names=m1
 spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
 spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
 spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/coursedb?useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
 spring.shardingsphere.datasource.m1.username=root
 spring.shardingsphere.datasource.m1.password=root
 
 spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=m1.t_dict_$->{1..2}
 
 spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
 spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
 spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1
 spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.sharding-column=ustatus
 spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.algorithm-expression=t_dict_$->{ustatus.toInteger()%2+1}
 
 spring.shardingsphere.sharding.tables.user.actual-data-nodes=m1.t_user_$->{1..2}
 spring.shardingsphere.sharding.tables.user.key-generator.column=user_id
 spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
 spring.shardingsphere.sharding.tables.user.key-generator.props.worker.id=1
 spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=ustatus
 spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=t_user_$->{ustatus.toInteger()%2+1}
 #\u7ED1\u5B9A\u8868\u793A\u4F8B
 spring.shardingsphere.sharding.binding-tables[0]=user,t_dict
 
 #其他运行属性
 spring.shardingsphere.props.sql.show=true
 # 通过注册与现有定义同名的定义，设置是否允许覆盖 bean 定义 默认false
 spring.main.allow-bean-definition-overriding=true

@Test
     public void addDict(){
         Dict d1 = new Dict();
         d1.setUstatus("1");
         d1.setUvalue("zc");
         dictMapper.insert(d1);
 
         Dict d2 = new Dict();
         d2.setUstatus("0");
         d2.setUvalue("bzc");
         dictMapper.insert(d2);
 
         for(int i = 0 ; i < 10 ; i ++){
             User user = new User();
             user.setUsername("user No "+i);
             user.setUstatus(""+(i%2));
             user.setUage(i*10);
             userMapper.insert(user);
         }
     }

@Test
     public void queryUserStatus(){
         List<User> users = userMapper.queryUserStatus();
         users.forEach(user -> System.out.println(user));
     }