springboot整合sharding-jdbc实现按月份分表mysql

这里只是简单的根据月份来进行分表，效果如图所示：

1、加入依赖文件:

  　　  
            io.shardingsphere
            sharding-jdbc-spring-boot-starter
            3.0.0.M2
         
        
            io.shardingsphere
            sharding-jdbc-spring-namespace
            3.0.0.M2
        

2.配置properties文件

sharding.jdbc.driver=com.mysql.jdbc.Driver
sharding.jdbc.url=jdbc:mysql://ip:3306/gw_dashboard?useUnicode=true&characterEncoding=utf8mb4
sharding.jdbc.username=user
sharding.jdbc.password=password

3、配置sharding-config.xml文件（在mybatis-config的基础的改进的）

    class="com.alibaba.druid.pool.DruidDataSource"
        init-method="init" destroy-method="close">
        
        
        
        
        
        

        
        
    
        
        
    
        
        
        
        

        
        
        
            value="20" />
        
         
    
　　　
    class="com.example.shardingsphere.util.ShardingAlgorithm" />
     
    
        sharding-column="id" precise-algorithm-ref="shardingAlgorithm" />
    
        
            
                t_sentinel_metric"
                    table-strategy-ref="tableShardingStrategy" />
            
        
    
    class="org.mybatis.spring.SqlSessionFactoryBean">
        
        
            value="classpath*:mapper/**/*Mapper.xml" />
        
    
    class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        
        
        
    
    class="org.mybatis.spring.SqlSessionTemplate"
        scope="prototype">
        
    
    class="org.springframework.jdbc.core.JdbcTemplate">
        
    

4、分表算法类ShardingAlgorithm

public class ShardingAlgorithm implements PreciseShardingAlgorithm{

    /** 
     * sql 中 = 操作时，table的映射 
　　　*　　根据传进来的日期命名表名称
     */  
    @Override
    public String doSharding(Collection tableNames, PreciseShardingValue shardingValue) {  
        String tableName = shardingValue.getLogicTableName();
        String key = shardingValue.getValue().substring(0, 6);
        return tableName.concat("_").concat(key);
    }
    
}

5、创建分表，可做一个定时器，定时去创建表

　　@Autowired
    private JdbcTemplate jdbcTemplate;
    private static DateFormat millisdf = new SimpleDateFormat("yyyyMMddHHmmssSS");

    public static String getLogID() {
        return getmillisTime() + ((int) (Math.random() * 1000) + 1000);
    }

    public static String getmillisTime() {
        Calendar c = Calendar.getInstance();
        c.setTime(new Date());
        c.add(Calendar.MONTH, +1);
        Date m = c.getTime();
        return millisdf.format(m);
    }

    @RequestMapping(value = "/createSentinelTable")
    @ResponseBody
    public Object createSentinelTable(HttpServletResponse response) throws Exception {
        ReturnResult result = new ReturnResult();
        Connection conn = null;
        ResultSet rs = null;
        try {

            String tableName = "t_sentinel_metric";
            String ym = getLogID().substring(0, 6);
            String table = tableName.concat("_").concat(ym);
            conn = jdbcTemplate.getDataSource().getConnection();
            rs = conn.getMetaData().getTables(null, null, table, null);
            if (rs.next()) {
                logger.info("table is exist!");
            } else {
                String c_sql = "CREATE TABLE "+ table +"( id varchar(100) NOT NULL,  gmt_create datetime DEFAULT NULL,"
                        + "  gmt_modified datetime DEFAULT NULL,  app varchar(100) DEFAULT NULL,  _timestamp datetime DEFAULT NULL,"
                        + " resource varchar(500) DEFAULT NULL, pass_qps bigint(11) DEFAULT '0',  success_qps bigint(11) DEFAULT '0',"
                        + " block_qps bigint(11) DEFAULT '0',  exception_qps int(11) DEFAULT '0',  rt double DEFAULT NULL, _count int(11) DEFAULT '0',"
                        + "  resource_code int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `INDEX_TIMESTAMP` (`_timestamp`),"
                        + "  KEY `INDEX_TSP_RESOURCE` (`_timestamp`,`resource`)) ";
                jdbcTemplate.execute(c_sql);
                logger.info("create table success!");
            }
            result.setCode(ReturnCodeType.SUCCESS);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            result.setCode(ReturnCodeType.FAILURE);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return result;
    }

生成表结构如图：

6、插入一条记录，在分库分表的时候要根据实际情况来决定根据哪个字段来分（不一定都是主键）;我这里用的是主键，

不能像以前一样使用数据库自增的主键了，会出现主键重复的问题

private static DateFormat millisdf = new SimpleDateFormat("yyyyMMddHHmmssSS");
    public static String getLogID() {
    return getmillisTime()
        + ((int) (Math.random() * 1000)+1000);
    }
    public static String getmillisTime() {
        return millisdf.format(new Date());
    }

public ReturnResult addSentinelMetric() {
        ReturnResult result = new ReturnResult();
        SentinelMetric sm = new SentinelMetric();
        sm.setId(getLogID());//配置分配的id，决定分配到哪个分表     
        sm.setResource("demo");
        sm.setApp("demo");
        sm.setPassQps(1l);
        sm.setBlockQps(1l);
        sm.setSuccessQps(1l);
        sm.setExceptionQps(1);
        sm.setRt(1d);
        sm.setCount(1);
        sm.setTimestamp(new Date());
        sm.setGmtModified(new Date());
        smMapper.insertSelective(sm);
        result.setCode(ReturnCodeType.SUCCESS).setMessage("添加成功");
        return result;
    }