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