一、行表达式策略:
1、application.properties:
server.context-path=/sharding
server.port=7777
mybatis.mapper-locations=classpath*:Mapper/*Mapper.xml
# 配置数据源,(db3以及t_user_3实际上均无数据,这里测试是否为全路由)
spring.shardingsphere.datasource.names=db1,db2,db3
#配置db1
spring.shardingsphere.datasource.db1.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding_demo_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=******
#配置db2
spring.shardingsphere.datasource.db2.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding_demo_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=******
#配置db3
spring.shardingsphere.datasource.db3.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db3.url=jdbc:mysql://localhost:3306/sharding_demo_3?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db3.username=root
spring.shardingsphere.datasource.db3.password=******
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
#配置t_user表的分布,配置主键id为雪花算法自增长
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=db$->{1..3}.t_user_$->{1..3}
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
#配置t_user表的分库策略,age为分片键,为偶数在db1库,奇数在db2库
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=db$->{age % 2 + 1}
#配置t_user表的分表策略,id为分片键,为偶数在1表,奇数在2表
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{id % 2+1}
2、测试:
(1)批量新增:
@Override
public void batchInsert() {
List<UserDTO> users = new ArrayList<>();
for(int i=0;i<6;i++){
UserDTO userDTO = new UserDTO();
userDTO.setUserName("张三"+i);
userDTO.setAge(i);
users.add(userDTO);
}
userMapper.batchInsert(users);
}
<insert id="batchInsert">
insert into t_user(user_name,age) values
<foreach collection="users" item="item" separator=",">
(#{item.userName},#{item.age})
</foreach>
</insert>
访问localhost:7777/sharding/user/batchInsert
后台日志打印
2021-12-22 14:51:21.800 INFO 12300 --- [nio-7777-exec-2] ShardingSphere-SQL : Rule Type: sharding
2021-12-22 14:51:21.802 INFO 12300 --- [nio-7777-exec-2] ShardingSphere-SQL : Logic SQL: insert into t_user(user_name,age) values
(?,?)
,
(?,?)
,
(?,?)
,
(?,?)
,
(?,?)
,
(?,?)
2021-12-22 14:51:21.802 INFO 12300 --- [nio-7777-exec-2] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=1})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=3})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=5})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=7})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=9})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=11})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=18)], parametersIndex=12, logicSQL=insert into t_user(user_name,age) values
(?,?)
,
(?,?)
,
(?,?)
,
(?,?)
,
(?,?)
,
(?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[user_name, age], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@e3c4100, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6785da2d]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@55999193, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@4d28a3b8]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2170df5c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@686d3ded]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@71612c7a, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@795c748d]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5e71cec5, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5ff202b3]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2788e7cb, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3dea17ab])])
2021-12-22 14:51:21.802 INFO 12300 --- [nio-7777-exec-2] ShardingSphere-SQL : Actual SQL: db1 ::: insert into t_user_2 (user_name, age, id) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) ::: [张三0, 0, 680426342226853889, 张三2, 2, 680426342226853891, 张三4, 4, 680426342226853893]
2021-12-22 14:51:21.803 INFO 12300 --- [nio-7777-exec-2] ShardingSphere-SQL : Actual SQL: db2 ::: insert into t_user_1 (user_name, age, id) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) ::: [张三1, 1, 680426342226853890, 张三3, 3, 680426342226853892, 张三5, 5, 680426342226853894]
查看数据库:分片成功
(2)查询:
<select id="getById" resultType="com.demo.model.UserDTO">
select id,user_name userName,age from t_user where id = #{id}
</select>
访问localhost:7777/sharding/user/getById?id=680428938429726720,返回:
因为查询条件只有分表键,所以是全库路由
2021-12-22 15:03:50.183 INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL : Rule Type: sharding
2021-12-22 15:03:50.183 INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL : Logic SQL: select id,user_name userName,age from t_user where id = ?
2021-12-22 15:03:50.183 INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=id, tableName=t_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select id,user_name userName,age from t_user where id = ?)), containStar=false, firstSelectItemStartIndex=7, selectListStopIndex=31, groupByLastIndex=0, items=[CommonSelectItem(expression=id, alias=Optional.absent()), CommonSelectItem(expression=user_name, alias=Optional.of(userName)), CommonSelectItem(expression=age, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-12-22 15:03:50.184 INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL : Actual SQL: db1 ::: select id,user_name userName,age from t_user_1 where id = ? ::: [680428938429726720]
2021-12-22 15:03:50.184 INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL : Actual SQL: db2 ::: select id,user_name userName,age from t_user_1 where id = ? ::: [680428938429726720]
2021-12-22 15:03:50.184 INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL : Actual SQL: db3 ::: select id,user_name userName,age from t_user_1 where id = ? ::: [680428938429726720]
而
<select id="getByIdAndAge" resultType="com.demo.model.UserDTO">
select id,user_name userName,age from t_user where id = #{id}
and age = #{age}
</select>
执行localhost:7777/sharding/user/getByIdAndAge?id=680428938429726720&age=1,返回结果相同,因为查询条件包含了库分片键和表分片键,所以是精准路由
2021-12-22 15:08:51.626 INFO 1688 --- [nio-7777-exec-3] ShardingSphere-SQL : Rule Type: sharding
2021-12-22 15:08:51.626 INFO 1688 --- [nio-7777-exec-3] ShardingSphere-SQL : Logic SQL: select id,user_name userName,age from t_user where id = ?
and age = ?
2021-12-22 15:08:51.626 INFO 1688 --- [nio-7777-exec-3] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=id, tableName=t_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0}), Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=select id,user_name userName,age from t_user where id = ?
and age = ?)), containStar=false, firstSelectItemStartIndex=7, selectListStopIndex=31, groupByLastIndex=0, items=[CommonSelectItem(expression=id, alias=Optional.absent()), CommonSelectItem(expression=user_name, alias=Optional.of(userName)), CommonSelectItem(expression=age, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-12-22 15:08:51.626 INFO 1688 --- [nio-7777-exec-3] ShardingSphere-SQL : Actual SQL: db2 ::: select id,user_name userName,age from t_user_1 where id = ?
and age = ? ::: [680428938429726720, 1]
(3)in查询:
<select id="inQuery" resultType="com.demo.model.UserDTO">
select id,user_name userName,age from t_user where id in
<foreach collection="ids" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
and age in
<foreach collection="ages" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</select>
访问localhost:7777/sharding/user/inQuery?ids=680426342226853893,680426342226853894,680426342226853889&ages=0,1,2,3
后台日志打印
2021-12-22 15:48:49.494 INFO 13152 --- [nio-7777-exec-7] ShardingSphere-SQL : Actual SQL: db1 ::: select id,user_name userName,age from t_user_1 where id in
(
?
,
?
,
?
)
and age in
(
?
,
?
,
?
,
?
) ::: [680426342226853893, 680426342226853894, 680426342226853889, 0, 1, 2, 3]
2021-12-22 15:48:49.494 INFO 13152 --- [nio-7777-exec-7] ShardingSphere-SQL : Actual SQL: db1 ::: select id,user_name userName,age from t_user_2 where id in
(
?
,
?
,
?
)
and age in
(
?
,
?
,
?
,
?
) ::: [680426342226853893, 680426342226853894, 680426342226853889, 0, 1, 2, 3]
2021-12-22 15:48:49.494 INFO 13152 --- [nio-7777-exec-7] ShardingSphere-SQL : Actual SQL: db2 ::: select id,user_name userName,age from t_user_1 where id in
(
?
,
?
,
?
)
and age in
(
?
,
?
,
?
,
?
) ::: [680426342226853893, 680426342226853894, 680426342226853889, 0, 1, 2, 3]
2021-12-22 15:48:49.495 INFO 13152 --- [nio-7777-exec-7] ShardingSphere-SQL : Actual SQL: db2 ::: select id,user_name userName,age from t_user_2 where id in
(
?
,
?
,
?
)
and age in
(
?
,
?
,
?
,
?
) ::: [680426342226853893, 680426342226853894, 680426342226853889, 0, 1, 2, 3]
其他以此类推。
二、标准分片策略&按年分库+按月分表:
建立sharding_demo_2020、sharding_demo_2021两个库,每个库建立
t_order_1到t_order_12共12张表
CREATE TABLE `t_order_1` (
`id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
`order_number` varchar(255) DEFAULT NULL COMMENT '订单号',
`order_date` date DEFAULT NULL COMMENT '下单时间',
`user_id` bigint(20) DEFAULT NULL,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(我这里只建了1和12两个月表)
1、application.properties:
server.context-path=/sharding
server.port=6666
mybatis.mapper-locations=classpath*:Mapper/*Mapper.xml
# 配置数据源
spring.shardingsphere.datasource.names=sharding_demo_2020,sharding_demo_2021
#配置db1
spring.shardingsphere.datasource.sharding_demo_2020.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding_demo_2020.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding_demo_2020.url=jdbc:mysql://localhost:3306/sharding_demo_2020?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.sharding_demo_2020.username=root
spring.shardingsphere.datasource.sharding_demo_2020.password=******
#配置db2
spring.shardingsphere.datasource.sharding_demo_2021.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding_demo_2021.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding_demo_2021.url=jdbc:mysql://localhost:3306/sharding_demo_2021?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.sharding_demo_2021.username=root
spring.shardingsphere.datasource.sharding_demo_2021.password=******
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
#配置t_order库、表的分布,配置主键id为雪花算法自增长
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=sharding_demo_$->{2020..2021}.t_order_$->{1..12}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
#配置t_order表的分库策略,按年分库
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.preciseAlgorithmClassName=com.demo.config.OrderDbPreciseShardingConfig
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.rangeAlgorithmClassName=com.demo.config.OrderDbRangeShardingConfig
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=order_date
#配置t_order表的分表策略,按月分表
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.preciseAlgorithmClassName=com.demo.config.OrderTablePreciseShardingConfig
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.rangeAlgorithmClassName=com.demo.config.OrderTableRangeShardingConfig
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_date
2、分片策略:
(1)精准分库:
package com.demo.config;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
/**
* 分库
* sharding_demo_yyyy
*/
public class OrderDbPreciseShardingConfig implements PreciseShardingAlgorithm<Date> {
//精准
@Override
public String doSharding(Collection<String> collection,
PreciseShardingValue<Date> preciseShardingValue) {
Date value = preciseShardingValue.getValue();
if(value == null){
throw new UnsupportedOperationException("prec is null");
}
SimpleDateFormat format = new SimpleDateFormat("yyyy");
String dbName= "sharding_demo_"+format.format(value);
System.out.println("dbName:"+dbName);
for (String collectionDbName : collection) {//循环表名已确定使用哪张表
if (collectionDbName.equals(dbName)){ //表示相等就返回
System.out.println("库名为"+collectionDbName);
return collectionDbName;
}
}
return null;
}
}
(2)范围分库:
package com.demo.config;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
public class OrderDbRangeShardingConfig implements RangeShardingAlgorithm<Date> {
@Override
public Collection<String> doSharding(Collection<String> collection,
RangeShardingValue<Date> rangeShardingValue) {
Range<Date> dates = rangeShardingValue.getValueRange();
Date minDate = dates.lowerEndpoint();
Date maxDate = dates.upperEndpoint();
List<String> dbs = new ArrayList<>();
for(String dbName : collection){
SimpleDateFormat format = new SimpleDateFormat("yyyy");
String dbName1 = "sharding_demo_"+format.format(minDate);
String dbName2 = "sharding_demo_"+format.format(maxDate);
if(dbName.equals(dbName1) || dbName.equals(dbName2)){
dbs.add(dbName);
}
}
return dbs;
}
}
(3)精准分表:
package com.demo.config;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
/**
* 分表
* t_order_M
*/
public class OrderTablePreciseShardingConfig implements PreciseShardingAlgorithm<Date> {
//精准
@Override
public String doSharding(Collection<String> collection,
PreciseShardingValue<Date> preciseShardingValue) {
Date value = preciseShardingValue.getValue();
if(value == null){
throw new UnsupportedOperationException("prec is null");
}
SimpleDateFormat format = new SimpleDateFormat("M");
String tableName= "t_order_"+format.format(value);
System.out.println("tableName:"+tableName);
for (String collectionTableName : collection) {//循环表名已确定使用哪张表
if (collectionTableName.equals(tableName)){ //表示相等就返回
System.out.println("表名为"+tableName);
return tableName;
}
}
return null;
}
}
(4)范围分表:
package com.demo.config;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
public class OrderTableRangeShardingConfig implements RangeShardingAlgorithm<Date> {
@Override
public Collection<String> doSharding(Collection<String> collection,
RangeShardingValue<Date> rangeShardingValue) {
Range<Date> dates = rangeShardingValue.getValueRange();
Date minDate = dates.lowerEndpoint();
Date maxDate = dates.upperEndpoint();
List<String> tables = new ArrayList<>();
for(String tableName : collection){
SimpleDateFormat format = new SimpleDateFormat("M");
String tableName1 = "t_order_"+format.format(minDate);
String tableName2 = "t_order_"+format.format(maxDate);
if(tableName.equals(tableName1) || tableName.equals(tableName2)){
tables.add(tableName);
}
}
return tables;
}
}
3、测试:
(1)批量新增
<insert id="batchInsert" parameterType="com.demo.model.OrderDTO">
insert into t_order(order_number,order_date) values
<foreach collection="orderDTOs" separator="," item="item">
(#{item.orderNumber},#{item.orderDate})
</foreach>
</insert>
后台日志打印:
dbName:sharding_demo_2021
库名为sharding_demo_2021
tableName:t_order_12
表名为t_order_12
dbName:sharding_demo_2020
库名为sharding_demo_2020
tableName:t_order_1
表名为t_order_1
dbName:sharding_demo_2020
库名为sharding_demo_2020
tableName:t_order_12
表名为t_order_12
2021-12-23 10:58:18.769 INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL : Rule Type: sharding
2021-12-23 10:58:18.769 INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL : Logic SQL: insert into t_order(order_number,order_date) values
(?,?)
,
(?,?)
,
(?,?)
2021-12-23 10:58:18.769 INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=order_date, tableName=t_order), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=1})]), AndCondition(conditions=[Condition(column=Column(name=order_date, tableName=t_order), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=3})]), AndCondition(conditions=[Condition(column=Column(name=order_date, tableName=t_order), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=5})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=19)], parametersIndex=6, logicSQL=insert into t_order(order_number,order_date) values
(?,?)
,
(?,?)
,
(?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[order_number, order_date], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5a99b219, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2c4576d]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@172b3af1, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@649b583b]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@738537cb, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@535a69e9])])
2021-12-23 10:58:18.770 INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL : Actual SQL: sharding_demo_2021 ::: insert into t_order_12 (order_number, order_date, id) VALUES (?, ?, ?) ::: [aaa, 2021-12-21 00:00:10.0, 680730081433223168]
2021-12-23 10:58:18.770 INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL : Actual SQL: sharding_demo_2020 ::: insert into t_order_1 (order_number, order_date, id) VALUES (?, ?, ?) ::: [bbb, 2020-01-21 00:00:10.0, 680730081433223169]
2021-12-23 10:58:18.770 INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL : Actual SQL: sharding_demo_2020 ::: insert into t_order_12 (order_number, order_date, id) VALUES (?, ?, ?) ::: [ccc, 2020-12-21 00:00:10.0, 680730081433223170]
验证分库分表成功:
(2)between and
<select id="betweenAndQuery" resultType="com.demo.model.OrderDTO">
select id,order_number orderNumber,order_date orderDate
from t_order where order_date between #{minDate}
and #{maxDate}
</select>
访问localhost:6666/sharding/order/betweenAndQuery?minDate=2020/1/20 00:00:00&maxDate=2021/12/22 00:00:10返回
后台日志打印:
2021-12-23 11:07:13.749 INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL : Logic SQL: select id,order_number orderNumber,order_date orderDate
from t_order where order_date between ?
and ?
2021-12-23 11:07:13.750 INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=order_date, tableName=t_order), operator=BETWEEN, compareOperator=null, positionValueMap={}, positionIndexMap={0=0, 1=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=select id,order_number orderNumber,order_date orderDate
from t_order where order_date between ?
and ?)), containStar=false, firstSelectItemStartIndex=7, selectListStopIndex=54, groupByLastIndex=0, items=[CommonSelectItem(expression=id, alias=Optional.absent()), CommonSelectItem(expression=order_number, alias=Optional.of(orderNumber)), CommonSelectItem(expression=order_date, alias=Optional.of(orderDate))], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-12-23 11:07:13.751 INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL : Actual SQL: sharding_demo_2020 ::: select id,order_number orderNumber,order_date orderDate
from t_order_1 where order_date between ?
and ? ::: [2020-01-20 00:00:00.0, 2021-12-22 00:00:10.0]
2021-12-23 11:07:13.751 INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL : Actual SQL: sharding_demo_2020 ::: select id,order_number orderNumber,order_date orderDate
from t_order_12 where order_date between ?
and ? ::: [2020-01-20 00:00:00.0, 2021-12-22 00:00:10.0]
2021-12-23 11:07:13.751 INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL : Actual SQL: sharding_demo_2021 ::: select id,order_number orderNumber,order_date orderDate
from t_order_1 where order_date between ?
and ? ::: [2020-01-20 00:00:00.0, 2021-12-22 00:00:10.0]
2021-12-23 11:07:13.751 INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL : Actual SQL: sharding_demo_2021 ::: select id,order_number orderNumber,order_date orderDate
from t_order_12 where order_date between ?
and ? ::: [2020-01-20 00:00:00.0, 2021-12-22 00:00:10.0]
三、操作公共表:在所有库中有一张公共表system_config,操作公共表时,需要操作所有数据库中的这张表。以第一个demo为例:
1、加上配置
#配置system_config表
spring.shardingsphere.sharding.tables.system_config.actual-data-nodes=db$->{1..3}.system_config
2、测试:
(1)新增:
<insert id="add" parameterType="com.demo.model.SystemConfigDTO">
insert into system_config(config_key,config_value) values (#{configKey},#{configValue})
</insert>
后台日志打印
2021-12-23 14:08:28.558 INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL : Rule Type: sharding
2021-12-23 14:08:28.559 INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL : Logic SQL: insert into system_config(config_key,config_value) values (?,?)
2021-12-23 14:08:28.559 INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=system_config, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=system_config, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=25)], parametersIndex=2, logicSQL=insert into system_config(config_key,config_value) values (?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[config_key, config_value], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3ef32e26, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@69e1d51])])
2021-12-23 14:08:28.560 INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL : Actual SQL: db1 ::: insert into system_config (config_key, config_value) VALUES (?, ?) ::: [aaa, aaa值]
2021-12-23 14:08:28.560 INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL : Actual SQL: db2 ::: insert into system_config (config_key, config_value) VALUES (?, ?) ::: [aaa, aaa值]
2021-12-23 14:08:28.560 INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL : Actual SQL: db3 ::: insert into system_config (config_key, config_value) VALUES (?, ?) ::: [aaa, aaa值]
查看三个表均新增成功: