Avoid Common Pitfalls When Combining MyBatis‑Plus with ShardingSphere‑JDBC

Combination Basics

MyBatis‑Plus handles ORM and single‑table enhancements, while ShardingSphere‑JDBC intercepts SQL to perform sharding routing, rewriting, and result merging; they cooperate without MyBatis‑Plus being aware of sharding.

Core Responsibilities

MyBatis‑Plus (MP)

Position: Enhances MyBatis without changing its core.

Core Features: Provides generic CRUD interfaces such as IService, BaseMapper, condition builders like QueryWrapper, UpdateWrapper, and pagination interceptor PaginationInnerInterceptor.

Working Principle: Generates standard MyBatis MappedStatement and SQL statements via inheritance and injection.

ShardingSphere‑JDBC

Position: Lightweight Java framework that adds data‑sharding, read/write separation, etc., at the JDBC driver layer.

Core Features: SQL parsing, routing, rewriting, execution, and result merging.

Working Principle: Implements core JDBC interfaces ( DataSource, Connection, PreparedStatement) and hides physical DB connections from the application.

Cooperative Workflow

When both MP and ShardingSphere‑JDBC are present, a SQL request follows these steps:

Application Layer Call : Code invokes MP methods such as baseMapper.insert(entity) or service.page(page, queryWrapper).

MP Generates Logical SQL : MP creates a complete SQL with logical table names (e.g., SELECT * FROM t_user WHERE id = 1) based on entity annotations.

Sharding‑JDBC Intercepts :

Parsing – builds an AST to understand type, fields, conditions, and table names.

Routing – uses sharding strategy (e.g., t_user_${id % 2}) to decide target physical databases/tables.

Rewriting – modifies logical SQL to physical SQL (e.g., SELECT * FROM t_user_1 WHERE id = 1) and adjusts pagination (e.g., LIMIT 0,30 for a LIMIT 10,20 request).

Execution – sends rewritten SQL to the appropriate data sources, possibly in parallel.

Merging – aggregates results, handling simple queries, pagination, and aggregation (COUNT, SUM) correctly.

Result Return : ShardingSphere‑JDBC merges results and returns them to MyBatis/MP, which maps them back to entity objects.

Key Pitfalls and Solutions

1. SQL Parsing Issues

Complex dynamic SQL or special characters may cause parsing failures. Use MP’s QueryWrapper or LambdaQueryWrapper, avoid nested <if> in XML, and prefer @Select annotations with proper formatting.

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds_${0..1}.t_order_${0..1}
            table-strategy:
              inline:
                sharding-column: order_id
                algorithm-expression: t_order_${order_id % 2}
        sql-parser:
          type: ShardingSphereSQLParser
          props:
            sql-comment-enabled: true
            sql-pretty-enabled: true

2. Pagination Conflicts

MP’s pagination plugin adds LIMIT globally, conflicting with ShardingSphere‑JDBC’s per‑shard pagination. Use ShardingSphere‑JDBC’s pagination instead and disable MP’s PageHelper.

// Correct usage with ShardingSphere‑JDBC
Page<Order> page = new Page<>(1, 10);
Page<Order> result = orderMapper.selectPage(page, null);
// Avoid MP PageHelper
// PageHelper.startPage(1, 10);
// List<Order> orders = orderMapper.selectList(null);

3. Primary‑Key Generation Conflict

MP’s @TableId(type = IdType.AUTO) relies on DB auto‑increment, which clashes with ShardingSphere’s distributed key generator. Configure MP to use IdType.INPUT and set ShardingSphere’s key generator (e.g., Snowflake).

public class Order {
    @TableId(type = IdType.INPUT)
    private Long id;
    // other fields
}

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          t_order:
            table-strategy:
              inline:
                sharding-column: order_id
                algorithm-expression: t_order_${order_id % 2}
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 123

4. Missing Sharding Key in Queries

Queries without the sharding key cause full‑table scans. Ensure the sharding key is present or use HintManager to manually specify it.

QueryWrapper<Order> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("user_id", 123); // user_id is sharding key

try (HintManager hintManager = HintManager.getInstance()) {
    hintManager.addTableHint("t_order", "user_id=123");
    List<Order> orders = orderMapper.selectList(queryWrapper);
}

5. Distributed Transaction Complexity

Avoid cross‑shard transactions when possible; if needed, integrate a distributed transaction framework like Seata.

spring:
  shardingsphere:
    transaction:
      type: Seata
      provider-type: SeataAT

6. Broadcast Table Misconfiguration

Mark tables that need to be present on every shard (e.g., dictionary tables) as broadcast tables to prevent unnecessary scans.

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          t_dict:
            actual-data-nodes: ds_${0..1}.t_dict
        broadcast-tables: t_dict

7. SQL Injection Risk

Never concatenate raw SQL strings; always use MP’s condition builders and validate user inputs.

QueryWrapper<Order> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("user_id", userId); // validate userId beforehand
if (!userId.matches("^\\d+$")) {
    throw new IllegalArgumentException("Invalid user ID");
}

8. Poor Sharding Algorithm Design

Choosing low‑cardinality fields (e.g., gender) leads to hotspot shards. Prefer high‑cardinality, uniformly distributed fields and consider consistent‑hash algorithms.

public class UserOrderShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        Long userId = shardingValue.getValue();
        int shardCount = availableTargetNames.size();
        int shardIndex = Math.abs(userId.hashCode() % shardCount);
        return availableTargetNames.stream()
                .skip(shardIndex)
                .findFirst()
                .orElse(availableTargetNames.iterator().next());
    }
}

9. Update Overwrites in Concurrency

Use optimistic locking (add @Version field) or explicitly check original values to prevent lost updates.

public class Order {
    @TableId(type = IdType.AUTO)
    private Long id;
    @Version
    private Integer version;
    // other fields
}

// Optimistic lock configuration
mybatis-plus:
  global-config:
    optimistic-locker:
      enable: true

// Update example
Order order = new Order();
order.setId(1001L);
order.setStatus("PAID");
int rows = orderMapper.updateById(order);
if (rows == 0) {
    throw new RuntimeException("Data has been modified by another user");
}

Best‑Practice Summary

SQL Parsing: Use QueryWrapper, avoid complex XML conditions.

Pagination: Rely on ShardingSphere‑JDBC pagination, disable MP’s PageHelper.

Primary‑Key Generation: Configure ShardingSphere’s key generator and set MP’s @TableId(type = IdType.INPUT).

Query Conditions: Always include the sharding key; use HintManager when necessary.

Transaction Management: Prefer single‑shard operations; use Seata for required distributed transactions.

Broadcast Tables: Declare them in broadcast-tables configuration.

SQL Injection Prevention: Validate inputs and use condition builders.

Sharding Algorithm: Choose high‑cardinality fields and consider consistent hashing.

Concurrent Updates: Apply optimistic locking ( @Version) or field‑level updates.