sharding-jdbc+mybatis-plus 快速实现分
2021-01-13 本文已影响0人
东方不喵
在开发过程中经常会遇到数据量过大，再除了缓存之外，可以对数据库进行分库分表。本文主要描述快速实现基于 sharding-jdbc 进行分库分表配置。以及在配置过程中踩到的一些坑。
版本：
springboot: 2.4.1
mybatis-plus-boot-starter: 2.2.0
sharding-jdbc-spring-boot-starter: 4.1.1
druid: 1.2.4官网：https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/
Demo gitee: https://gitee.com/old_guys/sharding-test-demo
目录：
- 引入依赖
- 基本配置及描述（多数据源，分库分表）
- JOIN 关联查询
采坑：
- 引入 druid-spring-boot-starter （使用这个会报错，直接使用druid依赖就不会）
- 如果有多个数据源，但是不配置（配置失效）的时候，就会随机获取一个数据源进行处理。
- (也不算坑，官方文档没有直接描述明显) sharding-jdbc 只会根据 from 后的第一张表进行分库分表策略，其他表都会按照第一张表的规则来，其他关联表配置不会有效。
- 不支持部分数据库函数 ，会导致分库分表失效，最后按照 随机获取数据源那种方式来进行。另外 postgres不支持 case when语句 ，会导致分库分表失败。
另外：
- 由于mybatis一个容器默认只有一个数据库类型，所以是不能直接支持 几种不同的数据库同时使用的。
Caused by: java.lang.IllegalStateException: Database type inconsistent
with 'org.apache.shardingsphere.underlying.common.database.type.dialect.PostgreSQLDatabaseType@44cffc25'
and 'org.apache.shardingsphere.underlying.common.database.type.dialect.MySQLDatabaseType@6e041285'
at com.google.common.base.Preconditions.checkState(Preconditions.java:173)
1. 引入依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.4</version>
</dependency>
</dependencies>
第一个坑 !: 引入 druid-spring-boot-starter （使用这个会报错，直接使用druid依赖就不会）
2. 基本配置
spring.shardingsphere.datasource.names=ds-0,ds-1,ds-2
#打开sql显示
spring.shardingsphere.props.sql.show=true
spring.shardingsphere.datasource.ds-0.url=jdbc:mysql://127.0.0.1:3306/ds-0?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&serverTimezone=GMT
#spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=root
## mybatis不支持默认多种数据库相互切换
#spring.shardingsphere.datasource.ds-0.url=jdbc:postgresql://localhost:5432/test_db
##spring.shardingsphere.datasource.ds-0.type=com.zaxxer.hikari.HikariDataSource
#spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.ds-0.driver-class-name=org.postgresql.Driver
#spring.shardingsphere.datasource.ds-0.username=postgres
#spring.shardingsphere.datasource.ds-0.password=root
spring.shardingsphere.datasource.ds-1.url=jdbc:mysql://127.0.0.1:3306/ds-1?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&serverTimezone=GMT
#spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=root
spring.shardingsphere.datasource.ds-2.url=jdbc:mysql://127.0.0.1:3306/ds-2?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&serverTimezone=GMT
#spring.shardingsphere.datasource.ds-2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2.username=root
spring.shardingsphere.datasource.ds-2.password=root
##自定义参数
my-sharding-table.config=0,1,2,3
## demo2_test_user 如果有多个数据源，但是不配置（配置失效）的时候，就会随机获取一个数据源进行处理。
spring.shardingsphere.sharding.tables.demo2_test_user.actual-data-nodes=ds-0.test_user
##test_user
spring.shardingsphere.sharding.tables.test_user.actual-data-nodes=ds-$->{1..2}.test_user_$->{0..3}
spring.shardingsphere.sharding.tables.test_user.database-strategy.inline.sharding-column=scope
spring.shardingsphere.sharding.tables.test_user.database-strategy.inline.algorithm-expression=ds-$->{scope%2+1}
spring.shardingsphere.sharding.tables.test_user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.test_user.table-strategy.inline.algorithm-expression=test_user_$->{id%4}
##course
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds-$->{[1,2]}.course_$->{[${my-sharding-table.config}]}
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=scope
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=ds-$->{scope%2+1}
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id%4}
##course_selected
spring.shardingsphere.sharding.tables.course_selected.actual-data-nodes=ds-$->{1..2}.course_selected_$->{0..3}
spring.shardingsphere.sharding.tables.course_selected.database-strategy.inline.sharding-column=scope
spring.shardingsphere.sharding.tables.course_selected.database-strategy.inline.algorithm-expression=ds-$->{scope%2+1}
spring.shardingsphere.sharding.tables.course_selected.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course_selected.table-strategy.inline.algorithm-expression=course_selected_$->{user_id%4}
spring.shardingsphere.sharding.binding-tables[0]=test_user,course_selected,course
基本配置属性：
| 属性 | 意义 | |
|---|---|---|
| 1 | spring.shardingsphere.datasource.names=ds-0,ds-1,ds-2 | 启用数据源：ds-0，ds-1，ds-2 |
| 2 | spring.shardingsphere.props.sql.show=true | 打印sharding-jdbc的SQL转换 |
| 3 | spring.shardingsphere.datasource.ds-0.url=xxx spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds-0.username=root spring.shardingsphere.datasource.ds-0.password=root |
数据源配置，ds-0,ds-1,ds-2 分别进行配置。 mysql-driver：8.0 使用 com.mysql.cj.jdbc.Driver mysql-driver：5.+ 使用 com.mysql.jdbc.Driver |
| 4 | spring.shardingsphere.sharding.tables.table_xxx.actual-data-nodes=ds-0.test_user | 配置表映射实际表 ds-0（上面配置数据源）.test_user（实际表名） |
如果只是单纯想做多数据源配置 ，上面的配置就已经够了。按照类似结构进行配置，就可以进行多数据源配置。
其中如果出现不同库表名相同，可以通过直接改表映射名// 数据库 db_1 db_2 都有表 test_user // 写SQL 的 from表名 为 db1_test_user spring.shardingsphere.sharding.tables.db1_test_user.actual-data-nodes=ds-1.test_user // 写SQL 的 from表名 为 db2_test_user spring.shardingsphere.sharding.tables.db2_test_user.actual-data-nodes=ds-2.test_user这样实际生成的 SQL 就是 SELECT * FROM test_user 并且会去指定的数据源进行查询
进行分库分表
| 属性 | 意义 | |
|---|---|---|
| 5 | spring.shardingsphere.sharding.tables.test_user.actual-data-nodes=ds-$->{1..100}.test_user_$->{0..3} |
ds-$->{1..100}从1到2的所有数字拼接成数据源，如：ds-1，ds-2，ds-3 |
| 6 |
my-sharding-table.config=0,1,2,3spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds-$->{[1,3]}.course_$->{[${my-sharding-table.config}]}
|
ds-$->{[1,3]}：数组，如：ds-1，ds-3$->{[${my-sharding-table.config}]}:从el表达式中获取值
|
| 7 |
spring.shardingsphere.sharding.tables.test_user.database-strategy.inline.sharding-column=scope spring.shardingsphere.sharding.tables.test_user.database-strategy.inline.algorithm-expression=ds-$->{scope%2+1}
|
分库策略：根据指定的字段，及分库策略表达式$->{scope%2+1} 对scope的值进行取mod 结果再 +1
|
| 8 |
spring.shardingsphere.sharding.tables.test_user.table-strategy.inline.sharding-column=id spring.shardingsphere.sharding.tables.test_user.table-strategy.inline.algorithm-expression=test_user_$->{id%4}
|
分表策略：根据指定的字段，及分表策略表达式$->{id%4} 对id的值进行取mod 结果再 +1
|
| 9 | spring.shardingsphere.sharding.binding-tables[0]=test_user,course_selected,course | 将表进行关联，join查询必须有关联才可以实现分库分表，不然会报找不到表或者没有指定的分库分表策略的错误 |
扩展配置
# 配置 主键生成
spring.shardingsphere.sharding.tables.course.key-generator.column=id
#指定course表里面主键id生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
因为项目使用的是mybatis-plus，已经默认了雪花ID，所以这里不配也行
此处采坑 2 ! 如果有多个数据源，但是不配置（配置失效）的时候，就会随机获取一个数据源进行处理。
## 如果有多个数据源，但是不配置（配置失效）的时候，就会随机获取一个数据源进行处理。 spring.shardingsphere.sharding.tables.table_xxx.actual-data-nodes=ds-0.test_user
日志如下：
2021-01-12 11:44:43.326 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.327 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a1371), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a1371, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838288484306946, name-0, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.327 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838288484306946, name-0, 1]
2021-01-12 11:44:43.376 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.376 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@655203e3), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@655203e3, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290745036802, name-1, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.376 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-0 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838290745036802, name-1, 1]
2021-01-12 11:44:43.381 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.382 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@568f4faa), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@568f4faa, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290770202626, name-2, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.382 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-0 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838290770202626, name-2, 1]
2021-01-12 11:44:43.385 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.385 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@43588265), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@43588265, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290786979842, name-3, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.385 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-0 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838290786979842, name-3, 1]
2021-01-12 11:44:43.387 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.387 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2774dcf4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2774dcf4, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290795368449, name-4, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.387 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838290795368449, name-4, 1]
2021-01-12 11:44:43.392 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.392 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@61ab6521), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@61ab6521, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290820534273, name-5, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.393 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838290820534273, name-5, 1]
2021-01-12 11:44:43.396 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.396 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@52c46334), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@52c46334, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290828922881, name-6, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.396 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-0 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838290828922881, name-6, 1]
2021-01-12 11:44:43.399 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.399 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2b458cd6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2b458cd6, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290845700098, name-7, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.400 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-0 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838290845700098, name-7, 1]
2021-01-12 11:44:43.402 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.402 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@227a933d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@227a933d, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290862477314, name-8, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.402 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838290862477314, name-8, 1]
2021-01-12 11:44:43.405 INFO 31140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
( ?,
?,
? )
2021-01-12 11:44:43.405 INFO 31140 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@dd737ea), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@dd737ea, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290870865921, name-9, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.406 INFO 31140 --- [ main] ShardingSphere-SQL : Actual SQL: ds-0 ::: INSERT INTO test_user
( id,
`name`,
`scope` ) VALUES
(?, ?, ?) ::: [1348838290870865921, name-9, 1]
JOIN 关联查询
## 进行表关联
spring.shardingsphere.sharding.binding-tables[0]=test_user,course_selected,course
不配置：分片字段会无效，表进行笛卡尔集，
: Logic SQL: SELECT * FROM course a,course_selected b WHERE a.id = b.course_id
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@3ef2b8e5, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@49190ed6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@49190ed6, 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=scope, alias=Optional.empty), ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=scope, alias=Optional.empty), ColumnProjection(owner=null, name=user_id, alias=Optional.empty), ColumnProjection(owner=null, name=course_id, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@5d717f19, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@18715bb, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@2a19a0fe, containsSubquery=false)
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743 INFO 30632 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_3 b WHERE a.id = b.course_id
配置：分片字段生效，进行分库分表查
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT * FROM course a,course_selected b WHERE a.id = b.course_id
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@2321e482, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@467ef400), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@467ef400, 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=scope, alias=Optional.empty), ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=scope, alias=Optional.empty), ColumnProjection(owner=null, name=user_id, alias=Optional.empty), ColumnProjection(owner=null, name=course_id, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@13fe5bb7, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4276ad40, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@6e5f5478, containsSubquery=false)
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756 INFO 19256 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_3 b WHERE a.id = b.course_id
采坑3，sharding-jdbc 只会根据 from 后的第一张表进行分库分表策略，其他表都会按照第一张表的规则来，所以其他关联表，就算不配置分库分表的策略，依然有效。配置如下，效果一致。
##course
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds-$->{[1,2]}.course_$->{[${my-sharding-table.config}]}
##course_selected
spring.shardingsphere.sharding.tables.course_selected.actual-data-nodes=ds-$->{1..2}.course_selected_$->{0..3}
spring.shardingsphere.sharding.tables.course_selected.database-strategy.inline.sharding-column=scope
spring.shardingsphere.sharding.tables.course_selected.database-strategy.inline.algorithm-expression=ds-$->{scope%2+1}
spring.shardingsphere.sharding.tables.course_selected.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course_selected.table-strategy.inline.algorithm-expression=course_selected_$->{user_id%4}
spring.shardingsphere.sharding.binding-tables[0]=test_user,course_selected,course
采坑4 ！ 不支持部分数据库函数 ，会导致分库分表失效，最后按照 随机获取数据源那种方式来进行。另外postgres不支持 case when语句 ，会导致分库分表失败，但是MYSQL不会
MySQL 使用 case when 正常进行分库分表
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user a JOIN course_selected b ON a.id = b.user_id
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5c4714ef, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@c94bd18), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@c94bd18, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=76, distinctRow=false, projections=[ColumnProjection(owner=a, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=b, name=course_id, alias=Optional.empty), ExpressionProjection(expression=CASEa.scopeWHEN0THEN100ELSE200END, alias=Optional[scope])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@71fb8301, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@7cdfa824, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@18db3b3c, containsSubquery=false)
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_0 a JOIN course_selected_0 b ON a.id = b.user_id
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_1 a JOIN course_selected_1 b ON a.id = b.user_id
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_2 a JOIN course_selected_2 b ON a.id = b.user_id
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : Actual SQL: ds-1 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_3 a JOIN course_selected_3 b ON a.id = b.user_id
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_0 a JOIN course_selected_0 b ON a.id = b.user_id
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_1 a JOIN course_selected_1 b ON a.id = b.user_id
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_2 a JOIN course_selected_2 b ON a.id = b.user_id
2021-01-14 14:08:33.100 INFO 8648 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_3 a JOIN course_selected_3 b ON a.id = b.user_id
Postgres 使用 case when ，无法使用分库表策略，报找不到表错误
Logic SQL: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user a JOIN course_selected b ON a.id = b.user_id
2021-01-14 14:06:15.031 INFO 3808 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@57c6feea, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3b57f915), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3b57f915, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=70, distinctRow=false, projections=[ColumnProjection(owner=a, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=b, name=course_id, alias=Optional.empty), ExpressionProjection(expression=CASEa.scopeWHEN0THEN100ELSE200END, alias=Optional[END])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@39c7fb0b, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@645dc557, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@48c5698, containsSubquery=false)
2021-01-14 14:06:15.031 INFO 3808 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user a JOIN course_selected b ON a.id = b.user_id
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: org.postgresql.util.PSQLException: 错误: 关系 "test_user" 不存在
位置：84
### The error may exist in com/example/sharding/modules/demo1/dao/jpas/TestUserMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user a JOIN course_selected b ON a.id = b.user_id
### Cause: org.postgresql.util.PSQLException: 错误: 关系 "test_user" 不存在
位置：84
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 错误: 关系 "test_user" 不存在
位置：84
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy66.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy85.findCaseWhen(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
at com.sun.proxy.$Proxy86.findCaseWhen(Unknown Source)
at com.example.sharding.dao.DefaultShardingTests.testCaseWhen(DefaultShardingTests.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:688)
at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:210)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:206)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:131)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:65)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
at java.util.ArrayList.forEach(ArrayList.java:1257)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
at java.util.ArrayList.forEach(ArrayList.java:1257)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:108)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:96)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:75)
at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:74)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.postgresql.util.PSQLException: 错误: 关系 "test_user" 不存在
位置：84
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:62)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:58)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.syncExecute(ExecutorEngine.java:97)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.parallelExecute(ExecutorEngine.java:93)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.execute(ExecutorEngine.java:76)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:68)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:51)
at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:129)
at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:148)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:145)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy100.query(Unknown Source)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
... 83 more
