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

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

1、加入依赖文件:

      <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0.M2</version>
         </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>3.0.0.M2</version>
        </dependency>

2.配置properties文件

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

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

    <bean id="shardingDataSource" class="com.alibaba.druid.pool.DruidDataSource"
        init-method="init" destroy-method="close">
        <property name="driverClassName" value="${sharding.jdbc.driver}" />
        <property name="url" value="${sharding.jdbc.url}" />
        <property name="username" value="${sharding.jdbc.username}" />
        <property name="password" value="${sharding.jdbc.password}" />
        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait" value="6000" />

        <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
    
        <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="300000" />
    
        <property name="validationQuery" value="SELECT 'x'" />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />

        <!-- 打开PSCache,并且指定每个连接上PSCache的大小 -->
        <property name="poolPreparedStatements" value="false" />
        <property name="maxPoolPreparedStatementPerConnectionSize"
            value="20" />
        <!-- 配置监控统计拦截的filters -->
        <property name="filters" value="stat" /> 
    </bean>
   <!-- 配置分表算法类,后面会给出这个类 --> <bean id="shardingAlgorithm" class="com.example.shardingsphere.util.ShardingAlgorithm" /> <!-- 根据"id"字段来分表 t_sentinel_metric是要主表名称--> <sharding:standard-strategy id="tableShardingStrategy" sharding-column="id" precise-algorithm-ref="shardingAlgorithm" /> <sharding:data-source id="shardingDS"> <sharding:sharding-rule data-source-names="shardingDataSource"> <sharding:table-rules> <sharding:table-rule logic-table="t_sentinel_metric" table-strategy-ref="tableShardingStrategy" /> </sharding:table-rules> </sharding:sharding-rule> </sharding:data-source> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="shardingDS" /> <property name="mapperLocations" value="classpath*:mapper/**/*Mapper.xml" /> <property name="typeAliasesPackage" value="com.example.shardingsphere.dao.model" /> </bean> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.example.shardingsphere.dao.mapper" /> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /> <!-- <property name="annotationClass" value="com.gmall88.server.mybatis.MyBatisRepository"/> --> </bean> <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate" scope="prototype"> <constructor-arg index="0" ref="sqlSessionFactory" /> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="shardingDS"></property> </bean>

4、分表算法类ShardingAlgorithm

public class ShardingAlgorithm implements PreciseShardingAlgorithm<String>{

    /** 
     * sql 中 = 操作时,table的映射 
   *  根据传进来的日期命名表名称
*/ @Override public String doSharding(Collection<String> tableNames, PreciseShardingValue<String> 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;
    }
 
原文地址:https://www.cnblogs.com/ouyanxia/p/11918627.html