【开源项目】使用ShardingJdbc进行分库分表

最新推荐文章于 2024-06-22 10:04:11 发布

秋装什么

最新推荐文章于 2024-06-22 10:04:11 发布

阅读量317

点赞数

分类专栏：开源项目介绍文章标签：开源 java mybatis

本文链接：https://blog.csdn.net/qq_42985872/article/details/129199976

版权

开源项目介绍专栏收录该内容

53 篇文章 8 订阅

订阅专栏

项目介绍

Sharding-JDBC定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
支持任意实现JDBC规范的数据库。目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。

示例

引入Maven依赖，直接上pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.charles</groupId>
    <artifactId>demo-sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo-sharding-jdbc</name>
    <description>demo-sharding-jdbc</description>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.6</version>
        </dependency>
        <!-- 核心依赖-mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.3.7.RELEASE</version>
                <configuration>
                    <mainClass>com.charles.DemoShardingJdbcApplication</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

实体类

@Data
public class UserModel {

    private Long id;

    private String name;

    private Integer age;
}

Mapper类

public interface UserMapper {
    
    /** 如果是主键自动生成的，切记不要传入id，否则会报错 */
    @Insert("insert into t_user(name,age) values(#{name},#{age})")
    void insert(UserModel user);


    @Select("select * from t_user")
    List<UserModel> selectAll();


    @Select("select * from t_user where name like #{name}")
    List<UserModel> selectLike(String name);


    @Select("select * from t_user where name like #{name} limit 1")
    List<UserModel> selectLikePage(String name);

}

查询入口

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;


    @GetMapping("/add")
    public Object add() {
        UserModel model = new UserModel();
        model.setName("test1");
        model.setAge(2);
        userMapper.insert(model);
        return "ok";
    }


    @GetMapping("/list")
    public Object list() {
        return userMapper.selectAll();
    }


    @GetMapping("/like")
    public Object like() {
        return userMapper.selectLike("%2%");
    }

    @GetMapping("/page")
    public Object page() {
        return userMapper.selectLikePage("%2%");
    }
}

配置文件application.yml，定义了真实节点的集合，分表的策略，分库的策略。

# 应用服务 WEB 访问端口
server:
  port: 8080

  # 应用名称
spring:
  application:
    name: demo-sharding-jdbc
  shardingsphere:
    # 是否打印sql
    props:
      sql:
        show: true
    datasource:

      # 有几个库
      names: db1,db2
      # 库1 的配置
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db1?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
        username: root
        password: root
      # 库2 的配置
      db2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db2?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
        username: root
        password: root
    sharding:
      # 默认的库
      default-data-source-name: db1
      # 绑定的表 不配置也没找出啥问题
      binding-tables: t_user
      # 配置表的分片规则
      tables:
        # 指定某个表的分片配置
        t_user:
          # 这个配置是告诉sharding有多少个库和多少个表
          actual-data-nodes: db$->{1..2}.t_user_$->{1..2}
          #分库策略
          database-strategy:
            # 行表达式模式
            inline:
              # 选择需要分库的字段，根据那个字段进行区分
              sharding-column: age
              # 表达式，分库的算法，这个是通过年龄取模然后决定落到哪个库
              algorithm-expression: db$->{age % 2 + 1}
          # 主键生成策略（如果是自动生成的，在插入数据的sql中就不要传id，null也不行，直接插入字段中就不要有主键的字段）
          key-generator:
            # 对应的数据库表的主键
            column: id
            # 生成方式， 雪花模式
            type: SNOWFLAKE
          # 配置表分片策略
          table-strategy:
            # 行表达式
            inline:
              # 配置表分片的字段
              sharding-column: id
              # 配置表分片算法
              algorithm-expression: t_user_$->{id % 2 +1}


mybatis.configuration.map-underscore-to-camel-case: true

建表语句

create table t_user_xx
(
    id   bigint auto_increment comment '用户id'
        primary key,
    age  int         null comment '年龄',
    name varchar(50) null comment '姓名'
);

测试效果：

访问http://localhost:8080/add，新增用户数据

2023-02-16 10:57:02.511  INFO 17500 --- [nio-8080-exec-3] ShardingSphere-SQL                       : Logic SQL: insert into t_user(name,age) values(?,?)
2023-02-16 10:57:02.511  INFO 17500 --- [nio-8080-exec-3] ShardingSphere-SQL                       : SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@c3a3036, tablesContext=TablesContext(tables=[Table(name=t_user, alias=Optional.absent())], schema=Optional.absent())), columnNames=[name, age], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=36, stopIndex=36, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=38, stopIndex=38, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[test1, 2])])
2023-02-16 10:57:02.512  INFO 17500 --- [nio-8080-exec-3] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into t_user_1(name,age, id) values(?, ?, ?) ::: [test1, 2, 832932642843262976]

访问http://localhost:8080/list，查询用户列表

2023-02-16 10:58:39.016  INFO 17500 --- [nio-8080-exec-6] ShardingSphere-SQL                       : Logic SQL: select * from t_user
2023-02-16 10:58:39.016  INFO 17500 --- [nio-8080-exec-6] ShardingSphere-SQL                       : SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1959b6e5, tablesContext=TablesContext(tables=[Table(name=t_user, alias=Optional.absent())], schema=Optional.absent())), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.absent())], columnLabels=[id, age, name]), groupByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.groupby.GroupByContext@1a6061cb, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@725bc7a1, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@39436183, containsSubquery=false)
2023-02-16 10:58:39.016  INFO 17500 --- [nio-8080-exec-6] ShardingSphere-SQL                       : Actual SQL: db1 ::: select * from t_user_1
2023-02-16 10:58:39.016  INFO 17500 --- [nio-8080-exec-6] ShardingSphere-SQL                       : Actual SQL: db1 ::: select * from t_user_2
2023-02-16 10:58:39.016  INFO 17500 --- [nio-8080-exec-6] ShardingSphere-SQL                       : Actual SQL: db2 ::: select * from t_user_1
2023-02-16 10:58:39.016  INFO 17500 --- [nio-8080-exec-6] ShardingSphere-SQL                       : Actual SQL: db2 ::: select * from t_user_2