Spingboot-使用AOP实现动态自动切换Mybatis数据源+分页

ゝ一世哀愁。 2022-02-27 17:12 139阅读 0赞

目标：使用AOP实现动态自动切换Mybatis数据源，分页插件使用PageHelper。  
项目使用Springboot搭建。

application.yml配置文件

server:
      port: 8086
    
    
    db1:
      datasource:
        url: jdbc:mysql://localhost:3306/demo?characterEncoding=UTF-8&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
        username: root
        password: 1234
        driver-class-name: com.mysql.cj.jdbc.Driver
    db2:
      datasource:
        url: jdbc:mysql://localhost:3306/demo2?characterEncoding=UTF-8&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
        username: root
        password: 1234
        driver-class-name: com.mysql.cj.jdbc.Driver
    
    
    
    mybatis:
      mapper-locations: classpath:mapper/*.xml
      type-aliases-package: com.example.demo.model
    
    pagehelper:
      auto-dialect: true
      reasonable: true
      support-methods-arguments: true
      params: count=countSql

数据库分别是demo和demo2，其结构相同，数据不同。  
demo：  
![在这里插入图片描述][20190328103555752.jpg]  
demo2：  
![在这里插入图片描述][20190328103633399.jpg]

pom.xml

<dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-aop</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-redis</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.0.0</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-devtools</artifactId>
                <scope>runtime</scope>
            </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.springframework.boot</groupId>
                <artifactId>spring-boot-starter-logging</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>1.2.54</version>
            </dependency>
    
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper</artifactId>
                <version>5.1.2-beta</version>
            </dependency>
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-autoconfigure</artifactId>
                <version>1.2.3</version>
            </dependency>
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.3</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-lang3</artifactId>
                <version>3.8.1</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-collections4</artifactId>
                <version>4.3</version>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.14</version>
            </dependency>
        </dependencies>

DSEnum数据源枚举类

/**
     * Created by wzx on 25/03/2019
     */
    public enum DSEnum {
        DATA_SOURCE_CORE("dataSource"),//默认数据源db1
        DATA_SOURCE_DB2("dataSource_db2");//db2
    
        private String name;
    
        DSEnum(String name) {
            this.name = name;
        }
    
        public String getName() {
            return name;
        }
    }

DataSourceContextHolder：存储当前线程数据源

/**
     * 存储当前线程数据源类型
     * Created by wzx on 22/03/2019
     */
    public class DataSourceContextHolder {
    
        private static final ThreadLocal<DSEnum> holder = new ThreadLocal();
    
        // 设置当前线程数据源
        public static void setDataSource(DSEnum dataSource) {
            holder.set(dataSource);
        }
    
        // 获取当前线程数据源
        public static DSEnum getDataSource() {
            return holder.get();
        }
    
        // 清除数据源
        public static void clearDataSource() {
            holder.remove();
        }
    
    
    }

DynamicDataSource自定义数据源类

/**
     * Created by wzx on 22/03/2019
     */
    public class DynamicDataSource extends AbstractRoutingDataSource {
    
        @Override
        protected Object determineCurrentLookupKey() {
            return DataSourceContextHolder.getDataSource();
        }
    }

DS：数据源标识,指定使用哪个数据源

/**
     * 数据源标识,指定使用哪个数据源
     * Created by wzx on 25/03/2019
     */
    @Inherited
    @Retention(RetentionPolicy.RUNTIME)
    @Target({ElementType.METHOD})
    public @interface DS {
    
        DSEnum name() default DSEnum.DATA_SOURCE_CORE;
    
    }

MyBatisConfig配置类，配置3个数据源，2个事务管理器，分别与数据源相关，1个sessionFactory，其数据源为动态数据源，并配置分页插件

/**
     * Created by wzx on 25/03/2019
     */
    @Configuration
    public class MyBatisConfig {
    
        private static Logger logger = LoggerFactory.getLogger(MyBatisConfig.class);
    
        @Primary
        @ConfigurationProperties(prefix = "db1.datasource")
        @Bean("datasource")
        public DruidDataSource dataSource() {
            return new DruidDataSource();
        }
    
        @ConfigurationProperties(prefix = "db2.datasource")
        @Bean("datasource2")
        public DruidDataSource dataSource2() {
            return new DruidDataSource();
        }
    
        @Bean(name = "txManager1")
        public PlatformTransactionManager txManager(@Qualifier("datasource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "txManager2")
        public PlatformTransactionManager txManager2(@Qualifier("datasource2") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        /**
         * 多数据源连接池配置
         *
         * @return DynamicDataSource
         */
        @Bean(name = "multiDataSource")
        public DynamicDataSource multiDataSource() {
            DruidDataSource dataSource = dataSource();
            DruidDataSource dataSource2 = dataSource2();
    
            try {
                dataSource.init();
                dataSource2.init();
            } catch (SQLException e) {
                logger.error("datasource init error");
                logger.error(e.getMessage(), e);
            }
            DynamicDataSource dynamicDataSource = new DynamicDataSource();
            Map<Object, Object> map = new HashMap<>();
            map.put(DSEnum.DATA_SOURCE_CORE, dataSource);
            map.put(DSEnum.DATA_SOURCE_DB2, dataSource2());
            dynamicDataSource.setTargetDataSources(map);
            dynamicDataSource.setDefaultTargetDataSource(dataSource);
            return dynamicDataSource;
        }
    
        @Bean("sessionFactory")
        public SqlSessionFactory sessionFactory() throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            //一定要配置动态数据源,否则无法实现动态切换
            bean.setDataSource(multiDataSource());
    
            Interceptor interceptor = new PageInterceptor();
            Properties properties = new Properties();
            properties.setProperty("helperDialect", "mysql");
            properties.setProperty("offsetAsPageNum", "true");
            properties.setProperty("rowBoundsWithCount", "true");
            properties.setProperty("reasonable", "true");
            properties.setProperty("supportMethodsArguments", "true");
            properties.setProperty("params", "pageNum=pageNumKey;pageSize=pageSizeKey;");
            interceptor.setProperties(properties);
    
            bean.setPlugins(new Interceptor[]{interceptor});
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
            return bean.getObject();
        }
    
    }

MultiDataSourceAspect切面类，实现切换数据源核心，如果有事务，数据源切换应该在开启事务之前完成切换。

/**
     * 通过AOP动态动态切换数据源
     * Created by wzx on 25/03/2019
     */
    @Aspect
    @Component
    public class MultiDataSourceAspect implements Ordered {
    
        private static Logger logger = LoggerFactory.getLogger(MultiDataSourceAspect.class);
    
        @Pointcut("@annotation(com.example.demo.config.datasource.DS)")
        private void dataSource() {
        }
    
        @Around("dataSource()")
        public Object dataSourceAround(ProceedingJoinPoint point) throws Throwable {
            MethodSignature signature = (MethodSignature) point.getSignature();
            DS annotation = signature.getMethod().getAnnotation(DS.class);
            if (annotation != null) {
                DataSourceContextHolder.setDataSource(annotation.name());
                logger.debug("设置数据源:{}", annotation.name());
            } else {
                //设置为默认数据源
                DataSourceContextHolder.setDataSource(DSEnum.DATA_SOURCE_CORE);
                logger.debug("设置默认数据源:{}", DSEnum.DATA_SOURCE_CORE);
            }
            try {
                return point.proceed();
            } finally {
                logger.debug("清理数据源信息");
                DataSourceContextHolder.clearDataSource();
            }
        }
    
        @Override
        public int getOrder() {
            return 1;
        }
    
    }

程序启动类，这里需要排除分页插件自动配置，否则会出现找到多个分页插件错误。

@SpringBootApplication(exclude = PageHelperAutoConfiguration.class)
    @MapperScan(basePackages="com.example.demo.mapper")
    public class DemoApplication implements CommandLineRunner {
    
        public static void main(String[] args) {
            SpringApplication.run(DemoApplication.class, args);
        }
    
    }

service：

/**
     * Created by wzx on 19/03/2019
     */
    public interface SysUserService {
    
        int add(SysUser sysUser);
    
        PageInfo<SysUser> getAll(Integer pageNum, Integer pageSize);
    
        PageInfo<SysUser> getAll2(Integer pageNum, Integer pageSize);
    }

/**
     * Created by wzx on 19/03/2019
     */
    @Service
    public class SysUserServiceImpl implements SysUserService {
    
        private SysUserMapper sysUserMapper;
    
        @Autowired
        public SysUserServiceImpl(SysUserMapper sysUserMapper) {
            Assert.notNull(sysUserMapper, "sysUserMapper must not be null");
            this.sysUserMapper = sysUserMapper;
        }
    
        @Override
        public int add(SysUser sysUser) {
            return sysUserMapper.insert(sysUser);
        }
    
        @Override
        public PageInfo<SysUser> getAll(Integer pageNum, Integer pageSize) {
            PageHelper.startPage(pageNum, pageSize);
            SysUserExample sysUserExample = new SysUserExample();
            List<SysUser> sysUsers = sysUserMapper.selectByExample(sysUserExample);
            return new PageInfo<>(sysUsers);
        }
    
        /**
         * 使用第二个数据源
         * @param pageNum
         * @param pageSize
         * @return
         */
        @Override
        @DS(name = DSEnum.DATA_SOURCE_DB2)
        @Transactional(value = "txManager2", readOnly = true)
        public PageInfo<SysUser> getAll2(Integer pageNum, Integer pageSize) {
            PageHelper.startPage(pageNum, pageSize);
            SysUserExample sysUserExample = new SysUserExample();
            List<SysUser> sysUsers = sysUserMapper.selectByExample(sysUserExample);
            return new PageInfo<>(sysUsers);
        }
    
    }

controller

/**
     * Created by wzx on 20/03/2019
     */
    @RestController
    @RequestMapping("/sys_user")
    public class SysUserController {
    
        private final SysUserService sysUserService;
    
        @Autowired
        public SysUserController(SysUserService sysUserService) {
            Assert.notNull(sysUserService, "sysUserService must not be null");
            this.sysUserService = sysUserService;
        }
    
        @GetMapping("/query/{pageNum}/{pageSize}")
        public Object queryPage(@PathVariable Integer pageNum, @PathVariable Integer pageSize) {
            return sysUserService.getAll(pageNum, pageSize);
        }
    
    
    }

/**
     * Created by wzx on 25/03/2019
     */
    @RestController
    @RequestMapping("/dynamic_datasource")
    public class DynamicDatasourceController {
    
        private final SysUserService sysUserService;
    
        @Autowired
        public DynamicDatasourceController(SysUserService sysUserService) {
            Assert.notNull(sysUserService, "sysUserService must not be null");
            this.sysUserService = sysUserService;
        }
    
        @GetMapping("/query/{pageNum}/{pageSize}")
        public Object queryPage(@PathVariable Integer pageNum, @PathVariable Integer pageSize) {
            return sysUserService.getAll2(pageNum, pageSize);
        }
    
    
    
    }

调用接口测试数据源1：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmc3MDQ5ODc1NjI_size_16_color_FFFFFF_t_70]  
返回结果：

{"pageNum":1,"pageSize":2,"size":2,"startRow":1,"endRow":2,"total":2,"pages":1,"list":[{"id":1,"name":"test1","age":12,"birthday":"2019-03-28T02:31:24.000+0000"},{"id":2,"name":"test2","age":12,"birthday":"2019-03-28T02:31:39.000+0000"}],"prePage":0,"nextPage":0,"isFirstPage":true,"isLastPage":true,"hasPreviousPage":false,"hasNextPage":false,"navigatePages":8,"navigatepageNums":[1],"navigateFirstPage":1,"navigateLastPage":1,"firstPage":1,"lastPage":1}

调用接口测试数据源2：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmc3MDQ5ODc1NjI_size_16_color_FFFFFF_t_70 1]  
返回结果：

{"pageNum":1,"pageSize":2,"size":2,"startRow":1,"endRow":2,"total":2,"pages":1,"list":[{"id":1,"name":"test3","age":12,"birthday":"2019-03-28T02:31:24.000+0000"},{"id":2,"name":"test4","age":12,"birthday":"2019-03-28T02:31:39.000+0000"}],"prePage":0,"nextPage":0,"isFirstPage":true,"isLastPage":true,"hasPreviousPage":false,"hasNextPage":false,"navigatePages":8,"navigatepageNums":[1],"navigateFirstPage":1,"navigateLastPage":1,"firstPage":1,"lastPage":1}

[20190328103555752.jpg]: /images/20220227/1459355619e34764a596768a24e81688.png
[20190328103633399.jpg]: /images/20220227/657670c94f3e412aa7459843eae285cf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmc3MDQ5ODc1NjI_size_16_color_FFFFFF_t_70]: /images/20220227/e740c36bf98e430f8860768ed465408a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmc3MDQ5ODc1NjI_size_16_color_FFFFFF_t_70 1]: /images/20220227/c58773a30a774c94abca29d9f7cbf6e1.png