jpa整合sharding-jdbc不分库分表（包括id主键生成策略的使用）

1. 首先将官网地址与有用的博文奉献给大家在这里致谢贡献者：

https://shardingsphere.apache.org/document/5.2.0/en/overview/
https://blog.csdn.net/weixin_28979369/article/details/112540611

2. maven依赖

<!--        数据库连接jar 可以换成自己想用的版本-->
       <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
<!--        分库分表-->
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>1.5.4.1</version>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3. 配置数据源（数据库）.yml文件类型的配置

server:
  port: 2805
logging:
  pattern:
    file: "%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n"
    console: "%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n"
  file:
    path: "./logs/"
  file.max-size: 10MB
  level:
    org.hibernate.SQL: TRACE
    org.hibernate.type.description.sql: TRACE
    org.hibernate.type.descriptor.sql.BasicBinder: TRACE
spring:
  jpa:
    properties:
      hibernate:
        format_sql: true
        #integrator_provider: com.zgy.handle.userService.service.auto.InsertEventIntegratorProvider
    hibernate:
      ddl-auto: none #必须自己建表了！！！！必须none
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    open-in-view: false
    # 以下是重点sharding-jdbc的数据源会用到，上面的配置根据你的数据库情况而定这里我只做分表所以就有一个数据库
database0: # 这个随便取这是用来配置类里读属性的标识
  url: jdbc:mysql://localhost:3306/material?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false
  username: root # 必有
  password: 123456 # 必有
  driver-class-name: com.mysql.jdbc.Driver #数据库连接驱动必有
  max-wait: 10000
  min-idle: 2
  initial-size: 2
  validation-query: SELECT 1
  test-on-borrow: false
  test-while-idle: true
  time-between-eviction-runs-millis: 18800
  databaseName: material #数据库名必有跟url中数据库名一致
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

4.新建Database0Config类（dataSource数据源配置类）

package com.szc.material.analysisService.confg.shardingjdbc;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

@Data
@ConfigurationProperties(prefix = "database0")//读.yml文件中的属性值
@Component
public class Database0Config {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private int maxwait;
    private int  minidle;
    private int   initialsize;
    private String  validationquery;
    private boolean testonborrow;
    private boolean testwhileidle;
    private int timebetweenevictionrunsmillis;
    private String databaseName;
    public DataSource createDataSource() {
        DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(getDriverClassName());
        result.setUrl(getUrl());
        result.setUsername(getUsername());
        result.setPassword(getPassword());
        result.setMaxWait(maxwait);
        result.setMinIdle(minidle);
        result.setInitialSize(initialsize);
        result.setValidationQuery(validationquery);
        result.setTestOnBorrow(testonborrow);
        result.setTestWhileIdle(testwhileidle);
        result.setTimeBetweenEvictionRunsMillis(timebetweenevictionrunsmillis);
        return result;
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

5. 新建TableShardingAlgorithm类（自定义分表的规则）
   

package com.szc.material.analysisService.confg.shardingjdbc;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;
import org.springframework.stereotype.Component;

import java.util.Collection;
import java.util.LinkedHashSet;
//这里每个方法中的 %20 是我的分片策略改成你们的，建议你们也用id分表这样下面的代码只改 “%你分的表的个数”
//我有一个material数据库其中有一个analysis_formal_data_attribute表，我将其复制了20个做为analysis_formal_data_attribute的实际数据库表，原来的analysis_formal_data_attribute表就删掉了，向数据库存数据时会用 分片值%20来确定数据要存入哪张表
@Component
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
        /**
     *根据分片值和SQL的=运算符计算分片结果名称集合.
     * 参数:
     * availableTargetNames - 所有的可用目标名称集合, 一般是数据源或表名称
     * shardingValue - 分片值
     * 返回:
     * 分片后指向的目标名称, 一般是数据源或表名称
     */
    @Override
    public String doEqualSharding(Collection<String> collection, ShardingValue<Long> shardingValue) {
        for (String each : collection) {
            if (each.endsWith(shardingValue.getValue() % 20 + "")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
       /**
     *根据分片值和SQL的IN运算符计算分片结果名称集合.
     * 参数:
     * availableTargetNames - 所有的可用目标名称集合, 一般是数据源或表名称
     * shardingValue - 分片值
     * 返回:
     * 分片后指向的目标名称集合, 一般是数据源或表名称
     */
    @Override
    public Collection<String> doInSharding(Collection<String> collection, ShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(collection.size());
        for (Long value : shardingValue.getValues()) {
            for (String tableName : collection) {
                if (tableName.endsWith(value % 20 + "")) {
                    result.add(tableName);
                }
            }
        }
        return result;

    }
         /**
     *根据分片值和SQL的BETWEEN运算符计算分片结果名称集合.
     * 参数:
     * availableTargetNames - 所有的可用目标名称集合, 一般是数据源或表名称
     * shardingValue - 分片值
     * 返回:
     * 分片后指向的目标名称集合, 一般是数据源或表名称
     */
    @Override
    public Collection<String> doBetweenSharding(Collection<String> collection, ShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(collection.size());
        Range<Long> range = shardingValue.getValueRange();
        for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : collection) {
                if (each.endsWith(i % 20 + "")) {
                    result.add(each);
                }
            }
        }
        return result;

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

6. 新建DataSourceConfig类在里面将数据源类，表的分片规则类进行配置（sharding-jdbc主配置类，注意我这里没有分库，做分库的话也是在这配的）

package com.szc.material.analysisService.confg.shardingjdbc;

import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.keygen.DefaultKeyGenerator;
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfig {
    @Autowired
    private Database0Config database0Config;

    @Autowired
    private TableShardingAlgorithm tableShardingAlgorithm;

    @Bean
    public DataSource getDataSource() throws SQLException {
        return buildDataSource();
    }

    private DataSource buildDataSource() throws SQLException {
         // https://blog.csdn.net/u012014505/article/details/108844887 这里面有讲到 多个库的设置
        //分库设置 （我这只有一个库初始容量就设为1了）
        Map<String, DataSource> dataSourceMap = new HashMap<>(1);
        //添加数据库database0 （我只有一个库）
        dataSourceMap.put(database0Config.getDatabaseName(), database0Config.createDataSource());
        //设置默认数据库（我只有一个库，我怕其他的表没设分片策略会报错我就把我这一个库设为了默认库）
        DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, database0Config.getDatabaseName());
        //分表设置，大致思想就是将查询虚拟表数据根据一定规则映射到真实表中去，使用sharding-jdbc后sql中的表名用这个虚拟表名analysis_formal_data_attribute
        //数据库中建分表是有规则的 例如我这个analysis_formal_data_attribute分了20个
        //analysis_formal_data_attribute_$
        //$从零开始到分的总表数减一
        TableRule orderTableRule = TableRule.builder("analysis_formal_data_attribute")
                .actualTables(Arrays.asList("analysis_formal_data_attribute_0",
                        "analysis_formal_data_attribute_1",
                        "analysis_formal_data_attribute_2",
                        "analysis_formal_data_attribute_3",
                        "analysis_formal_data_attribute_4",
                        "analysis_formal_data_attribute_5",
                        "analysis_formal_data_attribute_6",
                        "analysis_formal_data_attribute_7",
                        "analysis_formal_data_attribute_8",
                        "analysis_formal_data_attribute_9",
                        "analysis_formal_data_attribute_10",
                        "analysis_formal_data_attribute_11",
                        "analysis_formal_data_attribute_12",
                        "analysis_formal_data_attribute_13",
                        "analysis_formal_data_attribute_14",
                        "analysis_formal_data_attribute_15",
                        "analysis_formal_data_attribute_16",
                        "analysis_formal_data_attribute_17",
                        "analysis_formal_data_attribute_18",
                        "analysis_formal_data_attribute_19"))
                .dataSourceRule(dataSourceRule)
                .build();

        //分库分表策略（我这里只有分表的策略，分库的话也在这配）
        ShardingRule shardingRule = ShardingRule.builder()
                .dataSourceRule(dataSourceRule)
                .tableRules(Arrays.asList(orderTableRule))
                .tableShardingStrategy(new TableShardingStrategy("id", tableShardingAlgorithm)).build();
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
        return dataSource;
    }
    //主键生成策略jpa自动生成id值会用到最后会讲的
    @Bean
    public KeyGenerator keyGenerator() {
        return new DefaultKeyGenerator();
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

7. 上面就是整个配置了你可以写个测试类了

package com.szc.material.analysisService;

import com.szc.material.analysisService.model.formalDataAttribute.FormalDataAttribute;
import com.szc.material.analysisService.model.templateAttribute.TemplateAttribute;
import com.szc.material.analysisService.repository.formalDataAttribute.FormalDataAttributeUpdateRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;



@RunWith(SpringRunner.class)
@SpringBootTest(classes = AnalysisApplication.class)
public class FTest {
    @Autowired
    FormalDataAttributeUpdateRepository formalDataAttributeUpdateRepository;
    //查询测试
    @Test
    public void a(){

        for (FormalDataAttribute dataAttribute : formalDataAttributeUpdateRepository.findAll()) {
            System.out.println(dataAttribute);
        }

    }
    //保存测试
    @Test
    public void b(){
        TemplateAttribute templateAttribute = new TemplateAttribute();
        templateAttribute.setId(27903867563803298L);
        FormalDataAttribute formalDataAttribute = new FormalDataAttribute();
        formalDataAttribute.setCode("真尼玛离谱");
       formalDataAttributeUpdateRepository.save(formalDataAttribute);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

8. 到这里你成功了吗，哈哈到这里是可以成功的如果没成功接着看，我到这里没有成功因为我的实体类在保存时是有id自动生成策略的，之前我这个项目的自动生成策略用的是Hibernate的下面的配置方法，经过我不断查资料打断点得知可能是sharding-jdbc不兼容下面generate方法中的sqll代码

package com.szc.material.analysisService.model.hibernate;

import org.hibernate.HibernateException;
import org.hibernate.MappingException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.id.Configurable;
import org.hibernate.id.IdentifierGenerator;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.type.Type;

import javax.persistence.Id;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class UUIDGenerator implements IdentifierGenerator, Configurable {
    private final String sql = "select uuid_short()";
    @Override
    public void configure(Type type, Properties properties, ServiceRegistry serviceRegistry) throws MappingException {

    }

    @Override
    public Serializable generate(SharedSessionContractImplementor sharedSessionContractImplementor, Object o) throws HibernateException {
        synchronized (this){
            Long result;
            try {
                Long id = null;
                Field[] declaredFields = o.getClass().getDeclaredFields();
                int size = declaredFields.length;
                for (int i = 0; i < size; i++){
                    Field field = declaredFields[i];
                    Id annotation = (Id)field.getAnnotation(Id.class);
                    if (annotation != null){
                        try {
                            Method method = o.getClass().getMethod("get" + field.getName().substring(0,1).toUpperCase() + field.getName().substring(1));
                            Object invoke = method.invoke(o);
                            id = (Long)invoke;
                        }catch (Exception ex){
                            ex.printStackTrace();
                        }
                        break;
                    }
                }
                if (id == null){
                    PreparedStatement st = sharedSessionContractImplementor.connection().prepareStatement("select uuid_short()");
                    try{
                        ResultSet rs = st.executeQuery();

                        long resultx;
                        try{
                            rs.next();
                            resultx = rs.getLong(1);
                        }finally {
                            rs.close();
                        }
                        return resultx;
                    }finally {
                        st.close();
                    }
                }
                result = id;
            }catch (Exception ex){
                return null;
            }
            return result;
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

之后我就用上面Sharding-jdbc的配置中的以下代码自动生成了id

@Bean
public KeyGenerator keyGenerator() {
    return new DefaultKeyGenerator();
}
1
2
3
4

改出了最后针对我这一个分表，的自动生成id的策略，废话少说上代码

package com.szc.material.analysisService.model.hibernate;

import com.dangdang.ddframe.rdb.sharding.keygen.DefaultKeyGenerator;
import org.hibernate.HibernateException;
import org.hibernate.MappingException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.id.Configurable;
import org.hibernate.id.IdentifierGenerator;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.type.Type;



import java.io.Serializable;

import java.util.Properties;
public class UUIDGenerator_FormalDataAttribute implements IdentifierGenerator, Configurable {

    private final String sql = "select uuid_short()";
    DefaultKeyGenerator defaultKeyGenerator=new DefaultKeyGenerator();
    @Override
    public void configure(Type type, Properties properties, ServiceRegistry serviceRegistry) throws MappingException {

    }

    @Override
    public Serializable generate(SharedSessionContractImplementor sharedSessionContractImplementor, Object o) throws HibernateException {
        synchronized (this){
            return defaultKeyGenerator.generateKey().longValue();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

将上面的成功策略放入@GenericGenerator中strategy 中如下图

这回表也分完了，id策略也配了，应该没事了，散会！！！关注我，豪杰带大家填坑！！！