SpringBoot分库分表sharding-sphere2 接着上一篇：SpringBoot分库分表

接着上一篇：SpringBoot 分库分表sharding-sphere
上一篇的自定义分片算法有个错误DateShardingAlgorithm.java
该类的日期转换错误，正确如下：
package com.pack.sharding.algorithm;import java.time.ZoneId;import java.time.format.DateTimeFormatter;import java.util.Collection;import java.util.Date;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;public class DateTableShardingAlgorithm implements PreciseShardingAlgorithm { private static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");@Override public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {Date value = http://kandian.youth.cn/index/shardingValue.getValue() ;if (value == null) {value = new Date() ;}String actualTableName = shardingValue.getLogicTableName() +"_" + formatter.format(value.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime()) ;if (availableTargetNames.contains(actualTableName)) {return actualTableName ;}throw new UnsupportedOperationException("未知的表名称: " + actualTableName); } }接下来我们实现一个范围查询sql中 between...and
分片算法的字段是create_time 日期类型
当我们没有配置RangeShardingAlgorithm ，我们的sql中出现了between...and时这时候会对所有库的所有表进行查询。
范围查询DataTabeRangeShardingAlgorithm.java
DataTabeRangeShardingAlgorithm配置：
spring:shardingsphere:props:sql:show: truedatasource:names: ds0ds0:type: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://localhost:3306/ds0?serverTimezone=GMT%2B8username: rootpassword: 123123minimumIdle: 20maximumPoolSize: 200autoCommit: trueidleTimeout: 30000poolName: DS0DatabookHikariCPmaxLifetime: 1800000connectionTimeout: 30000connectionTestQuery: SELECT 1sharding:broadcastTables: t_usertables:t_order: #逻辑表名actualDataNodes: ds$->{0..0}.t_order_$->{2020..2020}0$->{1..9}, ds$->{0..0}.t_order_$->{2020..2020}$->{10..12} #实际表tableStrategy: #分表策略standard: #标准分片策略shardingColumn: create_time #分片字段preciseAlgorithmClassName: com.pack.sharding.algorithm.DateTableShardingAlgorithmrangeAlgorithmClassName: com.pack.sharding.algorithm.DataTabeRangeShardingAlgorithmrangeAlgorithmClassName: com.pack.sharding.algorithm.DataTabeRangeShardingAlgorithm
这是配置的范围，用于支持BETWEEN AND, >, <, >=, <= 分?
OrdersMapper.xml
SELECT * FROM T_ORDER T WHERE 1 = 1注意：AND create_time between #{params.startDate} and #{params.endDate}
create_time是分片列，如果这里的#{params.startDate}、#{params.endDate}两个查询参数值用了函数，如：DATE_FORMAT(#{params.endDate},'%Y-%m-%d')那么我们的分片算法将不会生效，将会进行全库全表的查询是哪里配置理解错误？
测试：
查询参数：
文章插图
执行结果：
文章插图
【SpringBoot分库分表sharding-sphere2】实际我们分了12张表，加入范围查询后根据入参startDate和endDate把查询表限定在了这两个日期之间。
当sql 是如下形式也是生效的：
= #{params.startDate} and create_time <= #{params.endDate}]]> 完毕！！！