ShardingSphere-JDBC分库分表实操 - 快速上手
实操中常见错误
Sharding JDBC 错误 : Inline sharding algorithm expression cannot be null.
Sharding JDBC 错误 : Could not resolve placeholder ‘user_id % 2‘ in value “ds_${user_id % 2}“
Sharding JDBC 错误 : Parameter index out of range (1 > number of parameters, which is 0).
ShardingSphere-JDBC是什么?
ShardingSphere-JDBC通常用于数据分片。定位为轻量级 Java 框架,在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库,以 jar 包形式提供服务,无需额外部署和依赖,可理解为增强版的 JDBC 驱动,完全兼容 JDBC 和各种 ORM 框架。
- 适用于任何基于 JDBC 的 ORM 框架,如:JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
- 支持任何第三方的数据库连接池,如:DBCP, C3P0, BoneCP, Druid, HikariCP 等。
- 支持任意实现 JDBC 规范的数据库,目前支持 MySQL,Oracle,SQLServer,PostgreSQL 以及任何遵循 SQL92 标准的数据库。
ShardingSphere-JDBC实现分库分表
该案例以用户订单分库分表为例。
-
创建一个Spring Boot项目
-
引入shardingsphere-jdbc-core-spring-boot-starter
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId> <version>5.0.0</version> </dependency>
-
配置分片规则
spring: shardingsphere: datasource: ds0: driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/ds0 password: 123456 type: com.zaxxer.hikari.HikariDataSource username: root ds1: driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/ds1 password: 123456 type: com.zaxxer.hikari.HikariDataSource username: root names: ds0,ds1 rules: sharding: sharding-algorithms: database-inline: props: algorithm-expression: ds$->{user_id % 2} type: INLINE table-inline: props: algorithm-expression: tb_order$->{order_id.toString().charAt(order_id.toString().length() - 1)} type: INLINE tables: tb_order: actual-data-nodes: ds$->{0..1}.tb_order$->{0..9} database-strategy: standard: sharding-algorithm-name: database-inline sharding-column: user_id table-strategy: standard: sharding-algorithm-name: table-inline sharding-column: order_id
-
准备数据库、表
4.1 创建ds0、ds1数据库
4.2 分别在ds0、ds1中执行以下语句创建订单表表
CREATE TABLE `tb_order0` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_order1` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_order2` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_order3` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_order4` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_order5` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_order6` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_order7` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_order8` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_order9` ( `id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `order_id` bigint(20) NOT NULL, `product_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-
创建Controller
/** * @author Fred-X * @date 2021/12/31 15:35 */ @Controller @RequestMapping("/test") public class TestController { @Autowired private TestService testService; @PostMapping("/testGet") public void testGet() { testService.testGet(); } @PostMapping("/testInsert") public void testInsert() { testService.testInsert(); } @PostMapping("/testDelete") public void testDelete() { testService.testDelete(); } @PostMapping("/testUpdate") public void testUpdate() { testService.testUpdate(); } @PostMapping("/testPage") public void testPage() { testService.testPage(); } }
-
Service接口
/** * @author Fred-X * @date 2021/12/31 15:35 */ public interface TestService { void testInsert(); void testGet(); void testDelete(); void testUpdate(); void testPage(); }
-
Service接口实现类
/** * @author Fred-X * @date 2021/12/31 15:37 */ @Service public class TestServiceImpl implements TestService { @Autowired private DataSource dataSource; @Override public void testInsert() { while (true) { Connection connection = null; try { Thread.sleep(100); connection = dataSource.getConnection(); long l = RandomUtils.nextLong(); String sql = "insert into tb_order(id,user_id,order_id) value (?,?,?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setLong(1, l); preparedStatement.setInt(2, 4); preparedStatement.setInt(3, 10001); preparedStatement.execute(); connection.close(); } catch (Exception exception) { exception.printStackTrace(); } } } @Override public void testGet() { Connection connection = null; try { connection = dataSource.getConnection(); String sql = "select * from tb_order where id = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setLong(1, 9123951672448291086L); ResultSet resultSet = preparedStatement.executeQuery(); int counter = 0; while (resultSet.next()) { counter++; System.out.println(resultSet.getLong("id")); } System.out.println("counter:" + counter); resultSet.close(); connection.close(); } catch (Exception exception) { exception.printStackTrace(); } } @Override public void testDelete() { Connection connection = null; try { connection = dataSource.getConnection(); String sql = "delete from tb_order where id = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setLong(1, 9123951672448291086L); System.out.println(preparedStatement.executeUpdate()); connection.close(); } catch (Exception exception) { exception.printStackTrace(); } } @Override public void testUpdate() { Connection connection = null; try { connection = dataSource.getConnection(); String sql = "update tb_order set product_name = ? where id = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "小米手机"); preparedStatement.setLong(2, 9123951672448291086L); System.out.println(preparedStatement.executeUpdate()); connection.close(); } catch (Exception exception) { exception.printStackTrace(); } } @Override public void testPage() { Connection connection = null; try { connection = dataSource.getConnection(); String sql = "select * from tb_order limit 10,10"; PreparedStatement preparedStatement = connection.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery(); int counter = 0; while (resultSet.next()) { counter++; System.out.println(resultSet.getLong("id")); } System.out.println("counter:" + counter); resultSet.close(); connection.close(); } catch (Exception exception) { exception.printStackTrace(); } } }
自此就已经实现分库分表,代码看上去好像和我们平时写jdbcCURD没什么区别,确实没什么区别。但是在应用运行时就有区别了。
@Autowired private DataSource dataSource;
关键就在于DataSource的注入,这里和以往不一样,这里注入的是ShardingSphereDataSource,也就是说所有逻辑不再是以前普通的CURD,而是执行的ShardingSphereDataSource的逻辑,ShardingSphereDataSource内部会根据配置的规则做数据分片处理。