pan1998 opened a new issue, #37728: URL: https://github.com/apache/shardingsphere/issues/37728
When I use sharding-jdbc 5.5.2 to shard Oracle database tables, it fails to automatically shard and prompts that table A_Record does not exist. Operating environment: JDK11 + Hibernate 5.6 + Spring 5.2 The content of applicationContext.xml is as follows: <?xml version="1.0" encoding="UTF-8" standalone="no"?> <beans xmlns="http://www.springframework.org/schema/beans"; xmlns:aop="http://www.springframework.org/schema/aop"; xmlns:context="http://www.springframework.org/schema/context"; xmlns:jee="http://www.springframework.org/schema/jee"; xmlns:tx="http://www.springframework.org/schema/tx"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd";> <context:property-placeholder location="classpath*:/application.properties" /> <context:spring-configured /> <context:component-scan base-package="org.nercita.dmp"> <context:exclude-filter expression="org.springframework.stereotype.Controller" type="annotation" /> </context:component-scan> <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"> <property name="driverClassName" value="${db.driverClass}"/> <property name="jdbcUrl" value="${db.url}"/> <property name="username" value="${db.username}"/> <property name="password" value="${db.password}"/> <property name="connectionTimeout" value="30000"/> <!-- 连接超时时间（毫秒） --> <property name="idleTimeout" value="25200000"/> <!-- 空闲连接超时时间（毫秒），对应 C3P0 的 maxIdleTime --> <property name="maxLifetime" value="1800000"/> <!-- 连接最大存活时间（毫秒） --> <property name="maximumPoolSize" value="10"/> <!-- 最大连接数 --> <property name="minimumIdle" value="5"/> <!-- 最小空闲连接数 --> </bean> <!-- ShardingSphere-JDBC --> <!-- 单表配置 --> <bean id="singleRuleConfiguration" class="org.apache.shardingsphere.single.config.SingleRuleConfiguration"> <constructor-arg name="tables"> <list> <value>*.*</value> </list> </constructor-arg> <constructor-arg name="defaultDataSource" value="ds0"/> </bean> <!-- 分片表配置 --> <bean id="tRecordTableRuleConfiguration" class="org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration"> <constructor-arg value="a_record"/> <constructor-arg value="ds0.a_record_$->{2025..2026}_q$->{1..4}"/> <property name="tableShardingStrategy"> <bean class="org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration"> <constructor-arg value="recordTime"/> <constructor-arg value="recordQuarterAlgorithm"/> </bean> </property> <property name="keyGenerateStrategy"> <bean class="org.apache.shardingsphere.sharding.api.config.strategy.keygen.KeyGenerateStrategyConfiguration"> <constructor-arg value="id"/> <constructor-arg value="uuid"/> </bean> </property> </bean> <bean id="recordShardingRuleConfiguration" class="org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration"> <property name="tables"> <list> <ref bean="tRecordTableRuleConfiguration"/> </list> </property> <property name="shardingAlgorithms"> <map> <entry key="recordQuarterAlgorithm"> <bean class="org.apache.shardingsphere.infra.algorithm.core.config.AlgorithmConfiguration"> <constructor-arg value="CLASS_BASED"/> <constructor-arg> <props> <prop key="strategy">STANDARD</prop> <prop key="strategyClassName">org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration</prop> <prop key="algorithmClassName">org.nercita.dmp.sharding.RecordQuarterShardingAlgorithm</prop> </props> </constructor-arg> </bean> </entry> </map> </property> <property name="keyGenerators"> <map> <entry key="uuid"> <bean class="org.apache.shardingsphere.infra.algorithm.core.config.AlgorithmConfiguration"> <constructor-arg value="UUID"/> <constructor-arg> <props></props> </constructor-arg> </bean> </entry> </map> </property> <!-- 默认数据库策略（不分库） --> <property name="defaultDatabaseShardingStrategy"> <bean class="org.apache.shardingsphere.sharding.api.config.strategy.sharding.NoneShardingStrategyConfiguration"/> </property> <!-- 默认表策略（不分表） --> <property name="defaultTableShardingStrategy"> <bean class="org.apache.shardingsphere.sharding.api.config.strategy.sharding.NoneShardingStrategyConfiguration"/> </property> </bean> <bean id="shardingDataSource" class="org.springframework.beans.factory.config.MethodInvokingFactoryBean"> <property name="targetClass" value="org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory"/> <property name="targetMethod" value="createDataSource"/> <property name="arguments"> <list> <value>${hibernate.default_schema}</value> <map> <entry key="ds0" value-ref="dataSource"/> </map> <list> <ref bean="singleRuleConfiguration"/> <ref bean="recordShardingRuleConfiguration"/> </list> <props> </props> </list> </property> </bean> <!-- sessionFactory --> <bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean"> <property name="dataSource"> <ref bean="shardingDataSource" /> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">${hibernate.dialect}</prop> <prop key="hibernate.show_sql">${hibernate.show_sql}</prop> <prop key="hibernate.format_sql">${hibernate.format_sql}</prop> <prop key="hibernate.cache.use_second_level_cache">${hibernate.use_second_level_cache}</prop> <prop key="hibernate.cache.use_query_cache">${hibernate.use_query_cache}</prop> <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop> <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl}</prop> <prop key="hibernate.default_schema">${hibernate.default_schema}</prop> <prop key="hibernate.jdbc.fetch_size">100</prop> <prop key="hibernate.jdbc.batch_size">50</prop> </props> </property> <property name="packagesToScan"> <list> <value>org.nercita.dmp.**.domain**</value> </list> </property> </bean> <!-- 事务管理器 --> <bean id="transactionManager" class="org.springframework.orm.hibernate5.HibernateTransactionManager"> <property name="sessionFactory" ref="sessionFactory" /> </bean> <!-- 开启注解事务 --> <tx:annotation-driven transaction-manager="transactionManager" /> <!-- 获取spring上下文 的ApplicationContextAware的实现Bean --> <bean class="org.nercita.core.utils.SpringContextHolder" lazy-init="false" /> <import resource="classpath*:/applicationContext-activemq.xml" /> <bean id="jedisPool" class="redis.clients.jedis.JedisPool"> <constructor-arg name="poolConfig" ref="poolConfig"></constructor-arg> <constructor-arg name="host" value="${redis.host}"></constructor-arg> <constructor-arg name="port" value="${redis.port}"></constructor-arg> <constructor-arg name="password" value="${redis.password}"></constructor-arg> <constructor-arg name="timeout" value="2000"></constructor-arg> <constructor-arg name="database" value="${redis.database}"></constructor-arg> </bean> <bean id="poolConfig" class="org.apache.commons.pool2.impl.GenericObjectPoolConfig"> </bean> </beans> hibernate.dialect=org.hibernate.dialect.DmDialect db.driverClass=dm.jdbc.driver.DmDriver db.url =jdbc:dm://192.168.1.12:5236/DMP?compatibleMode=oracle&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true hibernate.default_schema=DMP @Entity @Table(name = "A_RECORD") public class Record extends BaseUidEntity { private static final long serialVersionUID = 1L; @Column(length = 255) private String tname; @Column private Date recordTime; private String mydatestr; public String getTname() { return tname; } public void setTname(String tname) { this.tname = tname; } public Date getRecordTime() { return recordTime; } public void setRecordTime(Date recordTime) { this.recordTime = recordTime; } public String getMydatestr() { return mydatestr; } public void setMydatestr(String mydatestr) { this.mydatestr = mydatestr; } } public final class RecordQuarterShardingAlgorithm implements StandardShardingAlgorithm<Date> { private Properties props = new Properties(); @Override public void init(Properties properties) { this.props = properties; } @Override public String getType() { return "RECORD_QUARTER"; } @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) { Date dt = shardingValue.getValue(); String suffix = suffixOf(dt); String logicTable = shardingValue.getLogicTableName(); // 根据逻辑表名和计算出的后缀查找实际表名 for (String tableName : availableTargetNames) { if (tableName.toLowerCase().startsWith(logicTable.toLowerCase() + "_") && tableName.toLowerCase().endsWith(suffix.toLowerCase())) { return tableName; } } // 如果在availableTargetNames中未找到匹配的表，则构造表名 return logicTable + "_" + suffix; } @Override public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> shardingValue) { Date lower = shardingValue.getValueRange().hasLowerBound() ? shardingValue.getValueRange().lowerEndpoint() : null; Date upper = shardingValue.getValueRange().hasUpperBound() ? shardingValue.getValueRange().upperEndpoint() : null; List<String> result = new ArrayList<>(); String logicTable = shardingValue.getLogicTableName(); if (lower == null && upper == null) { return availableTargetNames; } Calendar cal = Calendar.getInstance(); if (lower == null) { cal.setTime(upper); cal.add(Calendar.YEAR, -1); lower = cal.getTime(); } if (upper == null) { cal.setTime(lower); cal.add(Calendar.YEAR, 1); upper = cal.getTime(); } Set<String> targetSuffixes = new HashSet<>(); Calendar tempCal = Calendar.getInstance(); tempCal.setTime(lower); // 计算范围内的所有季度 while (!tempCal.getTime().after(upper)) { targetSuffixes.add(suffixOf(tempCal.getTime())); tempCal.add(Calendar.MONTH, 1); // 每月检查一次，确保覆盖所有季度 } // 从availableTargetNames中找出符合要求的表名 for (String tableName : availableTargetNames) { for (String suffix : targetSuffixes) { if (tableName.toLowerCase().startsWith(logicTable.toLowerCase() + "_") && tableName.toLowerCase().endsWith(suffix.toLowerCase())) { result.add(tableName); break; } } } return result; } private String suffixOf(Date date) { Calendar cal = Calendar.getInstance(); cal.setTime(date); int month = cal.get(Calendar.MONTH) + 1; // Calendar.MONTH从0开始，所以+1 int quarter = (month - 1) / 3 + 1; // 计算季度，1-3月为第1季度，4-6月为第2季度，以此类推 return new SimpleDateFormat("yyyy").format(cal.getTime()) + "_q" + quarter; } private List<String> quartersBetween(Date start, Date end) { Calendar cal = Calendar.getInstance(); cal.setTime(start); List<String> res = new ArrayList<>(); while (!cal.getTime().after(end)) { String suffix = suffixOf(cal.getTime()); if (!res.contains(suffix)) { res.add(suffix); } cal.add(Calendar.MONTH, 3); // 每次跳3个月，即一个季度 } return res; } } -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
