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.sql.jdbc.Driver
sharding.jdbc.url=jdbc:mysql://ip:3306/gw_dashboard?useUnicode=true&characterEncoding=utf8mb4
sharding.jdbc.username=user
sharding.jdbc.password=password
3、配置l⽂件（在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="ample.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>
springboot推荐算法<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="batis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="shardingDS" />
<property name="mapperLocations"
value="classpath*:mapper/**/*l" />
<property name="typeAliasesPackage" value="ample.del" />
</bean>
<bean class="batis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="ample.shardingsphere.dao.mapper" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
<!-- <property name="annotationClass" value="batis.MyBatisRepository"/> -->
</bean>
<bean id="sqlSession" class="batis.spring.SqlSessionTemplate"
scope="prototype">
<constructor-arg index="0" ref="sqlSessionFactory" />
</bean>
<bean id="jdbcTemplate" class="org.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 = LogicTableName();
String key = Value().substring(0, 6);
at("_").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 = Instance();
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 = at("_").concat(ym);
conn = DataSource().getConnection();
rs = MetaData().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`)) ";
logger.info("create table success!");
}
result.setCode(ReturnCodeType.SUCCESS);
} catch (Exception e) {
<(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;
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论