sharding-jdbc分表分库按时间字段

https://blog.csdn.net/myshy025tiankong/article/details/83063887
感谢大佬的文章，相当精彩

首先，数据库

create database db_201810;
create database db_201811;
 
use db_201810;
create table gps_20181014(id int not null auto_increment,gprs varchar(16) NOT NULL,sys_time datetime,PRIMARY KEY (id));
create table gps_20181015(id int not null auto_increment,gprs varchar(16) NOT NULL,sys_time datetime,PRIMARY KEY (id));
insert into gps_20181014 values(0,'0123456789012345','2018-10-14 0:0:0');
insert into gps_20181015 values(0,'0123456789012345','2018-10-15 0:0:0');
 
use db_201811;
create table gps_20181114(id int not null auto_increment,gprs varchar(16) NOT NULL,sys_time datetime,PRIMARY KEY (id));
create table gps_20181115(id int not null auto_increment,gprs varchar(16) NOT NULL,sys_time datetime,PRIMARY KEY (id));
insert into gps_20181114 values(0,'0123456789012345','2018-11-14 0:0:0');
insert into gps_20181115 values(0,'0123456789012345','2018-11-15 0:0:0');

pom.xml文件中加入

<!--sharding-jdbc --> 
        <dependency>
            <groupId>io.shardingjdbc</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>2.0.3</version>
        </dependency>

        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.3</version>
        </dependency>

扫描dao层，DataSourceConfig

package com.bbg.shardingtest.config;
 /**
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @author xwq
 * @date 2019/11/20 002014:54
 */


import org.apache.commons.dbcp.BasicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;


import io.shardingjdbc.core.api.config.ShardingRuleConfiguration;
import io.shardingjdbc.core.api.config.TableRuleConfiguration;
import io.shardingjdbc.core.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingjdbc.core.jdbc.core.datasource.ShardingDataSource;


import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author xwq
 * @create 2019-11-20 14:54
 **/

@Configuration
@MapperScan(basePackages = "com.bbg.shardingtest.dao", sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig {

    //配置sharding-jdbc的DataSource，给上层应用使用，这个DataSource包含所有的逻辑库和逻辑表，应用增删改查时，修改对应sql
    //然后选择合适的数据库继续操作。因此这个DataSource创建很重要。
    @Bean
    @Primary
    public DataSource shardingDataSource() throws SQLException {

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        //用户表配置，可以添加多个配置
        shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration());
//        shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration1());
        shardingRuleConfig.getBindingTableGroups().add("gps");

        //设置数据库策略，传入的是sys_time
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("sys_time", DatabaseShardingAlgorithm.class.getName()));
        //设置数据表策略，传入的是sys_time
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("sys_time", TableShardingAlgorithm.class.getName()));


        return new ShardingDataSource(shardingRuleConfig.build(createDataSourceMap()));
    }

    //创建用户表规则
    @Bean
    TableRuleConfiguration getUserTableRuleConfiguration() {
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();

        orderTableRuleConfig.setLogicTable("gps");
        //设置数据节点，格式为dbxx.tablexx。这里的名称要和map的别名一致。下面两种方式都可以
        //orderTableRuleConfig.setActualDataNodes("db_${0..1}.gps_${0..1}");
        orderTableRuleConfig.setActualDataNodes("db_201810.gps_20181014,db_201810.gps_20181015,db_201810.gps_20181114,db_201810.gps_20181115,db_201811.gps_20181114,db_201811.gps_20181115");
        //设置纵列名称
        orderTableRuleConfig.setKeyGeneratorColumnName("sys_time");
        return orderTableRuleConfig;
    }

    @Bean
    TableRuleConfiguration getUserTableRuleConfiguration1() {
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
        //设置用户表逻辑
        orderTableRuleConfig.setLogicTable("tb_user");
        //设置数据节点，格式为dbxx.tablexx。这里的名称要和map的别名一致
        orderTableRuleConfig.setActualDataNodes("gps_com.tb_user");

        return orderTableRuleConfig;
    }




    //下面函数是获取数据源，即包含有多少个数据库，读入到系统中存放于map中
    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> result = new HashMap<>();

        result.put("gps_com", createDataSource("jdbc:mysql://localhost:3306/gps_com?characterEncoding=utf8&useSSL=false"));
        result.put("db_201810", createDataSource("jdbc:mysql://localhost:3306/db_201810?characterEncoding=utf8&useSSL=false"));
        result.put("db_201811", createDataSource("jdbc:mysql://localhost:3306/db_201811?characterEncoding=utf8&useSSL=false"));
        return result;
    }

    private DataSource createDataSource(final String dataSourceName) {
        //使用默认连接池
        BasicDataSource result = new BasicDataSource();
        result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());
        //设置数据库路径
        result.setUrl(dataSourceName);
        //设置数据库用户名
        result.setUsername("root");
        //设置数据密码
        result.setPassword("123456");
        return result;
    }


    /**
     * 需要手动配置事务管理器
     */
    @Bean
    public DataSourceTransactionManager transactitonManager(DataSource shardingDataSource) {
        return new DataSourceTransactionManager(shardingDataSource);
    }


    //下面是SessionFactory配置，二选一，由类前语句选择  @MapperScan(basePackages = "com.net.domain", sqlSessionFactoryRef = "sqlSessionFactory")
    @Bean
    @Primary
    public SqlSessionFactory sqlSessionFactory(DataSource shardingDataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(shardingDataSource);
        return sessionFactory.getObject();
    }



}

DatabaseShardingAlgorithm库名的处理方法

package com.bbg.shardingtest.config;
/**
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @author xwq
 * @date 2019/11/20 002015:03
 */

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

/**
 *
 * @author xwq
 * @create 2019-11-20 15:03
 **/
public class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm<String> {



    //库名的处理
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {

       
       


        String db_name="db_";
        try {
            Date date = (Date) new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(preciseShardingValue.getValue());

            String year = String.format("%tY", date);
            String mon  = String.format("%tm",date);
            db_name=db_name+year+mon;
            System.out.println("db_name:" + db_name);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        for (String each : collection) {
            System.out.println("db:" + each);
            if (each.equals(db_name)) {
                return each;
            }
        }

        throw new IllegalArgumentException();
    }

}

TableShardingAlgorithm表名的处理方法

package com.bbg.shardingtest.config;
   /**
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @author xwq
 * @date 2019/11/20 002015:05
 */

/**
 *
 * @author xwq
 * @create 2019-11-20 15:05
 **/

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;


public class TableShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    //表名的处理
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String tb_name=preciseShardingValue.getLogicTableName() + "_";
        try {
            Date date = (Date) new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(preciseShardingValue.getValue());

            String year = String.format("%tY", date);
            String mon  = String.format("%tm",date);
            String dat  = String.format("%td",date);
            tb_name=tb_name+year+mon+dat;
            System.out.println("tb_name:" + tb_name);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        for (String each : collection) {
            System.out.println("tb:" + each);

            if (each.equals(tb_name)) {
                return each;
            }
        }

        throw new IllegalArgumentException();
    }


}

使用

控制类TestSpringBoot

package com.bbg.shardingtest.controller;    /**
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @author xwq
 * @date 2019/11/20 002019:00
 */

import com.bbg.shardingtest.dto.GpsData;
import com.bbg.shardingtest.service.GpsDataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.util.List;

/**
 *
 * @author xwq
 * @create 2019-11-20 19:00
 **/
@RestController
public class TestSpringBoot {

    @Autowired
    @Qualifier("gpsDataService")
    private GpsDataService gpsDataService;


    @RequestMapping("/log")
    public String login(HttpServletRequest request)
    {
        String time = request.getParameter("time");//"2018-10-14 0:0:0"
        System.out.println(time);

//        User user = userService.login("root", "123456");
//        System.out.println(user.toString());

        List<GpsData> GpsData_list = gpsDataService.getAll(time);

        System.out.println(GpsData_list.get(0).getGprs());
        System.out.println(GpsData_list.get(0).getSysTime());

        return "OK";
    }
}

Service

package com.bbg.shardingtest.service;
  /**
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @author xwq
 * @date 2019/11/20 002015:58
 */


import com.bbg.shardingtest.dto.GpsData;

import java.util.List;

/**
 *
 * @author xwq
 * @create 2019-11-20 15:58
 **/
public interface GpsDataService {

    List<GpsData> getAll(String sysTime);

}

package com.bbg.shardingtest.service.impl;
/**
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @author xwq
 * @date 2019/11/20 002015:59
 */


import com.bbg.shardingtest.dao.GpsDataMapper;
import com.bbg.shardingtest.dto.GpsData;
import com.bbg.shardingtest.service.GpsDataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 *
 * @author xwq
 * @create 2019-11-20 15:59
 **/

@Service("gpsDataService")
public class GpsDataServiceImpl implements GpsDataService {

    @Autowired
    private GpsDataMapper gpsDataMapper;

    @Override
    public List<GpsData> getAll(String sysTime)
    {
        return gpsDataMapper.findAll(sysTime);
    }


}

dao

package com.bbg.shardingtest.dao;
/**
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @author xwq
 * @date 2019/11/20 002015:55
 */

import com.bbg.shardingtest.dto.GpsData;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 *
 * @author xwq
 * @create 2019-11-20 15:55
 **/

//@Mapper
public interface GpsDataMapper  {

    @Select("select id,gprs,sys_time as sysTime from gps where sys_time = #{sysTime}")
    List<GpsData> findAll(@Param("sysTime") String sysTime);

}

dto

package com.bbg.shardingtest.dto;
/**
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @author xwq
 * @date 2019/11/20 002015:53
 */

import java.io.Serializable;

/**
 *
 * @author xwq
 * @create 2019-11-20 15:53
 **/
public class GpsData implements Serializable {
    private static final long serialVersionUID = 1L;
    private String gprs;
    private String sysTime;

    public String getGprs() {
        return gprs;
    }

    public void setGprs(String gprs) {
        this.gprs = gprs;
    }

    public String getSysTime() {
        return sysTime;
    }

    public void setSysTime(String sysTime) {
        this.sysTime = sysTime;
    }
}