使用ShardingSphere5.5.1实现读写分离与相关异常问题处理
环境
SpringBoot版本：2.7.1
ShardingSphere版本：5.5.1
相关配置
- application-read-write-split-2.yml
server:port: 9092
spring:datasource:driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:read-write-split.yaml
- 编写read-write-split.yaml文件
mode:type: Standalonerepository:type: JDBC
dataSources:write_ds:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: org.postgresql.DriverjdbcUrl: username: password: read_ds_0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: org.postgresql.DriverjdbcUrl: username: password: read_ds_1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: org.postgresql.DriverjdbcUrl: username: password: rules:- !SINGLEtables:- "*.*.*"defaultDataSource: write_ds # 默认数据源，仅在执行 CREATE TABLE 创建单表时有效。缺失值为空，表示随机单播路由。- !READWRITE_SPLITTINGdataSourceGroups:readwrite_ds:writeDataSourceName: write_dsreadDataSourceNames:- read_ds_0- read_ds_1transactionalReadQueryStrategy: PRIMARYloadBalancerName: roundloadBalancers:round:type: ROUND_ROBIN
props:sql-show: true
注意
- 请求报错：nested exception is java.lang.NoSuchMethodError: org.yaml.snakeyaml.LoaderOptions.setCodePointLimit(I)V] with root cause
2025-05-10 14:12:43.690 ERROR 16804 --- [nio-9092-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Handler dispatch failed; nested exception is java.lang.NoSuchMethodError: org.yaml.snakeyaml.LoaderOptions.setCodePointLimit(I)V] with root causejava.lang.NoSuchMethodError: org.yaml.snakeyaml.LoaderOptions.setCodePointLimit(I)V
解决方式：
在SpringBoot中引入如下依赖：
<dependency><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId><version>1.33</version></dependency>
- 请求接口后，找不到相关表异常：TableNotFoundException: Table or view ‘xxxxxxxx’ does not exist.] with root cause
2025-05-10 14:16:35.458 ERROR 33572 --- [nio-9092-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 'xxxxxxxx' does not exist.] with root causeorg.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 'xxxxxxxx' does not exist.
解决方式：
在读写分离配置文件中一定加入单表配置，详见上述的read-write-split.yaml配置方式
- !SINGLEtables:- "*.*.*"defaultDataSource: write_ds # 默认数据源，仅在执行 CREATE TABLE 创建单表时有效。缺失值为空，表示随机单播路由。
验证结果
- 使用SpringBoot在application.yml中激活使用application-read-write-split-2.yml文件
- 效果截图
从截图上看，成功实现了读写分离