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;
}
}