多源分库分表实现

梦里梦外; 2022-10-22 09:10 133阅读 0赞

项目架构：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzAx_size_16_color_FFFFFF_t_70]  
`原理`：采用哈希取余，然后定位存储或者查询哪张表

依赖：

<?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>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.4.4</version>
            <relativePath/> 
        </parent>
        <groupId>com.example</groupId>
        <artifactId>mysql</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>mysql</name>
        <description>Demo project for Spring Boot</description>
        <properties>
            <java.version>1.8</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </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>
            </dependency>
            <dependency>
                <groupId>org.mapstruct</groupId>
                <artifactId>mapstruct-jdk8</artifactId>
                <version>1.3.1.Final</version>
            </dependency>
            <dependency>
                <groupId>org.mapstruct</groupId>
                <artifactId>mapstruct-processor</artifactId>
                <version>1.3.1.Final</version>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.2.4</version>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>1.2.41</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>
            </plugins>
        </build>
    
    </project>

代码：

package com.example.mysql.config;
    
    
    import com.alibaba.druid.pool.DruidDataSource;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.boot.jdbc.DataSourceBuilder;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    
    import javax.sql.DataSource;
    
    @Configuration
    public class DataSourceConfig { 
    
    
        /** * 第一个数据连接，默认优先级最高 * @return */
        @Primary
        @Bean(name = "primaryDataSource")
        @Qualifier("primaryDataSource")
        @ConfigurationProperties(prefix="spring.datasource.primary")
        public DataSource PrimaryDataSource() { 
            return DataSourceBuilder.create().type(DruidDataSource.class).build();
        }
    
        /** * 第二个数据源 * @return */
        @Bean(name = "secondDataSource")
        @Qualifier("secondDataSource")
        @ConfigurationProperties(prefix="spring.datasource.second")
        public DataSource secondaryDataSource() { 
            return DataSourceBuilder.create().type(DruidDataSource.class).build();
        }
    }

package com.example.mysql.config;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.core.env.Environment;
    import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
    import org.springframework.orm.jpa.JpaTransactionManager;
    import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
    import org.springframework.transaction.PlatformTransactionManager;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import javax.persistence.EntityManager;
    import javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Objects;
    
    
    @Configuration
    @EnableTransactionManagement
    @EnableJpaRepositories(
            entityManagerFactoryRef="entityManagerFactoryPrimary",
            transactionManagerRef="transactionManagerPrimary",
            basePackages= {  "com.example.mysql.mapper.primary" })
    public class PrimaryConfig { 
    
        @Autowired
        private Environment environment;
    
        @Autowired
        @Qualifier("primaryDataSource")
        private DataSource primaryDataSource;
    
        @Primary
        @Bean(name = "entityManagerPrimary")
        public EntityManager entityManager(EntityManagerFactoryBuilder builder) { 
            return Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()).createEntityManager();
        }
    
        @Primary
        @Bean(name = "entityManagerFactoryPrimary")
        public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) { 
            return builder
                    .dataSource(primaryDataSource)
                    .properties(getVendorProperties())
                    .packages("com.example.mysql.entity.primary")
                    .persistenceUnit("primaryPersistenceUnit")
                    .build();
        }
    
    
    
        private Map<String, String> getVendorProperties() { 
            Map<String, String> jpaProperties = new HashMap<>(16);
            jpaProperties.put("hibernate.hbm2ddl.auto", "update");
            jpaProperties.put("hibernate.show_sql", environment.getProperty("spring.jpa.show-sql"));
            jpaProperties.put("hibernate.format_sql", environment.getProperty("spring.jpa.properties.hibernate.format_sql"));
            jpaProperties.put("hibernate.dialect", environment.getProperty("spring.jpa.properties.hibernate.primary-dialect"));
            jpaProperties.put("hibernate.current_session_context_class", "org.springframework.orm.hibernate5.SpringSessionContext");
            return jpaProperties;
        }
    
        @Primary
        @Bean(name = "transactionManagerPrimary")
        public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) { 
            return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
        }
    }

package com.example.mysql.config;
    
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.env.Environment;
    import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
    import org.springframework.orm.jpa.JpaTransactionManager;
    import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
    import org.springframework.transaction.PlatformTransactionManager;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import javax.persistence.EntityManager;
    import javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Objects;
    
    
    @Configuration
    @EnableTransactionManagement
    @EnableJpaRepositories(
            //实体管理
            entityManagerFactoryRef="entityManagerFactorySecond",
            //事务管理
            transactionManagerRef="transactionManagerSecond",
            //实体扫描,设置Repository所在位置
            basePackages= {  "com.example.mysql.mapper.second" })
    public class SecondConfig { 
    
        @Autowired
        @Qualifier("secondDataSource")
        private DataSource secondDataSource;
    
        @Autowired
        private Environment env;
    
        @Bean(name = "entityManagerSecond")
        public EntityManager entityManager(EntityManagerFactoryBuilder builder) { 
            return Objects.requireNonNull(entityManagerFactorySecond(builder).getObject()).createEntityManager();
        }
    
        @Bean(name = "entityManagerFactorySecond")
        public LocalContainerEntityManagerFactoryBean entityManagerFactorySecond (EntityManagerFactoryBuilder builder) { 
            return builder
                    .dataSource(secondDataSource)
                    .properties(getVendorProperties())
                    .packages("com.example.mysql.entity.second")
                    .persistenceUnit("secondPersistenceUnit")
                    .build();
        }
    
        private Map<String, String> getVendorProperties() { 
            Map<String, String> jpaProperties = new HashMap<>(16);
            jpaProperties.put("hibernate.hbm2ddl.auto", "update");
            jpaProperties.put("hibernate.show_sql", env.getProperty("spring.jpa.show-sql"));
            jpaProperties.put("hibernate.format_sql", env.getProperty("spring.jpa.properties.hibernate.format_sql"));
            jpaProperties.put("hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.second-dialect"));
            jpaProperties.put("hibernate.current_session_context_class", "org.springframework.orm.hibernate5.SpringSessionContext");
            return jpaProperties;
        }
    
        @Bean(name = "transactionManagerSecond")
        PlatformTransactionManager transactionManagerSecond(EntityManagerFactoryBuilder builder) { 
            return new JpaTransactionManager(entityManagerFactorySecond(builder).getObject());
        }
    }

package com.example.mysql.entity.primary;
    
    import com.example.mysql.entity.Message;
    import lombok.AccessLevel;
    import lombok.Data;
    import lombok.experimental.FieldDefaults;
    
    import javax.persistence.Entity;
    import javax.persistence.Table;
    
    @Data
    @Entity
    @Table(name = "message_01")
    @FieldDefaults(level = AccessLevel.PRIVATE)
    public class Message01 extends Message { 
    }

package com.example.mysql.entity.primary;
    
    import com.example.mysql.entity.Message;
    import lombok.AccessLevel;
    import lombok.Data;
    import lombok.experimental.FieldDefaults;
    
    import javax.persistence.Entity;
    import javax.persistence.Table;
    
    
    @Data
    @Entity
    @Table(name = "message_02")
    @FieldDefaults(level = AccessLevel.PRIVATE)
    public class Message02 extends Message { 
    }

package com.example.mysql.entity.second;
    
    import com.example.mysql.entity.Message;
    import lombok.AccessLevel;
    import lombok.Data;
    import lombok.experimental.FieldDefaults;
    
    import javax.persistence.Entity;
    import javax.persistence.Table;
    
    @Data
    @Entity
    @Table(name = "message_01")
    @FieldDefaults(level = AccessLevel.PRIVATE)
    public class Message01 extends Message { 
    }

package com.example.mysql.entity.second;
    
    import com.example.mysql.entity.Message;
    import lombok.AccessLevel;
    import lombok.Data;
    import lombok.experimental.FieldDefaults;
    
    import javax.persistence.Entity;
    import javax.persistence.Table;
    
    
    @Data
    @Entity
    @Table(name = "message_02")
    @FieldDefaults(level = AccessLevel.PRIVATE)
    public class Message02 extends Message { 
    }

package com.example.mysql.entity;
    
    import lombok.Data;
    
    import javax.persistence.*;
    
    @Data
    @MappedSuperclass
    public class Message{ 
    
        @Id
        @Column(columnDefinition = "bigint(20) COMMENT 'ID,自增'")
        @GeneratedValue(strategy = GenerationType.AUTO)
        private Long id;
    
        @Column(name = "user_no", columnDefinition = "varchar(32) COMMENT '用户编号'", nullable = false)
        String userNo;
    
        @Column(columnDefinition = "varchar(100) COMMENT '消息'", nullable = false)
        String message;
    }

package com.example.mysql.mapper.mapstruct;
    
    import com.example.mysql.entity.Message;
    import org.mapstruct.Mapper;
    import org.mapstruct.MappingTarget;
    
    /** * QueryDsl 与 mapstruct */
    @Mapper(componentModel = "spring")
    public interface MessageMapper { 
    
        Message mapToTarget(Message base, @MappingTarget Message target);
    }

package com.example.mysql.mapper.primary;
    
    import com.example.mysql.entity.primary.Message01;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface MessagePrimary01Repository extends JpaRepository<Message01, Long> { 
    }

package com.example.mysql.mapper.primary;
    
    import com.example.mysql.entity.primary.Message02;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface MessagePrimary02Repository extends JpaRepository<Message02, Long> { 
    }

package com.example.mysql.mapper.second;
    
    
    import com.example.mysql.entity.second.Message01;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface MessageSecond01Repository extends JpaRepository<Message01, Long> { 
    }

package com.example.mysql.mapper.second;
    
    import com.example.mysql.entity.second.Message02;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface MessageSecond02Repository extends JpaRepository<Message02, Long> { 
    }

package com.example.mysql.mapper;
    
    import com.example.mysql.entity.Message;
    
    import java.util.List;
    
    public interface MessageService { 
        /** * 新增一条消息 * * @return 自增的id */
        Long addMessage(Message msg);
    
        /** * 查询出给定用户的所有消息 */
        List<Message> findAll(String userNo);
    }

核心代码：

package com.example.mysql.service;
    
    
    import com.example.mysql.entity.Message;
    import com.example.mysql.mapper.MessageService;
    import com.example.mysql.mapper.mapstruct.MessageMapper;
    import com.example.mysql.mapper.primary.MessagePrimary01Repository;
    import com.example.mysql.mapper.primary.MessagePrimary02Repository;
    import com.example.mysql.mapper.second.MessageSecond01Repository;
    import com.example.mysql.mapper.second.MessageSecond02Repository;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Example;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.stereotype.Service;
    
    import javax.annotation.PostConstruct;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.function.Supplier;
    
    @Service
    public class MessageServiceImpl implements MessageService { 
    
        @Autowired
        private MessagePrimary01Repository messagePrimary01Repository;
    
        @Autowired
        private MessagePrimary02Repository messagePrimary02Repository;
    
        @Autowired
        private MessageSecond01Repository messageSecond01Repository;
    
        @Autowired
        private MessageSecond02Repository messageSecond02Repository;
    
        /** * 用来将 {@link Message} 类转换为具体的子类 */
        @Autowired
        private MessageMapper messageMapper;
    
        /** * 子表到对应实体的映射 */
        Map<Integer, Supplier<? extends Message>> entityMap;
        /** * 子表到对应 Repository 的映射 */
        Map<Integer, JpaRepository<? extends Message, Long>> repositoryMap;
    
        @PostConstruct
        void init() { 
            repositoryMap = new HashMap<>(4);
            repositoryMap.put(0, messagePrimary01Repository);
            repositoryMap.put(1, messagePrimary02Repository);
            repositoryMap.put(0, messageSecond01Repository);
            repositoryMap.put(1, messageSecond02Repository);
            entityMap = new HashMap<>(4);
            entityMap.put(0, com.example.mysql.entity.primary.Message01::new);
            entityMap.put(1, com.example.mysql.entity.primary.Message02::new);
            entityMap.put(0, com.example.mysql.entity.second.Message01::new);
            entityMap.put(1, com.example.mysql.entity.second.Message02::new);
        }
    
        /** * 定位用户的数据在哪一张表 */
        int getTablePartitionFor(String userNo) { 
            return userNo.hashCode() % 2;
        }
    
    
        /** * 定位用户所在具体实体类 */
        Supplier<? extends Message> getEntityFor(String userNo) { 
            return entityMap.get(getTablePartitionFor(userNo));
        }
    
        /** * 找出操作用户数据表的 Repository */
        JpaRepository<? extends Message, Long> getRepositoryFor(String userNo) { 
            return repositoryMap.get(getTablePartitionFor(userNo));
        }
    
    
        @Override
        public Long addMessage(Message msg) { 
            // 找到操作用户数据的 Repository
            JpaRepository repository = getRepositoryFor(msg.getUserNo());
            // 找到用户数据对应的实体
            Message entity = getEntityFor(msg.getUserNo()).get();
            // 将入参数据设置到刚才的实体中
            messageMapper.mapToTarget(msg, entity);
            // 保存实体
            repository.save(entity);
            // 返回自增id
            return entity.getId();
        }
    
    
        @Override
        public List<Message> findAll(String userNo) { 
            // 找到操作用户数据的 Repository
            JpaRepository repository = getRepositoryFor(userNo);
            // 找到用户数据对应的实体
            Message entity = getEntityFor(userNo).get();
            entity.setUserNo(userNo);
            // 按 userNo 查询
            Example<Message> exam = Example.of(entity);
            // 返回查询结果
            return repository.findAll(exam);
        }
    
    }

启动：

package com.example.mysql;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class MysqlApplication { 
    
        public static void main(String[] args) { 
            SpringApplication.run(MysqlApplication.class, args);
        }
    
    }

测试：

package com.example.mysql;
    
    import com.alibaba.fastjson.JSON;
    import com.example.mysql.entity.Message;
    import com.example.mysql.mapper.MessageService;
    import org.junit.jupiter.api.Test;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    
    import java.util.List;
    
    @SpringBootTest
    class MysqlApplicationTests { 
    
    
        @Autowired
        private MessageService messageService;
    
    
        @Test
        void contextLoads() { 
            List<Message> all = messageService.findAll("A");
            System.out.println(JSON.toJSONString(all));
        }
    
    
        @Test
        void add() { 
            Message message1 = new Message();
            message1.setId(1L);
            message1.setUserNo("A");
            message1.setMessage("A");
            messageService.addMessage(message1);
            Message message2 = new Message();
            message2.setId(1L);
            message2.setUserNo("B");
            message2.setMessage("B");
            messageService.addMessage(message2);
            Message message3 = new Message();
            message3.setId(1L);
            message3.setUserNo("C");
            message3.setMessage("C");
            messageService.addMessage(message3);
            Message message4 = new Message();
            message4.setId(1L);
            message4.setUserNo("D");
            message4.setMessage("D");
            messageService.addMessage(message4);
        }
    
    
    }

配置：

spring.datasource.primary.url=jdbc:mysql://127.0.0.1:3306/beimi?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
    spring.datasource.primary.username=root
    spring.datasource.primary.password=123456
    spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
    
    spring.datasource.second.url=jdbc:mysql://127.0.0.1:3306/bigdata?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
    spring.datasource.second.username=root
    spring.datasource.second.password=123456
    spring.datasource.second.driver-class-name=com.mysql.cj.jdbc.Driver
    
    #SQL 输出
    spring.jpa.show-sql=true
    spring.jpa.properties.hibernate.primary-dialect=org.hibernate.dialect.MySQL5InnoDBDialect
    spring.jpa.properties.hibernate.second-dialect=org.hibernate.dialect.MySQL5InnoDBDialect
    #format 一下 SQL 进行输出
    spring.jpa.properties.hibernate.format_sql=true

参考：[https://mp.weixin.qq.com/s?\_\_biz=MzI0NTYxMDg5NA==&mid=2247485525&idx=1&sn=12c97e51efee597bc9da9cb7a90933dd&chksm=e94aa8b4de3d21a2ebe5268f1b7a7d0b4f398f61750ea903b06e05c66ff15327004c15c592e4&scene=178&cur\_album\_id=1752837276922904579\#rd][https_mp.weixin.qq.com_s_biz_MzI0NTYxMDg5NA_mid_2247485525_idx_1_sn_12c97e51efee597bc9da9cb7a90933dd_chksm_e94aa8b4de3d21a2ebe5268f1b7a7d0b4f398f61750ea903b06e05c66ff15327004c15c592e4_scene_178_cur_album_id_1752837276922904579_rd]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzAx_size_16_color_FFFFFF_t_70]: /images/20221022/69b88f0149c0406a9112f6961605b605.png
[https_mp.weixin.qq.com_s_biz_MzI0NTYxMDg5NA_mid_2247485525_idx_1_sn_12c97e51efee597bc9da9cb7a90933dd_chksm_e94aa8b4de3d21a2ebe5268f1b7a7d0b4f398f61750ea903b06e05c66ff15327004c15c592e4_scene_178_cur_album_id_1752837276922904579_rd]: https://mp.weixin.qq.com/s?__biz=MzI0NTYxMDg5NA==&mid=2247485525&idx=1&sn=12c97e51efee597bc9da9cb7a90933dd&chksm=e94aa8b4de3d21a2ebe5268f1b7a7d0b4f398f61750ea903b06e05c66ff15327004c15c592e4&scene=178&cur_album_id=1752837276922904579#rd