最近项目因为业务问题，一张表内数据达到1800W+的数据量，每年新增500W+的数量。现在客户要求对数据中的表进行可视化界面，单表查询（表无做任何优化措施，如加索引等）根据日期区间需要5-10秒，因为每年新增数据量过大，只能对这张表进行单库分表（现在还遇到个难题sharding-jdbc每年自动建表问题），使用sharding-jdbc中间解决查询新增的问题。
项目现使用技术：springboot 2.3.4.RELEASE
mybatis 2.1.3
druid 1.1.22
数据库：oracle 11g
postgres
整合中间件：sharding-jdbc 4.1.1
pom依赖
<!--节选主要依赖包-->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.22</version>
</dependency>
<!--sharding的核心包，不使用sharding-jdbc-spring-boot-starter,springboot集成的包，是因为我使用的两种类型的数据库oracle和postgres数据库，springboot整合的好像只能使用一种类型的数据库-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
</dependencies>
properties配置文件
# 定义应用端口
server.port=8083
#设置上传文件目录
system.uploadPath=D:/uploadFile/
# 数据源配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 主库数据源
spring.datasource.druid.master.db-type=oracle
spring.datasource.druid.master.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.druid.master.url=jdbc:oracle:thin:@localhost:1521/orcl
spring.datasource.druid.master.username=orcl
spring.datasource.druid.master.password=123456
spring.datasource.druid.filter.config.enabled=true
# 初始连接数
spring.datasource.druid.master.initialSize=5
# 最小连接池数量
spring.datasource.druid.master.minIdle=10
# 最大连接池数量
spring.datasource.druid.master.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.druid.master.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.master.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.master.minEvictableIdleTimeMillis=300000
# 配置一个连接在池中最大生存的时间，单位是毫秒
spring.datasource.druid.master.maxEvictableIdleTimeMillis=900000
# 配置检测连接是否有效
spring.datasource.druid.master.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.master.testWhileIdle=true
spring.datasource.druid.master.testOnBorrow=false
spring.datasource.druid.master.testOnReturn=false
spring.datasource.druid.master.web-stat-filter.enabled=true
spring.datasource.druid.master.stat-view-servlet.enabled=true
# 设置白名单，不填则允许所有访问
spring.datasource.druid.stat-view-servlet.allow=
spring.datasource.druid.stat-view-servlet.url-pattern=/monitor/druid/*
spring.datasource.druid.filter.stat.enabled=true
# 慢SQL记录
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=1000
spring.datasource.druid.filter.stat.merge-sql=true
spring.datasource.druid.filter.wall.config.multi-statement-allow=true
#数据库过滤器
#打印sql
spring.datasource.druid.filter.commons-log.statement-log-enabled=true
spring.datasource.druid.filter.commons-log.statement-executable-sql-log-enable=true
#数据源2
spring.datasource.druid.slave.db-type=oracle
spring.datasource.druid.slave.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.druid.slave.url=jdbc:oracle:thin:@localhost:1521/orcl
spring.datasource.druid.slave.username=orcl
spring.datasource.druid.slave.password=123456
# 初始连接数
spring.datasource.druid.slave.initialSize=5
# 最小连接池数量
spring.datasource.druid.slave.minIdle=10
# 最大连接池数量
spring.datasource.druid.slave.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.druid.slave.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.slave.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.slave.minEvictableIdleTimeMillis=300000
# 配置一个连接在池中最大生存的时间，单位是毫秒
spring.datasource.druid.slave.maxEvictableIdleTimeMillis=900000
# 配置检测连接是否有效
spring.datasource.druid.slave.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.slave.testWhileIdle=true
spring.datasource.druid.slave.testOnBorrow=false
spring.datasource.druid.slave.testOnReturn=false
spring.datasource.druid.slave.web-stat-filter.enabled=true
spring.datasource.druid.slave.stat-view-servlet.enabled=true
#数据源3
spring.datasource.druid.postgres.db-type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.postgres.driver-class-name=org.postgresql.Driver
#postgreSql数据库
spring.datasource.druid.postgres.url=jdbc:postgresql://localhost:5432/csdzdb
spring.datasource.druid.postgres.username=postgres
spring.datasource.druid.postgres.password=Postgres#123
# 初始连接数
spring.datasource.druid.postgres.initialSize=5
# 最小连接池数量
spring.datasource.druid.postgres.minIdle=10
# 最大连接池数量
spring.datasource.druid.postgres.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.druid.postgres.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.postgres.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.postgres.minEvictableIdleTimeMillis=300000
# 配置一个连接在池中最大生存的时间，单位是毫秒
spring.datasource.druid.postgres.maxEvictableIdleTimeMillis=900000
#数据源4
spring.datasource.druid.impl.db-type=oracle
spring.datasource.druid.impl.driver-class-name=oracle.jdbc.driver.OracleDriver
#内网雨量数据库
spring.datasource.druid.impl.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.druid.impl.username=orcl
spring.datasource.druid.impl.password=12345
# 初始连接数
spring.datasource.druid.impl.initialSize=5
# 最小连接池数量
spring.datasource.druid.impl.minIdle=10
# 最大连接池数量
spring.datasource.druid.impl.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.druid.impl.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.impl.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.impl.minEvictableIdleTimeMillis=300000
# 配置一个连接在池中最大生存的时间，单位是毫秒
spring.datasource.druid.impl.maxEvictableIdleTimeMillis=900000
# 配置检测连接是否有效
spring.datasource.druid.impl.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.impl.testWhileIdle=true
spring.datasource.druid.impl.testOnBorrow=false
spring.datasource.druid.impl.testOnReturn=false
spring.datasource.druid.impl.web-stat-filter.enabled=true
spring.datasource.druid.impl.stat-view-servlet.enabled=true
spring.main.allow-bean-definition-overriding=true
# 配置 monitor 数据源 自动监测
spring.datasource.monitor.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.monitor.url=jdbc:oracle:thin:@localhost:1521/orcl
spring.datasource.monitor.username=orcl
spring.datasource.monitor.password=123456
spring.datasource.monitor.filter.stat.log-slow-sql=true
spring.datasource.monitor.filter.stat.slow-sql-millis=1000
spring.datasource.monitor.filter.stat.merge-sql=true
spring.datasource.monitor.filter.wall.config.multi-statement-allow=true
# 过滤器设置（第一个stat很重要，没有的话会监控不到SQL）
spring.datasource.monitor.filters=stat,wall,log4j2
spring.redis.host=127.0.0.1
spring.redis.port=6379
spring.redis.password=
spring.redis.database=1
swagger.enable=true数据源config类
主数据源
package com.yzb.csdz.core.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.yzb.csdz.mapper.master", sqlSessionTemplateRef = "masterSqlSessionTemplate")
public class MasterDataSourceConfiguration {
@Primary
@Bean(name = "masterDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.master")
public DataSource dataSource() {
return DruidDataSourceBuilder.create().build();
}
@Primary
@Bean(name = "masterSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/master/*.xml"));
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);//设置
return bean.getObject();
}
@Primary
@Bean(name = "masterTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Primary
@Bean(name = "masterSqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}业务数据源1
package com.yzb.csdz.core.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.yzb.csdz.mapper.slave", sqlSessionTemplateRef = "slaveSqlSessionTemplate")
public class SlaverDataSourceConfiguration {
@Bean(name = "slaveDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave")
public DataSource dataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "slaveSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/slave/*.xml"));
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
return bean.getObject();
}
@Bean(name = "slaveTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "slaveSqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}postgres数据源
package com.yzb.csdz.core.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.yzb.csdz.mapper.postgres", sqlSessionTemplateRef = "postgresSqlSessionTemplate")
public class PostgresDataSourceConfiguration {
@Bean(name = "postgresDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.postgres")
public DataSource dataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "postgresSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("postgresDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/postgres/*.xml"));
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
return bean.getObject();
}
@Bean(name = "postgresTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("postgresDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "postgresSqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("postgresSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}业务数据源2
package com.yzb.csdz.core.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
//扫描指定包，即扫描的包的mapper数据源使用从数据源
@MapperScan(basePackages = "com.yzb.csdz.mapper.impl", sqlSessionTemplateRef = "implSqlSessionTemplate")
public class ImplDataSourceConfiguration {
@Bean(name = "implDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.impl")
public DataSource dataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "implSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("implDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/impl/*.xml"));
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);//设置
return bean.getObject();
}
@Bean(name = "implTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("implDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "implSqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("implSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}进入主题，sharding数据源的配置，中间踩了不少坑，发出来供大家一起探讨学习，如果解释有不对的地方，欢迎指出。
sharding数据源配置
package com.yzb.csdz.core.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.yzb.csdz.util.DateUtils;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
@Configuration
@MapperScan(basePackages = "com.yzb.csdz.mapper.sharding", sqlSessionTemplateRef = "shardingSqlSessionTemplate")
public class ShardingDataSourceConfiguration {
@Value("${spring.datasource.monitor.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.monitor.url}")
private String url;
@Value("${spring.datasource.monitor.username}")
private String username;
@Value("${spring.datasource.monitor.password}")
private String password;
@Value("${spring.datasource.monitor.filters}")
private String filters;
@Bean(name = "shardingDataSource")
public DataSource dataSource() {
try {
DruidDataSource build = new DruidDataSource();
build.setUsername(username);
build.setPassword(password);
build.setDriverClassName(driverClassName);
build.setUrl(url);
build.setFilters(filters);
build.setValidationQuery("SELECT 1 FROM DUAL");
build.setTestWhileIdle(true);
build.setTestOnBorrow(false);
build.setTestOnReturn(false);
//分库设置
Map<String, DataSource> dataSourceMap = new HashMap<>(1);
dataSourceMap.put("monitor", build);
// 配置 t_user 表规则
TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration("CSDZ_MONITOR_DATA", "monitor.CSDZ_MONITOR_DATA$->{2018..2021}");
// 配置分表规则
tableRuleConfiguration.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("COLLECT_TIME", new MonitorShardingAlgorithm()));
// Sharding全局配置
ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
shardingRuleConfiguration.getTableRuleConfigs().add(tableRuleConfiguration);
// // 创建数据源
Properties properties = new Properties();
properties.setProperty("sql.show", "true");
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, properties);
return dataSource;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
static class MonitorShardingAlgorithm implements PreciseShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
for (String table : collection) {
int year = DateUtils.getYear(DateUtils.strToDate(preciseShardingValue.getValue()));
if (table.endsWith(year + "")) {
return table;
}
}
return "";
}
}
@Bean(name = "shardingSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("shardingDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/sharding/*.xml"));
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);//设置
return bean.getObject();
}
@Bean(name = "shardingTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("shardingDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "shardingSqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("shardingSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
部署完成，启动看下有啥问题。。。。
已启动果然有问题，这问题把我整蒙了，这里贴一下主要错误信息
021-11-22 15:47:10.072 INFO 4064 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-2} inited
2021-11-22 15:47:10.285 INFO 4064 --- [ main] o.a.s.core.log.ConfigurationLogger : ShardingRuleConfiguration:
tables:
CSDZ_MONITOR_DATA:
actualDataNodes: monitor.CSDZ_MONITOR_DATA$->{2018..2020}
logicTable: CSDZ_MONITOR_DATA
tableStrategy:
standard:
preciseAlgorithmClassName: com.yzb.csdz.core.config.ShardingDataSourceConfiguration$MonitorShardingAlgorithm
shardingColumn: COLLECT_TIME
2021-11-22 15:47:10.286 INFO 4064 --- [ main] o.a.s.core.log.ConfigurationLogger : Properties:
sql.show: 'true'
2021-11-22 15:47:10.293 INFO 4064 --- [ main] ShardingSphere-metadata : Loading 1 logic tables' meta data.
2021-11-22 15:47:11.437 INFO 4064 --- [ main] ShardingSphere-metadata : Loading 1559 tables' meta data.
2021-11-22 15:47:11.468 ERROR 4064 --- [ main] druid.sql.Statement : {conn-110001, stmt-120001} execute error. SELECT * FROM "SYS_IOT_OVER_70794" WHERE 1 != 1
java.sql.SQLSyntaxErrorException: ORA-00942: 表或视图不存在
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2883)
at com.alibaba.druid.filter.FilterAdapter.statement_executeQuery(FilterAdapter.java:2514)
at com.alibaba.druid.filter.FilterEventAdapter.statement_executeQuery(FilterEventAdapter.java:302)
at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2880)
at com.alibaba.druid.wall.WallFilter.statement_executeQuery(WallFilter.java:534)
at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2880)
at com.alibaba.druid.filter.FilterAdapter.statement_executeQuery(FilterAdapter.java:2514)
at com.alibaba.druid.filter.FilterEventAdapter.statement_executeQuery(FilterEventAdapter.java:302)
at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2880)
at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeQuery(StatementProxyImpl.java:221)
at com.alibaba.druid.pool.DruidPooledStatement.executeQuery(DruidPooledStatement.java:297)
at org.apache.shardingsphere.sql.parser.binder.metadata.column.ColumnMetaDataLoader.load(ColumnMetaDataLoader.java:75)
at org.apache.shardingsphere.sql.parser.binder.metadata.schema.SchemaMetaDataLoader.load(SchemaMetaDataLoader.java:84)
at org.apache.shardingsphere.sql.parser.binder.metadata.schema.SchemaMetaDataLoader.load(SchemaMetaDataLoader.java:76)
at org.apache.shardingsphere.core.metadata.ShardingMetaDataLoader.loadDefaultSchemaMetaData(ShardingMetaDataLoader.java:142)
at org.apache.shardingsphere.core.metadata.ShardingMetaDataLoader.load(ShardingMetaDataLoader.java:126)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.ShardingRuntimeContext.loadSchemaMetaData(ShardingRuntimeContext.java:65)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.MultipleDataSourcesRuntimeContext.createMetaData(MultipleDataSourcesRuntimeContext.java:57)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.MultipleDataSourcesRuntimeContext.<init>(MultipleDataSourcesRuntimeContext.java:51)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.ShardingRuntimeContext.<init>(ShardingRuntimeContext.java:49)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource.<init>(ShardingDataSource.java:54)
at org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory.createDataSource(ShardingDataSourceFactory.java:48)
at com.yzb.csdz.core.config.ShardingDataSourceConfiguration.dataSource(ShardingDataSourceConfiguration.java:72)
at com.yzb.csdz.core.config.ShardingDataSourceConfiguration$$EnhancerBySpringCGLIB$$23dcc7ef.CGLIB$dataSource$0(<generated>)
at com.yzb.csdz.core.config.ShardingDataSourceConfiguration$$EnhancerBySpringCGLIB$$23dcc7ef$$FastClassBySpringCGLIB$$68151d6c.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:244)
at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:331)
at com.yzb.csdz.core.config.ShardingDataSourceConfiguration$$EnhancerBySpringCGLIB$$23dcc7ef.dataSource(<generated>)
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.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154)
at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:650)
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:483)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1336)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1176)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:556)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1307)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1227)
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:884)
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:788)
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:538)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1336)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1176)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:556)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1307)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1227)
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:884)
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:788)
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:538)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1336)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1176)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:556)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:330)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:113)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1697)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1442)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:207)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.resolveBeanByName(AbstractAutowireCapableBeanFactory.java:453)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:527)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:497)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:650)
at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:239)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:130)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessProperties(CommonAnnotationBeanPostProcessor.java:318)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1420)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1307)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1227)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:521)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:497)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:650)
at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:239)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:130)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessProperties(CommonAnnotationBeanPostProcessor.java:318)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1420)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:897)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:879)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:551)
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:143)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
at com.yzb.csdz.CsdzApplication.main(CsdzApplication.java:14)
2021-11-22 15:47:11.479 WARN 4064 --- [ main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'csdzAutoMonitorController': Injection of resource dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'csdzDeviceConfigServiceImpl': Injection of resource dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'csdzShardingMonitorMapper' defined in file [D:\yzb_project\sub_table\yzb_csdz\csdz_suf\target\classes\com\yzb\csdz\mapper\sharding\CsdzShardingMonitorMapper.class]: Cannot resolve reference to bean 'shardingSqlSessionTemplate' while setting bean property 'sqlSessionTemplate'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'shardingSqlSessionTemplate' defined in class path resource [com/yzb/csdz/core/config/ShardingDataSourceConfiguration.class]: Unsatisfied dependency expressed through method 'sqlSessionTemplate' parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'shardingSqlSessionFactory' defined in class path resource [com/yzb/csdz/core/config/ShardingDataSourceConfiguration.class]: Unsatisfied dependency expressed through method 'sqlSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'javax.sql.DataSource' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Qualifier(value=shardingDataSource)}
2021-11-22 15:47:11.480 INFO 4064 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-0} closing ...what？表或试图不存在，什么鬼，难道是我表配置不对，不可能啊~~~之后经过跟踪源码排查发现他去读取当前用户表的时候把所有表都读取出来了包括一些oracle内置表都读取出来，当前用户甚至db用户都无法查询到这些表。下面贴一下我跟踪源码的截图~~~~
根据上面发现是读取表的问题，进去getTables方法获取一下sql语句看看是查询出来的到底是什么鬼东西。
将sql复制出来去plsql工具中查询看看。
查询发现他将所有当前用户表空间的表都查询出来了，oracle内置的表也查询出来了，仔细一看，那我只查询我属于我当前用户的表呢改下sql以后发现好像可行。如下图
到此升起了修改源码的想法，那我修改了sharding中JdbcUtil类的getSchema中oracle返回null，返回当前数据库用户不就行了吗？说干就干。根据以下卡片路径下载源码。下载sharding-jdbc4.1.1版本
https://archive.apache.org/dist/shardingsphere/4.1.1/apache-shardingsphere-4.1.1-src.zip 用idea打开此解压后的文件，下载对应依赖包。全局搜索JdbcUtil，修改返回的null字段。为对应用户。由于sharding的readme中提供的打包方法不知道为啥报错了。我嫌麻烦修直接编译了下，复制JdbcUtil.class的文件到本地仓库中jar中的JdbcUtil.class替换掉。下面是替换过程的截图
准备的差不多了，运行看看。
皇天不负有心人，终于读取正常了。当前用户下只有108个表权限。直接运行看看。
中间过程可能比较久，表越多运行的越久。这是sharding在循环查询匹配表的结果。
感谢一下僻静小角落博主提供的宝贵经验，提供给我不一样的思路，解决多种数据库类型sharding无法使用的问题。僻静小角落sharding-jdbc整合博客https://www.cnblogs.com/lyosaki88/p/springboot_shardingjdbc_druid_mybatis.html 后记：分表是成功，crud都是可以的，但是只能单表查询，无法关联查询（我太菜了，不知道怎么使用）。由于需求上关联查询和统计的比较多，最后没办法还是放弃了使用sharding-jdbc。可能还是我技术不到位，不会用sharding-jdbc，但是却给了我一次宝贵的经验，在此分享一下我整合的过程~~~~
扫一扫
6756
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
