shardingsphere-jdbc-5.4.1，多数据源，跨库查询，单表与分片表关联提示表不存在，求解决方案
使用环境：
springboot：2.7.18
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.4.1</version>
</dependency>
场景、问题：
用到了多数据源，并且需要做跨库关联查询，并且是单表与分片表的关联，提示表不存在，sharding-dev.yaml配置如下
dataSources:
base:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://:3306/cloud_base?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
username: ***
password: ***
manage:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://:3306/cloud_course_manage?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
username: ***
password: ***
rules:
- !SHARDING
defaultDatabaseStrategy:
none: { }
defaultTableStrategy:
none: { }
autoTables:
t_system_point_log: # 逻辑表名称
actualDataSources: base
shardingStrategy:
standard:
shardingColumn: school_code
shardingAlgorithmName: auto_school_hash_mod
t_base_course_table_extra:
actualDataSources: base
shardingStrategy:
standard:
shardingColumn: school_code
shardingAlgorithmName: auto_school_hash_mod_2分片算法
shardingAlgorithms:
auto_school_hash_mod:
type: HASH_MOD
props:
sharding-count: 3
auto_school_hash_mod_2:
type: HASH_MOD
props:
sharding-count: 9 - !SINGLE
tables:- “manage.*”
- “base.*”
props:
sql-show: true
sql-federation-enabled: true
已进行操作：
查詢sql如下：其中t_base_course_table_extra 是分片表，其他都单表
select d.teacher_code as teacherCode,
b.code as courseCode,
ifnull(e.sign_status,0) as status,
e.sign_time as signTime,
e.sign_url as signUrl
from cloud_course_manage.t_cm_duty_setting_class a
join cloud_base.t_base_course_table_extra b on b.school_code = a.school_code and b.class_code = a.class_code and b.sort_code = a.sort_code
and b.date = unix_timestamp(#{date}) * 1000
join cloud_base.t_base_group_relation c on c.school_code = a.school_code and c.relation_code = b.relation_code
join cloud_base.t_base_course_group d on d.school_code = c.school_code and d.code = c.group_code
left join cloud_course_manage.t_cm_signed_teacher e
on e.school_code = d.school_code and e.table_code = b.code and e.teacher_code = d.teacher_code
where a.school_code = #{schoolCode}
order by b.id
现状：
提示表不存在
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: org.apache.shardingsphere.infra.exception.TableNotExistsException: Table or view t_cm_duty_setting_class does not exist.
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy125.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.$Proxy156.selectTrustTeacherSignedList(Unknown Source)
at com.chen.zhi.cloud.datashare.TestDemo.test(TestDemo.java:32)
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.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: org.apache.shardingsphere.infra.exception.TableNotExistsException: Table or view t_cm_duty_setting_class does not exist.
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:97)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
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)
… 40 more
Caused by: org.apache.shardingsphere.infra.exception.TableNotExistsException: Table or view t_cm_duty_setting_class does not exist.
at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.lambda$checkTableExists$4(SimpleTableSegmentBinder.java:146)
at org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41)
at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.checkTableExists(SimpleTableSegmentBinder.java:143)
at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.bind(SimpleTableSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:54)
at org.apache.shardingsphere.infra.binder.segment.from.impl.JoinTableSegmentBinder.bind(JoinTableSegmentBinder.java:70)
at org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:57)
at org.apache.shardingsphere.infra.binder.segment.from.impl.JoinTableSegmentBinder.bind(JoinTableSegmentBinder.java:70)
at org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:57)
at org.apache.shardingsphere.infra.binder.segment.from.impl.JoinTableSegmentBinder.bind(JoinTableSegmentBinder.java:70)
at org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:57)
at org.apache.shardingsphere.infra.binder.segment.from.impl.JoinTableSegmentBinder.bind(JoinTableSegmentBinder.java:70)
at org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:57)
at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:56)
at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:46)
at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindDMLStatement(SQLBindEngine.java:100)
at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:80)
at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:71)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:202)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:167)
at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:83)
at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
… 54 more