java中分表入库的sql spring 分库分表

转载

Aceryt 2023-12-01 11:03:10

文章标签 java中分表入库的sql shardingjdbc spring maven apache 文章分类 Java 后端开发

1.下面已经实现了根据年份月份进行分表，使用的是shardingJDBC ID 生成策略为 SNOWFLAKE

2.自己传ID进来也可以根据ID进行分表，请看上一篇文章

3.自己建表这些，mybatis配置这些，就不用多说了吧，只需要下面的配置就可以使用了

但是要注意配置文件中的每个参数名的配置层级，我在进行配置的时候，就是因为参数层级位置不对的问题弄了一下午

比如：spring下面是shardingsphere 必须严格按照层级问题，重点注意

需要注意如果shardingJDBC要和druid一起使用，那么druid是spring的jar包不是springboot的jar重点注意

---------------------如果觉得pom文件冗余的话请看我上一篇文章

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.taguan</groupId>
    <artifactId>taGuanHttp</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <sharding-sphere.version>4.1.1</sharding-sphere.version>
    </properties>

    <dependencies>
        <!-- SpringBoot整合Web组件 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!--apache提供的众多commons工具包-->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8</version>
        </dependency>

        <!-- fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.58</version>
        </dependency>

        <!-- SmbFile 对远程操作共享文件操作-->
        <dependency>
            <groupId>jcifs</groupId>
            <artifactId>jcifs</artifactId>
            <version>1.3.17</version>
        </dependency>

        <!-- swagger2 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!--security 权限管理框架-->
       <!-- <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>-->

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <!-- 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.1.3</version>
        </dependency>

        <!--druid 数据源-->
        <!--<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>-->
        <!-- druid数据源 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.35</version>
        </dependency>

        <!-- 自动生成代码插件 -->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.5</version>
        </dependency>

        <!-- 日志 -->
        <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>


        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!-- 热部署插件 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>

        <!-- shardingJDBC-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>

       <!-- <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>-->

        <dependency>
            <groupId>com.xiaoleilu</groupId>
            <artifactId>hutool-all</artifactId>
            <version>3.2.0</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
            <!-- mybatis generator 自动生成代码插件 -->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.5</version>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.35</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <configurationFile>${basedir}/src/main/resources/generatorConfig.xml</configurationFile>
                    <!--允许自动覆盖的文件-->
                    <overwrite>true</overwrite>
                    <!--允许移动生成的文件-->
                    <verbose>true</verbose>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

全部采用YML配置进行数据源的配置

主配置源

application.yml

spring:
  profiles:
    active: sharding
  #日期格式化
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/tg_project?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    druid:
      initial-size: 5 #初始化大小
      min-idle: 5 #最小
      max-active: 100 #最大
      max-wait: 60000 #连接超时时间
      ##配置间隔多久进行一次检测，检测需要关闭的空闲连接，单位毫秒
      time-between-eviction-runs-millis: 60000
      ##指定空闲连接最少空闲多久可以被清除
      min-evictable-idle-time-millis: 30000
      validation-query: select 'X'
      ##当连接空闲时，是否执行连接测试
      test-while-idle: true
      ##当从连接池借用连接时，是否测试该连接
      test-on-borrow: false
      ##当连接归还到连接池时，是否测试该连接
      test-on-return: false
      ##配置监控统计拦截的filters,去掉后监控界面sql无法统计，‘wall’ 用于防火墙
      filters: wall,stat,log4j
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      max-open-prepared-statements: 20
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.slowSqlMillis=200;druid.stat.logSlowSql=true
      # WebStatFilter配置，说明请参考Druid Wiki，配置_配置WebStatFilter
      #是否启用StatFilter默认值true
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico
        session-stat-enable: true
        session-stat-max-count: 10
      # StatViewServlet配置，说明请参考Druid Wiki，配置_StatViewServlet配置
      #是否启用StatViewServlet默认值true
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        reset-enable: true
        login-username: admin
        login-password: admin
      filter:
        stat:
          db-type: mysql
          log-slow-sql: true
          slow-sql-millis: 5000
        wall:
          enabled: true
          db-type: mysql
          config:
            drop-table-allow: false
    resources:
      static-locations: classpath:/static/**,classpath:/templates/
    thymeleaf:
      #prefix：指定模板所在的目录
      prefix: classpath:templates/
      suffix: .html
      check-template-location: true
      encoding: UTF-8
      content-type: text/html
      mode: HTML5
      cache: false

mybatis:
  type-aliases-package: com.taguan.entity
  mapper-locations: classpath:mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true

server:
  port: 8080
  tomcat:
    uri-encoding: UTF-8
    max-threads: 1000
    min-spare-threads: 10
logging:
  level:
    com.taguan: debug

application-sharding.yml

shardingJDBC配置，具体配置参数，可以看官网，不做多的介绍，东西太多了

# 数据分片应用于单库分表操作
spring:
  shardingsphere:
    datasource:
      names: db1
      db1: # 数据库
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/tg_project?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: root
    sharding:
      tables:
        tg_weld_after_rule:
          # 配置表的分布，表的策略db1.tg_weld_after_rule_$->{2020..2021}0$->{1..9},db1.tg_weld_after_rule_$->{2020..2021}1$->{0..2}
          actual-data-nodes: db1.tg_weld_after_rule_$->{2021..2021}0$->{1..5}
          # 指定tg_weld_after_rule_表 主键id 生成策略为 SNOWFLAKE
          key-generator:
            column: id
            type: SNOWFLAKE
          # 指定分片策略
          table-strategy:
#            inline:
##              约定id值是偶数添加到tg_weld_after_rule_0表，如果id是奇数添加到tg_weld_after_rule_1表
#              sharding-column: id
#              algorithm-expression: tg_weld_after_rule_$->{id%2}
            standard:
              #根据年月份进行分表，分表规则自定义handler
              sharding-column: create_time
              precise-algorithm-class-name: com.taguan.utils.USerTablePreciseShardingAlgorithm
              #range-algorithm-class-name: com.taguan.utils.MyRangeShardingAlgorithm
        tg_weld_after_rule_test:
          # 配置表的分布，表的策略db1.tg_weld_after_rule_$->{2020..2021}0$->{1..9},db1.tg_weld_after_rule_$->{2020..2021}1$->{0..2}
          actual-data-nodes: db1.tg_weld_after_rule_test_$->{2021..2021}0$->{1..5}
          # 指定tg_weld_after_rule_表 主键id 生成策略为 SNOWFLAKE
          key-generator:
            column: id
            type: SNOWFLAKE
          # 指定分片策略
          table-strategy:
            standard:
              #根据年月份进行分表，分表规则自定义handler
              sharding-column: create_time
              precise-algorithm-class-name: com.taguan.utils.USerTablePreciseShardingAlgorithm
              range-algorithm-class-name: com.taguan.utils.MyRangeShardingAlgorithm
    props:
      # 打开sql输出日志
      sql:
        show: true

分片策略采用

精确分片算法

对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景。需要配合StandardShardingStrategy使用。

采用精确分片算法 PreciseShardingAlgorithm 可以不使用范围分片算法 RangeShardingAlgorithm

采用范围分片算 RangeShardingAlgorithm 就必须使用精确分片算法 PreciseShardingAlgorithm

分表分库策略类：

USerTablePreciseShardingAlgorithm

public class USerTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {

    private SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        try {
            String tableName = preciseShardingValue.getLogicTableName();
            log.info("tableName--------------{}",tableName);
            String dataTime = formatter.format(preciseShardingValue.getValue());
            String year = dataTime.substring(0, 4);
            System.out.println("-------------------------"+year);
            String key = dataTime.substring(5, 7);
            System.out.println("-------------------------"+key);
            return tableName.concat("_").concat(year).concat(key);
        }catch (Exception e){
            throw new IllegalArgumentException("没有匹配到库:" + preciseShardingValue.getValue());
        }


    }
}

自动生成代码插件----注意使用

mysql-connector-java jar包有版本问题，8版本会只有insersql 其他sql不会自动生成，我这里用的是5.1.35版本

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <!-- 数据库驱动:选择你的本地硬盘上面的数据库驱动包-->
    <classPathEntry  location="C:\Path\.m2\repository\mysql\mysql-connector-java\5.1.35\mysql-connector-java-5.1.35.jar"/>
    <context id="DB2Tables"  targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <!-- 是否去除自动生成的注释 true：是 ： false:否 -->
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!--数据库链接URL，用户名、密码 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://127.0.0.1:3306/tg_project?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT"
                        userId="root" password="root"/>

        <!-- 避免把 number 转成 bigInteger -->
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!-- 生成模型的包名和位置-->
        <javaModelGenerator targetPackage="com.taguan.entity" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!-- 生成映射文件的包名和位置-->
        <sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!-- 生成DAO的包名和位置-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.taguan.mapper" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!-- 要生成的表 tableName是数据库中的表名或视图名 domainObjectName是实体类名(不生成模板类)-->
        <table tableName="tg_weld_after_rule" domainObjectName="TgWeldAfterRule" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
        <table tableName="tg_weld_after_rule_test" domainObjectName="TgWeldAfterRuleTest" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
    </context>
</generatorConfiguration>

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。