Resolving ShardingSphere Oracle Issues: Config, Custom Sharding & Debugging Tips

This article discusses various problems encountered when using ShardingSphere with an Oracle database and provides the complete configuration, custom sharding implementation, and debugging steps that lead to a working solution.

Environment

springboot 2.2.10.RELEASE + mybatis + jpa + shardingsphere 4.1.1

Configuration Files

pom.xml

<properties>
  <java.version>1.8</java.version>
  <shardingsphere.version>4.1.1</shardingsphere.version>
</properties>
<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
  </dependency>
  <dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>4.1.3</version>
  </dependency>
  <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${shardingsphere.version}</version>
  </dependency>
  <dependency>
    <groupId>com.github.noraui</groupId>
    <artifactId>ojdbc7</artifactId>
    <version>12.1.0.2</version>
  </dependency>
</dependencies>

application.yml

server:
  port: 9900
---
spring:
  shardingsphere:
    enabled: true
    props:
      sql:
        show: true
    datasource:
      names: ds0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: oracle.jdbc.OracleDriver
        jdbcUrl: jdbc:oracle:thin:@10.100.102.113:1521/orcl
        username: empi
        password: empi
        minimumIdle: 10
        maximumPoolSize: 200
        autoCommit: true
        idleTimeout: 30000
        poolName: DS0DatabookHikariCP
        maxLifetime: 1800000
        connectionTimeout: 30000
        connectionTestQuery: SELECT 1 FROM DUAL
    sharding:
      tables:
        t_order:
          actualDataNodes: ds$->{0}.t_order_$->{2020..2020}0->{1..9}, ds$->{0}.t_order_$->{2020..2020}$->{10..12}
          tableStrategy:
            standard:
              shardingColumn: order_date
              preciseAlgorithmClassName: com.pack.sharding.algorithm.DateTableShardingAlgorithm
              rangeAlgorithmClassName: com.pack.sharding.algorithm.DataTabeRangeShardingAlgorithm
        t_product:
          actualDataNodes: ds$->{0}.t_product_$->{0..1}
          tableStrategy:
            inline:
              shardingColumn: id
              algorithmExpression: t_product_$->{id & 1}
        t_comment:
          actualDataNodes: ds$->{0}.t_comment_$->{0..1}
          tableStrategy:
            inline:
              shardingColumn: pid
              algorithmExpression: t_comment_$->{pid & 1}
      bindingTables:
        - t_product,t_comment
---
mybatis:
  config-location: classpath:/MyBatis-conf.xml
  type-aliases-package: com.pack.domain
  mapper-locations:
    - classpath:/com/pack/mapper/mysql/*.xml
---
spring:
  main:
    allowBeanDefinitionOverriding: true

Custom Sharding Algorithms

DateTableShardingAlgorithm (Precise Sharding)

public class DateTableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    private static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
        Date value = shardingValue.getValue();
        if (value == null) {
            value = new Date();
        }
        String actualTableName = shardingValue.getLogicTableName() + "_" + formatter.format(value.toInstant().atZone(ZoneId.systemDefault()));
        if (availableTargetNames.contains(actualTableName)) {
            return actualTableName;
        }
        throw new UnsupportedOperationException("未知的表名称: " + actualTableName);
    }
}

DataTabeRangeShardingAlgorithm (Range Sharding)

public class DataTabeRangeShardingAlgorithm implements RangeShardingAlgorithm<String> {
    private static final Logger logger = LoggerFactory.getLogger(DataTabeRangeShardingAlgorithm.class);
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<String> shardingValue) {
        Collection<String> result = new HashSet<>(availableTargetNames.size());
        Range<String> range = shardingValue.getValueRange();
        String lower = range.lowerEndpoint();
        String upper = range.upperEndpoint();
        if (lower != null && upper != null) {
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            LocalDate low = LocalDate.parse(lower, formatter);
            LocalDate up = LocalDate.parse(upper, formatter);
            logger.info("起始日期：{},结束日期：{}", low, up);
            while (low.isBefore(up)) {
                String tableName = shardingValue.getLogicTableName().toLowerCase() + "_" + DateTimeFormatter.ofPattern("yyyyMM").format(low);
                if (availableTargetNames.contains(tableName)) {
                    result.add(tableName);
                }
                low = low.plusMonths(1);
            }
            String upperTable = shardingValue.getLogicTableName() + "_" + DateTimeFormatter.ofPattern("yyyyMM").format(up);
            if (!result.contains(upperTable)) {
                result.add(upperTable);
            }
        }
        logger.info("查询表集合：{}", result);
        return result;
    }
}

Entity, DAO, Service

Entity

@Entity
@Table(name = "T_ORDER")
public class Orders extends BaseEntity {
    private String sno;
    private String address;
    private String name;
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date orderDate;
    @Column(columnDefinition = "int default 0")
    private Integer status;
}

Mapper Interface

public interface OrdersMapper {
    int saveOrders(Orders order);
    List<Orders> queryOrders(@Param("params") Map<String, Object> params);
}

Mapper XML (partial)

<mapper namespace="com.pack.ss.mapper.OrdersMapper">
    <resultMap type="com.pack.domain.Orders" id="ordersMapper">
        <id column="id" property="id"/>
        <id column="address" property="address"/>
        <id column="order_date" property="orderDate"/>
        <id column="name" property="name"/>
        <id column="sno" property="sno"/>
        <id column="status" property="status"/>
        <id column="create_time" property="createTime"/>
    </resultMap>
    <insert id="saveOrders" parameterType="com.pack.domain.Orders">
        insert into t_order (id, name, address, sno, order_date, status, create_time) values (#{id}, #{name}, #{address}, #{sno}, #{orderDate}, #{status}, #{createTime})
    </insert>
    <select id="queryOrders" resultMap="ordersMapper" parameterType="hashmap">
        SELECT * FROM t_order T WHERE 1 = 1
        <if test="params.status != '-1' and params.status != null">
            <![CDATA[ AND T.STATUS = #{params.status} ]]>
        </if>
        <if test="params.address != null and params.address != ''">
            <![CDATA[ AND T.ADDRESS LIKE CONCAT(CONCAT('%',#{params.address}), '%') ]]>
        </if>
        <if test="params.sno != null and params.sno != ''">
            <![CDATA[ AND T.sno = #{params.sno} ]]>
        </if>
        ORDER BY DATE DESC
    </select>
</mapper>

Service

@Service
public class OrdersService {
    @Resource
    private OrdersMapper orderMapper;
    @Transactional
    public int save(Orders order) {
        return orderMapper.saveOrders(order);
    }
    public List<Orders> queryOrdersMapperNormal(Map<String, Object> params) {
        return orderMapper.queryOrders(params);
    }
    public Pager<Orders> queryOrdersMapperPager(Map<String, Object> params) {
        return Pager.query(params, () -> orderMapper.queryOrders(params));
    }
}

Testing & Debugging

When the service started, an error "table does not exist" appeared. The investigation showed that ShardingSphere's loadAllTableNames method calls connection.getMetaData().getTables with a null schema pattern, which in Oracle returns tables from ALL_USERS. Because the code did not prepend the user name to the table, the metadata lookup failed.

Fix: Override JdbcUtil.getSchema to return null (or the correct schema) and place the custom class in the same package as the original, giving it higher class‑loading priority. After removing the if check, the application started correctly.

Various ShardingSphere versions (4.1.0, 4.0.1, 4.0.0‑RC3, 4.0.0‑RC2) exhibited different errors such as

IllegalStateException: Must have sharding column with subquery

or "invalid character". Version 4.0.0‑RC1 finally worked without code changes.

Remaining issue: a join condition written as (X.ID = Y.PID) caused an error in Oracle; removing the parentheses resolved it.

Images