浙江龙网

  1. 首页 > 生活百科 > >

shardingsphere-jdbc 水平分表学习记录

生活百科

放在自己博客里搬过来一份~
前司使用的是自己魔改的TDDL,在家时间比较多就尝试学一些业内比较常用的中间件.
这里记录一下学习中遇到的一些问题.
环境设置的比较简单(太懒了就测试了几个表), 两个分库, 各有几张分表.sharding-test_0

  • order_0 (order_id)
  • order_1
  • order_item_0 (order_id)
  • order_item_1
  • user_0 (user_id)
  • user_1
  • address (用来做broadcast表)
CREATE TABLE `order_0` (`order_id` int NOT NULL,`user_id` int NOT NULL,`address_id` int NOT NULL,PRIMARY KEY (`order_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `order_item_0` (`order_item_id` bigint NOT NULL,`order_id` int NOT NULL,PRIMARY KEY (`order_item_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `user_0` (`user_id` bigint NOT NULL,`user_name` varchar(45) NOT NULL,PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `address` (`address_id` int NOT NULL,`address_name` varchar(45) DEFAULT NULL,PRIMARY KEY (`address_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;spring-boot-starter使用2.7.5shardingsphere-jdbc-core-spring-boot-starter使用5.2.1
【shardingsphere-jdbc 水平分表学习记录】测试的时候最好直接跑,不要用单测,会被自动回滚掉.可以定义多个ApplicationRunner来测试.
@Componentpublic class MyApplicationRunner implements ApplicationRunner {@Autowiredprivate JdbcTemplate jdbcTemplate;@Overridepublic void run(final ApplicationArguments args) throws Exception {用JdbcTemplate方便点也省去了依赖更多的东西.返回自增key的代码样例:
KeyHolder keyHolder = new GeneratedKeyHolder();jdbcTemplate.update(connection -> {PreparedStatement ps = connection.prepareStatement("insert into user(`user_name`) values (?)",Statement.RETURN_GENERATED_KEYS);ps.setString(1, "cc2");return ps;}, keyHolder);System.out.println("key:" + keyHolder.getKey());配置本来使用yaml的配置,但看了一下有点太乱,先用properties的代替.配的时候有比较多的问题,几个配置错误会导致没法启动或者测试时报错,但配完之后感觉整体逻辑还是比较清晰的.
spring.shardingsphere.mode.type=Standalonespring.shardingsphere.props.sql-show=true# logic datasourcespring.shardingsphere.datasource.names=shard00,shard01# real datasourcespring.shardingsphere.datasource.shard00.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.shard00.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.shard00.jdbc-url=jdbc:mysql://localhost:3306/sharding_test_0spring.shardingsphere.datasource.shard00.username=rootspring.shardingsphere.datasource.shard00.password=spring.shardingsphere.datasource.shard01.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.shard01.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.shard01.jdbc-url=jdbc:mysql://localhost:3306/sharding_test_1spring.shardingsphere.datasource.shard01.username=rootspring.shardingsphere.datasource.shard01.password=spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=\shard0$->{0..1}.user_$->{0..1}spring.shardingsphere.rules.sharding.tables.order_item.actual-data-nodes=\shard0$->{0..1}.order_item_$->{0..1}spring.shardingsphere.rules.sharding.tables.order.actual-data-nodes=\shard0$->{0..1}.order_$->{0..1}spring.shardingsphere.rules.sharding.tables.address.actual-data-nodes=\shard0$->{0..1}.address# database strategy and table strategyspring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-column=user_idspring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-algorithm-name=alg_db_userspring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_user.type=MODspring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_user.props.sharding-count=2spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=user_idspring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=alg_table_userspring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_user.type=HASH_MODspring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_user.props.sharding-count=2spring.shardingsphere.rules.sharding.tables.order.database-strategy.standard.sharding-column=order_idspring.shardingsphere.rules.sharding.tables.order.database-strategy.standard.sharding-algorithm-name=alg_db_orderspring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_order.type=MODspring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_order.props.sharding-count=2spring.shardingsphere.rules.sharding.tables.order.table-strategy.standard.sharding-column=order_idspring.shardingsphere.rules.sharding.tables.order.table-strategy.standard.sharding-algorithm-name=alg_table_orderspring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_order.type=HASH_MODspring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_order.props.sharding-count=2# order_item and order use the same strategyspring.shardingsphere.rules.sharding.tables.order_item.database-strategy.standard.sharding-column=order_idspring.shardingsphere.rules.sharding.tables.order_item.database-strategy.standard.sharding-algorithm-name=alg_db_orderspring.shardingsphere.rules.sharding.tables.order_item.table-strategy.standard.sharding-column=order_idspring.shardingsphere.rules.sharding.tables.order_item.table-strategy.standard.sharding-algorithm-name=alg_table_order# key generatorspring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=user_idspring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=alg_snowflakespring.shardingsphere.rules.sharding.tables.order.key-generate-strategy.column=order_idspring.shardingsphere.rules.sharding.tables.order.key-generate-strategy.key-generator-name=alg_snowflakespring.shardingsphere.rules.sharding.tables.order_item.key-generate-strategy.column=order_item_idspring.shardingsphere.rules.sharding.tables.order_item.key-generate-strategy.key-generator-name=alg_snowflake# key generator algorithmspring.shardingsphere.rules.sharding.key-generators.alg_snowflake.type=SNOWFLAKEspring.shardingsphere.rules.sharding.key-generators.alg_uuid.type=UUID# binding table and broadcast tablespring.shardingsphere.rules.sharding.binding-tables[0]=order,order_itemspring.shardingsphere.rules.sharding.broadcast-tables=address

推荐阅读


上一篇：Scriptable Render Pipeline, SRP 可编程渲染管线

下一篇：2022，一个Java程序猿的装机配置