2021-10-20 springboot、sharding-jdbc、jpa、bootstrap.yml、postgre 实现分表

sharding-jdbc 使用YAML配置

！！！注意 本文仅为一个测试demo，为调通分表方便，项目命名并未按照驼峰式规则。

首先调通jpa、项目结构如下

springboot+jpa项目调通启动

pom引入包

  <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
  </dependency>

bootstrap设置

部分bootstrap设置如下（未包含所有设置项）

server:
  port: 18501
spring:
	application:
    	name: sharding_test
    	dbname: test_sharding_2
    server:
    # ip: 192.168....
  jpa:
    show-sql: true
    database: postgresql
    properties:
      hibernate:
        hbm2ddl:
          auto: update #none 设置为updata jpa自动创建表，后续需要删除原表
sharding:
  jdbc:
    datasource:
      names: ds
      ds:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: org.postgresql.Driver
        url: jdbc:postgresql://${spring.server.ip}:5432/${spring.application.dbname}
        username: admin
        password: 123456
    config:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds.t_order_$->{0..1}
#            local-table: t_order_
            table-strategy:
              inline:
                sharding-column: other_id
                algorithm-expression: t_order_$->{ other_id % 2}
#            keyGenerator:
#              column: id
#              type: UUID

props:
  sql.show: true

文件代码

controller

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.Map;

/**
 * @author 
 * @date 2021/10/20-0020  13:31:15
 * @Description:
 */
@RestController
@RequestMapping("/demo")
public class TOrderController {

    @Autowired
    private TOrderService tOrderService;

    @GetMapping
    public Map<String, Object> getList() {
        System.out.println(tOrderService.getAll());
        return null;
    }

    @PostMapping
    public void add(@RequestBody TOrderEntity tOrderEntity) {
        tOrderService.save(tOrderEntity);
    }

    @PutMapping
    public void update(@RequestBody TOrderEntity tOrderEntity) {
        tOrderService.save(tOrderEntity);
    }

    @DeleteMapping
    public void update(Long name){
        tOrderService.delete(name);}
}

entity

import lombok.Data;
import javax.persistence.*;
import java.io.Serializable;

/**
 * @author 
 * @date 2021/10/20-0020  13:18:22
 * @Description:
 */
@Entity
@Data
@Table(name = "t_order")
public class TOrderEntity implements Serializable {

    @Id
    @Column(name = "order_id")
    // 此处不设置主键规则，程序启动后需要删除该表
    // 并设置分表后的新表的主键规则，需要程序启动后通过sql语句创建一个序列
    // 将所有分表设置为同一序列
//    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "other_id")
    private Long otherId;
}

repository

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * @author 
 * @date 2021/10/20-0020  13:22:33
 * @Description:
 */
@Repository
public interface TOrderRepository extends JpaRepository<TOrderEntity,Long> {
}

service

import java.util.List;

/**
 * @author 
 * @date 2021/10/20-0020  13:23:31
 * @Description:
 */
public interface TOrderService {

    void save(TOrderEntity tOrderEntity);

    void update(TOrderEntity tOrderEntity);

    void delete(Long id);

    List<TOrderEntity> getAll();

}

serviceImpl

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author 
 * @date 2021/10/20-0020  13:27:39
 * @Description:
 */
@Service
public class TOrderServiceImpl implements TOrderService {

    @Autowired
    TOrderRepository tOrderRepository;
    @Override
    public void save(TOrderEntity tOrderEntity) {
        tOrderRepository.save(tOrderEntity);
    }

    @Override
    public void update(TOrderEntity tOrderEntity) {
        tOrderRepository.save(tOrderEntity);
    }

    @Override
    public void delete(Long id) {
        tOrderRepository.deleteById(id);
    }

    @Override
    public List<TOrderEntity> getAll() {
        return tOrderRepository.findAll();
    }
}

启动类

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

/**
 * @author 
 * @date 2021/10/20-0020  13:10:36
 * @Description:
 */
@SpringBootApplication
public class ShardingDemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingDemoApplication.class,args);
    }
}

注意事项

使用sharding-jdbc，在启动项目后需要手动删除jpa创建的表。并手动创建按照设定逻辑分出的表。（10/21更新，不用删除jpa启动建表也能启动，不受影响，但必须保证序列生效）

创建表sql（这里使用复制表结构的sql）
sql1 复制jpa自动建表的表结构
sql3 设定所有表使用某同一序列
sq2 为数据库设定一个序列
其中originalTableName 对应的value 在bootstrap 中设置

    @Modifying
    @Transactional(rollbackFor = Exception.class)
    public Boolean createTableMin(String tableName) {
        @Value("${table.name}")
        private String originalTableName;
        String sql = "create  table " + tableName + " as select * from " + originalTableName  ;

//
//        String sql2 = "CREATE SEQUENCE \"public\". \"序列名\" " +
//                "INCREMENT 1\n" +
//                "START 1\n" +
//                "MINVALUE 1\n" +
//                "MAXVALUE 99999999\n" +
//                "CACHE 1;";
//
//
        String sql3 = "alter table " +
                tableName +
                " alter column id set default nextval('${序列名}');";
        try {
            Query nativeQuery = entityManager.createNativeQuery(sql);
            nativeQuery.executeUpdate();
//            nativeQuery = entityManager.createNativeQuery(sql2);
//            nativeQuery.executeUpdate();
            nativeQuery = entityManager.createNativeQuery(sql3);
            nativeQuery.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

2021/10/21 更新

bootstrap.yml内

 actual-data-nodes: ds.表名$->{1..10000}

该项设置会自动建分表。
若使用sql手动控制分表，不需要配置该选项。规则配置正确后，插入数据依然能走分表。