springboot Sharding-JDBC mybatis框架整合及分库分表代码示例

在Spring Boot中整合Sharding-JDBC和MyBatis,以及实现分库分表的基本步骤如下:

  1. 添加依赖:

    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>5.0.0-RC1</version>
    </dependency>
    
  2. 配置数据源与分片规则(application.yml):

    spring:
      shardingsphere:
        datasource:
          names: ds0,ds1
          ds0:
            type: com.zaxxer.hikari.HikariDataSource
            driver-class-name: com.mysql.jdbc.Driver
            url: jdbc:mysql://localhost:3306/db0
            username: root
            password: root
          ds1:
            type: com.zaxxer.hikari.HikariDataSource
            driver-class-name: com.mysql.jdbc.Driver
            url: jdbc:mysql://localhost:3306/db1
            username: root
            password: root
    
        sharding:
          tables:
            t_order:
              actual-data-nodes: ds${0..1}.t_order${0..1}
              table-strategy:
                inline:
                  sharding-column: user_id
                  algorithm-expression: t_order${user_id % 2}
    
      mybatis:
        mapper-locations: classpath:mapper/*.xml
        type-aliases-package: com.example.model
    
  3. 创建实体类(如User.java)和对应的Mapper接口及XML文件(UserMapper.java & UserMapper.xml)。

  4. 在XML映射文件中编写SQL语句,无需关心具体的数据库和表名,由Sharding-JDBC自动处理路由。

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.example.mapper.UserMapper">
    
        <select id="selectById" resultType="com.example.model.User">
            SELECT * FROM t_order WHERE user_id = #{userId}
        </select>
    
    </mapper>
    
  5. 使用@Mapper注解将Mapper接口注入到Service或Controller中,并正常进行CRUD操作。

    @Service
    public class UserService {
    
        @Autowired
        private UserMapper userMapper;
    
        public User getUserById(Long userId) {
            return userMapper.selectById(userId);
        }
    }
    

通过上述配置和代码示例,Spring Boot项目成功整合了Sharding-JDBC和MyBatis,并实现了基于user_id的分库分表。当执行查询时,Sharding-JDBC会根据配置的策略将SQL请求路由到正确的数据库和表上。