ShardingSphere水平表实现

sharding-jdbc-core

pom.xml

		<!-- Spring data Jpa -->
        <dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
	    <!-- shardingsphere-jdbc -->
		<dependency>
		    <groupId>org.apache.shardingsphere</groupId>
		    <artifactId>sharding-jdbc-core</artifactId>
		    <version>4.1.1</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<!-- druid -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.20</version>
		</dependency>
		<!-- lombok -->
		<dependency>
		    <groupId>org.projectlombok</groupId>
		    <artifactId>lombok</artifactId>
		    <optional>true</optional>
		</dependency>

application.yml 

server:
  port: 8081
  servlet: 
   context-path: /
# Jpa配置
spring: 
  jpa:
    database: mysql
    show-sql: true

sharding:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/db1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
    username: root
    password: root

ShardingSphereProperties.java

@Data
@ConfigurationProperties(prefix = "sharding")
public class ShardingSphereProperties {

    private String driverClassName;

    private String url;

    private String username;

    private String password;

}

ShardingSphereConfig.java

@Configuration
@EnableConfigurationProperties(ShardingSphereProperties.class)
public class ShardingSphereConfig {

    @Autowired
    private ShardingSphereProperties properties;

    @Bean
    public DataSource getDate() throws SQLException {

        // 配置数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();

        // 配置第一个数据源
        DruidDataSource ds1 = new DruidDataSource();
        ds1.setDriverClassName(properties.getDriverClassName());
        ds1.setUsername(properties.getUsername());
        ds1.setUrl(properties.getUrl());
        ds1.setPassword(properties.getPassword());

        dataSourceMap.put("db1", ds1);

        // 配置第二个数据源
//        BasicDataSource ds2 = new BasicDataSource();
//        ds2.setDriverClassName("com.mysql.jdbc.Driver");
//        ds2.setUrl("jdbc:mysql://localhost:3306/ds1");
//        ds2.setUsername("root");
//        ds2.setPassword("");
//        dataSourceMap.put("test1", ds2);

        // 配置表规则 id取模分表
        // TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("user","db1.user${0..2}");
        // 配置表规则 按月份分表
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("user","db1.user0$->{1..9},db1.user1$->{0..2}");

        // 配置分库策略
        // orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
        // 分表策略 id取模分表
        // orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "user${id % 3}"));
        // 分表策略 按月份分表
        orderTableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("opt_time", new TableShardingAlgorithm()));
        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        // 省略配置order_item表规则...
        // ...

        // 获取数据源对象
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
        return dataSource;
    }

}

TableShardingAlgorithm.java

public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {

        // 真实节点
        availableTargetNames.stream().forEach((item) -> {
            System.out.printf("actual node table:%s\n", item);
        });

        //精确分片
        String tb_name = shardingValue.getLogicTableName() + "";

        // 根据当前日期 来 分库分表
        Date date = shardingValue.getValue();
//        String year = String.format("%tY", date);
        String mon =String.valueOf(Integer.parseInt(String.format("%tm", date))); // 去掉前缀0
//        String dat = String.format("%td", date);

        // 选择表
        tb_name = tb_name + mon;
        System.out.printf("插入表:%s\n", tb_name);

        for (String each : availableTargetNames) {
            if (each.equals(tb_name)) {
                return each;
            }
        }

        throw new IllegalArgumentException();
    }
}

测试：

@Entity
@Data
public class User implements Serializable {

    private static final long serialVersionUID = 3302718101440706557L;

    @Id
     // 将当前主键的值单独保存到数据库的一张表里去，主键的值每次都是从该表中查询获得，适用于任何数据库，不必担心兼容问题
    @TableGenerator(
        name = "id", // 生成器名称
        table = "hibernate_sequences", // 指定表名
        pkColumnName = "sequence_name", // primary key column name表示在持久化表中，该主键生成策略所对应键的名称
        valueColumnName="next_val", //表示在持久化表中，该主键当前所生成的值 整型
        pkColumnValue="id",    //主键名称，这里直接用了表名
        allocationSize=1            //每次主键值增加的大小
    )
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "id")
    private Integer id;

    private String name;

    private boolean sex;

    private Integer age;

    private String data;

    private Date optTime;

    /**
     * 虚拟列，不保存
     */
    @Transient
    private String hobby;
}


@RequestMapping(value="/user")
@RestController
public class UserController {

	@Autowired
	private UserRepository repository;
	
	@RequestMapping(value="/save")
	public User save(@RequestBody User user) {
	    user.setOptTime(new Date());
		return repository.save(user);
	}
	
	@GetMapping(value="/get/{id}")
	public User get(@PathVariable Integer id) {
		Optional<User> user = repository.findById(id);
		if(user.isPresent()) {
			return user.get();
		}
		return null;
	}
}

// Dao
public interface UserRepository extends CrudRepository<User, Integer> {
}

测试save  控制台打印 

Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update
Hibernate: update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?
Hibernate: insert into user (address, age, amount, data, name, opt_time, opt_user, sex, upd_time, upd_user, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
actual node table:user01
actual node table:user02
actual node table:user03
actual node table:user04
actual node table:user05
actual node table:user06
actual node table:user07
actual node table:user08
actual node table:user09
actual node table:user10
actual node table:user11
actual node table:user12
插入表:user10

sharding-jdbc-spring-boot-starter

pom改为

	    <!-- shardingsphere-jdbc -->
		<dependency>
		    <groupId>org.apache.shardingsphere</groupId>
		    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
		    <version>4.1.1</version>
		</dependency>

yml文件配置：然后就可以啦，日期分表需要添加分表策略类

server.port=9082

spring.shardingsphere.datasource.names=db1

spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/db1?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root

# 分表策略
spring.shardingsphere.sharding.tables.user.actual-data-nodes=db1.user0$->{1..9},db1.user1$->{0..2}
# 分库分片健      database-strategy 数据库策略
#spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=order_id
#spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.boke.order.sharding.time.DBShardingAlgorithm
# 分表
#spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=opt_time
#spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user$->{opt_time[5..6]}
spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=opt_time
spring.shardingsphere.sharding.tables.user.table-strategy.standard.preciseAlgorithmClassName=com.zhu.config.TableShardingAlgorithm


# 配置id生成策略
# spring.shardingsphere.sharding.tables.user.key-generator.column=id
# spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
# spring.shardingsphere.sharding.binding-tables=user

#spring.jpa.database=mysql
#spring.jpa.show-sql=true