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

Basic Principles of Combined Use

MyBatis‑Plus handles ORM and single‑table enhancements, while ShardingSphere‑JDBC intercepts SQL for sharding, routing, rewriting and result merging. They work together; MyBatis‑Plus is unaware of sharding.

Core Responsibilities

MyBatis‑Plus (MP)

Position: Enhances MyBatis without changing its core.

Core Functions: Provides generic CRUD interfaces such as IService, BaseMapper, condition builders QueryWrapper, UpdateWrapper, pagination interceptor PaginationInnerInterceptor, etc.

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

ShardingSphere‑JDBC

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

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

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

Collaborative Workflow (Core Process)

Application Layer Call – code calls MP methods such as baseMapper.insert(entity) or service.page(page, queryWrapper).

MP Generates Logical SQL – MP creates a complete SQL using entity annotations (e.g., @TableName) and condition wrappers. The logical table name is the one defined in the entity.

Sharding‑JDBC Intercepts and Processes

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

Routing – uses configured sharding strategy (e.g., t_user table with id % 2) to decide target physical database/table.

SQL Rewriting – rewrites logical SQL to physical SQL (e.g., replace table name, adjust LIMIT for pagination, generate distributed primary keys).

Execution – sends rewritten SQL to each physical data source, possibly in parallel.

Result Merging – merges results from all shards; simple queries may hit a single shard, while pagination, aggregation, and join queries require in‑memory merging.

Result Return – ShardingSphere‑JDBC returns merged results to MyBatis/MP, which maps them to entity objects.

Core Pitfall Details

1. SQL Parsing Issues

Problem: ShardingSphere‑JDBC requires specific SQL format; custom or dynamic SQL from MP may cause parsing failures.

Solution: Use MP’s QueryWrapper or LambdaQueryWrapper to generate SQL; avoid complex <if> in XML; for complex cases use @Select annotation with proper formatting.

# application.yml
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 Conflict

Problem: MP’s pagination plugin adds LIMIT clause that conflicts with ShardingSphere‑JDBC’s pagination.

Solution: Use ShardingSphere‑JDBC’s pagination; disable MP’s PageHelper.

// Correct: use ShardingSphere‑JDBC pagination
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

Problem: MP’s @TableId(type = IdType.AUTO) conflicts with ShardingSphere‑JDBC’s distributed key generator.

Solution: Set IdType.INPUT and configure ShardingSphere‑JDBC 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. Query Condition Missing Sharding Key

Problem: Queries without the sharding key cause full‑table scans across all shards.

Solution: Include sharding key in conditions or use HintManager to manually specify it.

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

// Hint usage when key cannot be in condition
try (HintManager hintManager = HintManager.getInstance()) {
    hintManager.addTableHint("t_order", "user_id=123");
    List<Order> orders = orderMapper.selectList(queryWrapper);
}

5. Transaction Management Complexity

Problem: Cross‑shard transactions become complex and may lead to inconsistency.

Solution: Prefer single‑shard operations; when necessary, use distributed transaction frameworks such as Seata.

# application.yml
spring:
  shardingsphere:
    rules:
      sharding:
    transaction:
      type: Seata
      provider-type: SeataAT

6. Broadcast Table Misconfiguration

Problem: Tables that should be broadcast (e.g., dictionary tables) are not configured, causing unnecessary queries on all shards.

Solution: Mark tables as broadcast in ShardingSphere‑JDBC configuration.

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

7. SQL Injection Risk

Problem: Dynamic SQL combined with sharding conditions may expose injection vulnerabilities.

Solution: Use MP’s condition builders, avoid string concatenation, and strictly validate user input.

// Safe query with QueryWrapper
QueryWrapper<Order> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("user_id", userId); // userId must be validated

if (!userId.matches("^\\d+$")) {
    throw new IllegalArgumentException("Invalid user ID");
}

8. Poor Sharding Algorithm Design

Problem: Bad sharding key choice leads to hotspot shards.

Solution: Choose high‑cardinality, uniformly distributed fields; 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

Problem: Concurrent updates may overwrite each other, causing data loss.

Solutions:

Use optimistic lock with @Version and enable MyBatis‑Plus optimistic‑locker.

Explicitly verify original values before update.

Update only necessary fields using UpdateWrapper.

// Optimistic lock example
public class Order {
    @TableId(type = IdType.AUTO)
    private Long id;
    @Version
    private Integer version;
    // other fields
}
mybatis-plus:
  global-config:
    optimistic-locker:
      enable: true

// Update with optimistic lock
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, retry");
}

Best‑Practice Summary

Pitfall

Core Advice

Implementation Points

SQL Parsing

Avoid complex SQL

Use QueryWrapper; avoid nested <if> in XML

Pagination

Use ShardingSphere‑JDBC pagination

Disable MyBatis‑Plus pagination plugin

Primary Key

Use ShardingSphere‑JDBC key generator

Set @TableId(type = IdType.INPUT) and configure key‑generator

Query Condition

Include sharding key

Ensure condition contains sharding key; use HintManager if needed

Transaction

Avoid cross‑shard transactions

Design business for single shard; use Seata when required

Broadcast Table

Configure correctly

Add table to broadcast-tables SQL Injection

Strict input validation

Use condition builders; forbid string concatenation

Sharding Algorithm

Select high‑cardinality field

Use consistent hash or similar strategy

Update Overwrite

Prefer optimistic lock

Add @Version and enable locker; or update specific fields only

Conclusion

Combining MyBatis‑Plus with ShardingSphere‑JDBC effectively solves performance bottlenecks in large‑scale data scenarios, but developers must pay attention to SQL parsing, pagination, key generation, sharding‑key routing, transaction handling, broadcast tables, injection protection, sharding algorithm design, and concurrent update control. Proper configuration and adherence to the best practices above enable building high‑performance, highly available, and consistent systems.