springboot动态多数据源切换

2023-07-27 13:48:40

application-test.properties

#datasource -- mysql

multiple.datasource.master.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true

multiple.datasource.master.username=root

multiple.datasource.master.password=pypua

multiple.datasource.master.driverClassName=com.mysql.jdbc.Driver

multiple.datasource.master.InitialSize=10

multiple.datasource.master.MinIdle=10

multiple.datasource.master.MaxActive=100

multiple.datasource.slave.url=jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true

multiple.datasource.slave.username=root

multiple.datasource.slave.password=pypua

multiple.datasource.slave.driverClassName=com.mysql.jdbc.Driver

multiple.datasource.slave.InitialSize=10

multiple.datasource.slave.MinIdle=10

multiple.datasource.slave.MaxActive=100

创建核心枚举类型（多个数据源的名称）DataSourceKey

package com.pupeiyuan.core;

public enum DataSourceKey {

     DB_MASTER,

     DB_SLAVE1,

}

创建动态数据源DynamicRoutingDataSource，代表当前数据源

package com.pupeiyuan.core;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicRoutingDataSource extends AbstractRoutingDataSource{

    @Override

    protected Object determineCurrentLookupKey() {

        logger.info("当前数据源：{}"+ DynamicDataSourceContextHolder.get());

        return DynamicDataSourceContextHolder.get();

    }

}

创建动态数据源上下文取值类 DynamicDataSourceContextHolder.java

package com.pupeiyuan.core;

import org.apache.log4j.Logger;

public class DynamicDataSourceContextHolder {

      private static final Logger LOG = Logger.getLogger(DynamicDataSourceContextHolder.class);

      private static final ThreadLocal<DataSourceKey> currentDatesource = new ThreadLocal<>();

      /**

         * 清除当前数据源

         */

        public static void clear() {

            currentDatesource.remove();

        }

        /**

         * 获取当前使用的数据源

         *

         * @return 当前使用数据源的ID

         */

        public static DataSourceKey get() {

            return currentDatesource.get();

        }

        /**

         * 设置当前使用的数据源

         *

         * @param value 需要设置的数据源ID

         */

        public static void set(DataSourceKey value) {

            currentDatesource.set(value);

        }

        /**

         * 设置从从库读取数据

         * 采用简单生成随机数的方式切换不同的从库

         */

        /*public static void setSlave() {

            if (RandomUtils.nextInt(0, 2) > 0) {

                DynamicDataSourceContextHolder.set(DataSourceKey.DB_SLAVE2);

            } else {

                DynamicDataSourceContextHolder.set(DataSourceKey.DB_SLAVE1);

            }

        }*/

}

最主要的配置 mybatisConfig

package com.pupeiyuan.config;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.Map;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.SqlSessionTemplate;

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

import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.core.annotation.Order;

import org.springframework.core.io.Resource;

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import org.springframework.core.io.support.ResourcePatternResolver;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import org.springframework.transaction.PlatformTransactionManager;

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;

import com.github.pagehelper.PageHelper;

import com.pupeiyuan.core.DataSourceKey;

import com.pupeiyuan.core.DynamicRoutingDataSource;

import tk.mybatis.spring.annotation.MapperScan;

import tk.mybatis.spring.mapper.MapperScannerConfigurer;

@Configuration

@MapperScan("com.pupeiyuan.mapper")

public class MyBatisConfig {

      @Bean

        //此处的"multiple.datasource.master"需要你在application.properties中配置，详细信息看下面贴出的application.properties文件。

        @ConfigurationProperties(prefix = "multiple.datasource.master")

        public DataSource dbMaster() {

          DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();

          //配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙

            try {

                druidDataSource.setFilters("stat,wall,log4j");

            } catch (SQLException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

            // 通过connectProperties属性来打开mergeSql功能；慢SQL记录

            druidDataSource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000");

            //合并多个DruidDataSource的监控数据

            druidDataSource.setUseGlobalDataSourceStat(true);

            return druidDataSource;

        }

        @Bean

        @ConfigurationProperties(prefix = "multiple.datasource.slave")

        public DataSource dbSlave1() {

             DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();

              //配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙

                try {

                    druidDataSource.setFilters("stat,wall,log4j");

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

                // 通过connectProperties属性来打开mergeSql功能；慢SQL记录

                druidDataSource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000");

                //合并多个DruidDataSource的监控数据

                druidDataSource.setUseGlobalDataSourceStat(true);

                return druidDataSource;

        }

        /**

         * 核心动态数据源

         *

         * @return 数据源实例

         */

        @Bean

        public DataSource dynamicDataSource() {

            DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();

            dataSource.setDefaultTargetDataSource(dbMaster());

            Map<Object, Object> dataSourceMap = new HashMap<>(4);

            dataSourceMap.put(DataSourceKey.DB_MASTER, dbMaster());

            dataSourceMap.put(DataSourceKey.DB_SLAVE1, dbSlave1());

            dataSource.setTargetDataSources(dataSourceMap);

            return dataSource;

        }

        @Bean

        public SqlSessionFactory sqlSessionFactory() throws Exception {

            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

            sqlSessionFactoryBean.setDataSource(dynamicDataSource());

            //此处设置为了解决找不到mapper文件的问题

            //sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));

            return sqlSessionFactoryBean.getObject();

        }

        @Bean

        public SqlSessionTemplate sqlSessionTemplate() throws Exception {

            return new SqlSessionTemplate(sqlSessionFactory());

        }

        /**

         * 事务管理

         *

         * @return 事务管理实例

         */

        @Bean

        public PlatformTransactionManager platformTransactionManager() {

            return new DataSourceTransactionManager(dynamicDataSource());

        }

}

如果要配置自动切换数据源可以考虑AOP方式或者拦截器的方式进行配置，我这里采用手动切换

controller中手动切换

package com.pupeiyuan.controller;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

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

import org.springframework.context.annotation.Scope;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RestController;

import org.springframework.web.servlet.ModelAndView;

import com.github.pagehelper.PageHelper;

import com.pupeiyuan.bean.NhReportStatusHistory;

import com.pupeiyuan.common.controller.BaseController;

import com.pupeiyuan.core.DataSourceKey;

import com.pupeiyuan.core.DynamicDataSourceContextHolder;

import com.pupeiyuan.services.NhReportService;

/**

 * @author pypua

 * @date 2018年8月30日 上午9:21:20

 *

 */

@Controller

@RequestMapping("burket")

@Scope("prototype")

public class BurketController extends BaseController {

    //services层注入

    @Autowired NhReportService nhReportService;

    @RequestMapping(value = "/burketList", method = {RequestMethod.GET,RequestMethod.POST})

    public ModelAndView burketList(HttpServletRequest request,

            HttpServletResponse response

            ) throws Exception {

        System.out.println("hello,springboot");

       //参数容器

        Map<String, Object> params = new HashMap<String, Object>();

        PageHelper.startPage(1, 2);

        DynamicDataSourceContextHolder.set(DataSourceKey.DB_SLAVE1);

        List<NhReportStatusHistory> findList = nhReportService.findList(params);

        ModelAndView modelAndView = new ModelAndView();

        modelAndView.setViewName("burketList");

        modelAndView.addObject("list", findList);

        return modelAndView;

    }

}

-----------------------------------------------亲测有效------------------------------------------------------

码农公寓

相关文章