mybatis plus + mybatis + shardingsphere + 分库分表

9 篇文章 0 订阅
4 篇文章 0 订阅

ID自增和mybatis相关

参考: https://blog.csdn.net/u010379996/article/details/113859626

shardingsphere4配置

官网源码:https://github.com/apache/shardingsphere/

文档:https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/

sql

CREATE TABLE `t_test_1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `type` tinyint(3) DEFAULT '0',
  `status` tinyint(3) DEFAULT '0',
  `update_time` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

pom.xml

<shardingsphere.version>4.1.0</shardingsphere.version>

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${shardingsphere.version}</version>
 </dependency>

配置文件(application-sharding-databases.properties)

## test默认数据源 ds-0,ds-1分片数据源
spring.shardingsphere.datasource.names=test,ds-0,ds-1

spring.shardingsphere.datasource.test.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.test.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.test.jdbc-url=jdbc:mysql://ip:3306/test
spring.shardingsphere.datasource.test.username=root
spring.shardingsphere.datasource.test.password=

spring.shardingsphere.datasource.ds-0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-0.jdbc-url=jdbc:mysql://ip:3306/demo_ds_0
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=

spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:mysql://ip:3306/demo_ds_1
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=

spring.shardingsphere.sharding.default-data-source-name=test

## 分库分表
spring.shardingsphere.sharding.tables.t_test.database-strategy.inline.sharding-column=type
spring.shardingsphere.sharding.tables.t_test.database-strategy.inline.algorithm-expression=ds-$->{type % 2}
spring.shardingsphere.sharding.tables.t_test.table-strategy.inline.sharding-column=status
spring.shardingsphere.sharding.tables.t_test.table-strategy.inline.algorithm-expression=t_test_$->{status % 2}
spring.shardingsphere.sharding.binding-tables=t_test

spring.shardingsphere.sharding.tables.t_test.actual-data-nodes=ds-$->{0..1}.t_test_${0..1}
spring.shardingsphere.sharding.tables.t_test.key-generator.column=id
spring.shardingsphere.sharding.tables.t_test.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_test.key-generator.props.worker.id=123

## 打印SQL
spring.shardingsphere.props.sql.show=true

t_test

@Data
@TableName("t_test")
public class TTestModel implements Serializable {

    private static final long serialVersionUID = 1L;

    /** id  */
    @TableId(type = IdType.NONE)
    @SequenceId(interval = 10)
    private Integer id;

    private String name;

    private Integer type;

    private Integer status;

    private Date updateTime;

    private Date createTime;

}

@RestController
@RefreshScope
public class TestController {
	
	@Autowired
	private ITTestService ttestService;
	@GetMapping("/ttest/add")
    public Object add() {
		TTestModel model = new TTestModel();
		model.setName("name");
		model.setType(new Random().nextInt(10));
		model.setStatus(new Random().nextInt(10));
		model.setUpdateTime(new Date());
    	model.setCreateTime(new Date());
    	return ResultModel.ok(ttestService.save(model));
    }
	
	@GetMapping("/ttest/list")
    public Object list() {
		Map<String, Object> map = Maps.newHashMap();
    	return ResultModel.ok(ttestService.findListByMap(map));
    }
}

验证

2021-03-02 09:49:16.884  INFO 33456 --- [nio-9696-exec-2] c.p.config.interceptor.LogInterceptor    : /ttest/add
2021-03-02 09:49:17.008 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.selectOne   : ==>  Preparing: SELECT id,name,sequence_id,update_time,create_time FROM test_sequence_id WHERE name=?
2021-03-02 09:49:17.028 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.selectOne   : ==> Parameters: t_test(String)
2021-03-02 09:49:17.605  INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL                       : Logic SQL: SELECT  id,name,sequence_id,update_time,create_time  FROM test_sequence_id 
 
 WHERE  name=?
2021-03-02 09:49:17.605  INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@20841be6, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@9b075c9), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@9b075c9, projectionsContext=ProjectionsContext(startIndex=8, stopIndex=50, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=sequence_id, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@477f53a0, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4730ab53, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@476bc378, containsSubquery=false)
2021-03-02 09:49:17.605  INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL                       : Actual SQL: test ::: SELECT  id,name,sequence_id,update_time,create_time  FROM test_sequence_id 
 
 WHERE  name=? ::: [t_test]
2021-03-02 09:49:17.654 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.selectOne   : <==      Total: 1
2021-03-02 09:49:17.659 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.updateById  : ==>  Preparing: UPDATE test_sequence_id SET name=?, sequence_id=?, update_time=?, create_time=? WHERE id=?
2021-03-02 09:49:17.660 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.updateById  : ==> Parameters: t_test(String), 61(Long), 2021-03-02 09:49:17.658(Timestamp), 2021-02-26 22:00:14.0(Timestamp), 2(Integer)
2021-03-02 09:49:17.667  INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL                       : Logic SQL: UPDATE test_sequence_id  SET name=?,
sequence_id=?,
update_time=?,
create_time=?  WHERE id=?
2021-03-02 09:49:17.667  INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL                       : SQLStatement: UpdateStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.UpdateStatement@76307d1, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7115f9f6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7115f9f6)
2021-03-02 09:49:17.667  INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL                       : Actual SQL: test ::: UPDATE test_sequence_id  SET name=?,
sequence_id=?,
update_time=?,
create_time=?  WHERE id=? ::: [t_test, 61, 2021-03-02 09:49:17.658, 2021-02-26 22:00:14.0, 2]
2021-03-02 09:49:17.673 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.updateById  : <==    Updates: 1
2021-03-02 09:49:17.679 DEBUG 33456 --- [nio-9696-exec-2] com.project.dao.TTestMapper.insert       : ==>  Preparing: INSERT INTO t_test ( id, name, type, status, update_time, create_time ) VALUES ( ?, ?, ?, ?, ?, ? )
2021-03-02 09:49:17.679 DEBUG 33456 --- [nio-9696-exec-2] com.project.dao.TTestMapper.insert       : ==> Parameters: 51(Integer), name(String), 9(Integer), 0(Integer), 2021-03-02 09:49:16.896(Timestamp), 2021-03-02 09:49:16.896(Timestamp)
2021-03-02 09:49:17.771  INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_test  ( id,
name,
type,
status,
update_time,
create_time )  VALUES  ( ?,
?,
?,
?,
?,
? )
2021-03-02 09:49:17.771  INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@2955334f, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7403cdfa), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7403cdfa, columnNames=[id, name, type, status, update_time, create_time], insertValueContexts=[InsertValueContext(parametersCount=6, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=84, stopIndex=84, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=87, stopIndex=87, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=90, stopIndex=90, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=93, stopIndex=93, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=96, stopIndex=96, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=99, stopIndex=99, parameterMarkerIndex=5)], parameters=[51, name, 9, 0, 2021-03-02 09:49:16.896, 2021-03-02 09:49:16.896])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=id, generated=false, generatedValues=[51])])
2021-03-02 09:49:17.772  INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: INSERT INTO t_test_0  ( id,
name,
type,
status,
update_time,
create_time )  VALUES  (?, ?, ?, ?, ?, ?) ::: [51, name, 9, 0, 2021-03-02 09:49:16.896, 2021-03-02 09:49:16.896]
2021-03-02 09:49:17.787 DEBUG 33456 --- [nio-9696-exec-2] com.project.dao.TTestMapper.insert       : <==    Updates: 1
2021-03-02 09:49:21.059  INFO 33456 --- [nio-9696-exec-3] c.p.config.interceptor.LogInterceptor    : /ttest/list
2021-03-02 09:49:21.064 DEBUG 33456 --- [nio-9696-exec-3] com.project.dao.TTestMapper.findByMap    : ==>  Preparing: select * from t_test t where 1=1
2021-03-02 09:49:21.064 DEBUG 33456 --- [nio-9696-exec-3] com.project.dao.TTestMapper.findByMap    : ==> Parameters: 
2021-03-02 09:49:21.076  INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL                       : Logic SQL: select * from t_test t
		 
		where 1=1
2021-03-02 09:49:21.076  INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5dd51fbb, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7bd6cbce), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7bd6cbce, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=type, alias=Optional.empty), ColumnProjection(owner=null, name=status, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@43ad9bef, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4d6a1f92, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@3837f943, containsSubquery=false)
2021-03-02 09:49:21.076  INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL                       : Actual SQL: ds-0 ::: select * from t_test_0 t
		 
		where 1=1
2021-03-02 09:49:21.076  INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL                       : Actual SQL: ds-0 ::: select * from t_test_1 t
		 
		where 1=1
2021-03-02 09:49:21.076  INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: select * from t_test_0 t
		 
		where 1=1
2021-03-02 09:49:21.076  INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: select * from t_test_1 t
		 
		where 1=1
2021-03-02 09:49:21.124 DEBUG 33456 --- [nio-9696-exec-3] com.project.dao.TTestMapper.findByMap    : <==      Total: 13

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值