栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

使用SpringBoot当配置多个mysql连接对象

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

使用SpringBoot当配置多个mysql连接对象

 

目录

1.1Mybatis插件(推荐) 

1.2基于AOP实现(推荐)

1.3基于实现DataSource接口实现(不推荐)

1.4实现在同一model下多个Mybatis

1.1 实现多个库的事物回滚策略


1.1Mybatis插件(推荐) 

1.1使用mybatis过滤器来实现分表查询 (适用于  一表查   一表写 )

**记得写完之后将插件配置 @Bean

//使用mybatis插件过滤器来实现  两库 一表查一表写
@Intercepts({
        @Signature(type = Executor.class,method = "update",args = {MappedStatement.class,Object.class}),
                @Signature(type = Executor.class,method = "query",args = {MappedStatement.class,Object.class, RowBounds.class
                , ResultHandler.class})
})
public class DynamicDataSourcePlugin implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        //获取执行方法的所有参数  (update,query)
        //例如   int update(MappedStatement var1, Object var2) throws SQLException;
        //参数为 MappedStatement var1, Object var2
        Object[] args = invocation.getArgs();
        // MappedStatement 封装了所有的CRUD元素和SQL
        //执行方法参数第一个都是MappedStatement  这个方法封装了所有的CRUD元素和SQL
        MappedStatement ms = (MappedStatement) args[0];

       //如果类型为读  select类型
        if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)){

            DyNameDataSource.threadLocal.set("r");

        }else {

            DyNameDataSource.threadLocal.set("w");
        }
        //修改当前线程要选择的数据源key
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof Executor){
            return Plugin.wrap(target, this);
        }else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

配置两个DataSource 从springboot配置文件读取

@Configuration
public class DataSourceConfig {
    
    @Value("${master.dataSource.url}")
    private String masterUrl;
    @Value("${master.dataSource.driver}")
    private String masterDriver;
    @Value("${master.dataSource.passowrd}")
    private String masterUsername;
    @Value("${master.dataSource.username}")
    private String masterPassword;

    
    @Value("${slave.dataSource.url}")
    private String slaveUrl;
    @Value("${slave.dataSource.driver}")
    private String slaveDriver;
    @Value("${slave.dataSource.passowrd}")
    private String slaveUsername;
    @Value("${slave.dataSource.username}")
    private String slavePassword;

    @Bean(name = "masterDruidDataSource")
    public DruidDataSource masterDruidDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(masterDriver);
        dataSource.setUrl(masterUrl);
        dataSource.setPassword(masterPassword);
        dataSource.setUsername(masterUsername);
        return dataSource;
    }

    @Bean(name = "slaveDruidDataSource")
    public DruidDataSource slaveDruidDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(slaveDriver);
        dataSource.setUrl(slaveUrl);
        dataSource.setPassword(slavePassword);
        dataSource.setUsername(slaveUsername);
        return dataSource;
    }

// 将定义的mybatis插件放入到springboot容器当中
    @Bean
    public Interceptor interceptor(){
        return new DynamicDataSourcePlugin();
    }
}

application.properties配置文件 

spring.application.name=mybatis
# 主节点
master.dataSource.url=jdbc:mysql://192.168.1.10:3306/test
master.dataSource.driver=com.mysql.jdbc.Driver
master.dataSource.passowrd=root
master.dataSource.username=123456
#从节点
slave.dataSource.url=jdbc:mysql://192.168.1.14:3306/test
slave.dataSource.driver=com.mysql.jdbc.Driver
slave.dataSource.passowrd=root
slave.dataSource.username=123456

1.2基于AOP实现(推荐)

此方法适用于  两个不同数据库进行CRUD

        1.1导包



    org.springframework.boot
    spring-boot-starter-aop

        1.2创建注解类 

@Target({ElementType.METHOD,ElementType.TYPE})//注解可以使用在类和方法上
@Retention(RetentionPolicy.RUNTIME) //保留方式 SOURCE(此注解不会编译到Class文件上去)
                                    //CLASS(会保留在Class文件上但不会被JVM加载)
                                    //RUNTIME(会被JVM加载也会保留在Class文件上)
public @interface WR {
    String value() default "w";

}

         1.3 创建AOP切面类

@Component
@Aspect//AOP
public class DynamicDataSourceAspect {

//    使用前置通知
    //within(com.wjj.service.Impl.*)代表这个包之内 有wr注解的方法都会被增强
    //@annotation(wr)有注解  @WR
    @Before("within(com.wjj.service.Impl.*) && @annotation(wr) ")
    public void before(JoinPoint joinPoint,WR wr){
        String value = wr.value();
        DyNameDataSource.threadLocal.set(value);

        System.out.println("value = " + value);
    }
}

        1.4 配置俩 DataSource 
 

@Configuration
public class DataSourceConfig {
    
    @Value("${master.dataSource.url}")
    private String masterUrl;
    @Value("${master.dataSource.driver}")
    private String masterDriver;
    @Value("${master.dataSource.passowrd}")
    private String masterUsername;
    @Value("${master.dataSource.username}")
    private String masterPassword;

    
    @Value("${slave.dataSource.url}")
    private String slaveUrl;
    @Value("${slave.dataSource.driver}")
    private String slaveDriver;
    @Value("${slave.dataSource.passowrd}")
    private String slaveUsername;
    @Value("${slave.dataSource.username}")
    private String slavePassword;

    @Bean(name = "masterDruidDataSource")
    public DruidDataSource masterDruidDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(masterDriver);
        dataSource.setUrl(masterUrl);
        dataSource.setPassword(masterPassword);
        dataSource.setUsername(masterUsername);
        return dataSource;
    }

    @Bean(name = "slaveDruidDataSource")
    public DruidDataSource slaveDruidDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(slaveDriver);
        dataSource.setUrl(slaveUrl);
        dataSource.setPassword(slavePassword);
        dataSource.setUsername(slaveUsername);
        return dataSource;
    }

1.3基于实现DataSource接口实现(不推荐)

弊端太多如果springboot使用 DataSource当中的其他方法我们又不会重写所以不推荐使用

@Primary//优先级最高
@Configuration
public class DyNameDataSource implements DataSource {

    public static ThreadLocal threadLocal = new ThreadLocal<>();

    @Resource(name = "masterDruidDataSource")
    DataSource masterDruidDataSource;

    @Resource(name = "slaveDruidDataSource")
    DataSource slaveDruidDataSource;


//    决定springboot初始化使用那个  getConnection()  方法
    @Override
    public Connection getConnection() throws SQLException {
        if (threadLocal.get().equals("w")) {
            return masterDruidDataSource.getConnection();
        }
        return slaveDruidDataSource.getConnection();
    }

    @Override
    public Connection getConnection(String s, String s1) throws SQLException {
        return null;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter printWriter) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int i) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }

    @Override
    public  T unwrap(Class aClass) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class aClass) throws SQLException {
        return false;
    }

//   bean初始化执行的方法
   @Override
    public void afterPropertiesSet() throws Exception {
        threadLocal.set("w");
    }
}

1.4实现在同一model下多个Mybatis

          1.1创建mybtais1

@Configuration
//1.指定扫描的包
//指定那个SqlSessionFactory库  (主库)
@MapperScan(basePackages = "com.wjj.m.mapper",sqlSessionFactoryRef = "mSqlSessionFactory")
public class MMybatisConfig {

    
    @Value("${slave.dataSource.url}")
    private String slaveUrl;
    @Value("${slave.dataSource.driver}")
    private String slaveDriver;
    @Value("${slave.dataSource.passowrd}")
    private String slaveUsername;
    @Value("${slave.dataSource.username}")
    private String slavePassword;

    @Bean(name = "slaveDruidDataSource")
    public DruidDataSource slaveDruidDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(slaveDriver);
        dataSource.setUrl(slaveUrl);
        dataSource.setPassword(slavePassword);
        dataSource.setUsername(slaveUsername);
        return dataSource;
    }

    

    @Bean
    @Primary
    public SqlSessionFactory mSqlSessionFactory(@Qualifier("slaveDruidDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        return sqlSessionFactoryBean.getObject();
    }

    
    @Bean
    public DataSourceTransactionManager MDataSourceTransactionManager(@Qualifier("slaveDruidDataSource") DataSource dataSource){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }
}

        1.2 创建mybtais2

@Configuration
//1.指定扫描的包
//指定那个SqlSessionFactory库  (主库)
@MapperScan(basePackages = "com.wjj.w.mapper",sqlSessionFactoryRef = "wSqlSessionFactory")
public class WMybatisConfig {

    
    @Value("${master.dataSource.url}")
    private String masterUrl;
    @Value("${master.dataSource.driver}")
    private String masterDriver;
    @Value("${master.dataSource.passowrd}")
    private String masterUsername;
    @Value("${master.dataSource.username}")
    private String masterPassword;



    @Bean(name = "masterDruidDataSource")
    public DruidDataSource masterDruidDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(masterDriver);
        dataSource.setUrl(masterUrl);
        dataSource.setPassword(masterPassword);
        dataSource.setUsername(masterUsername);
        return dataSource;
    }

    @Bean
//    @Primary
    public SqlSessionFactory wSqlSessionFactory(@Qualifier("masterDruidDataSource")DataSource dataSource) throws Exception {
       final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

       sqlSessionFactoryBean.setDataSource(dataSource);

        return sqlSessionFactoryBean.getObject();
    }
    
    @Bean
    public DataSourceTransactionManager WDataSourceTransactionManager(@Qualifier("masterDruidDataSource") DataSource dataSource){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }

}

        1.3 Mybtais Mapper和xml文件的目录结构

 

1.1 实现多个库的事物回滚策略

        1.1注入事物工具bean

​​​​​​​​​​​​​​        1.1WMybatisConfig当中

@Bean(name = "WTransactionTemplate")
public TransactionTemplate WTransactionTemplate(@Qualifier("masterDruidDataSource") DataSource dataSource){
    return new TransactionTemplate(WDataSourceTransactionManager(dataSource));
}

        1.2MMybatisConfig当中

@Bean(name = "MTransactionTemplate")
public TransactionTemplate MTransactionTemplate(@Qualifier("slaveDruidDataSource") DataSource dataSource){
    return new TransactionTemplate(MDataSourceTransactionManager(dataSource));
}

         1.3ServiceImpl当中注入两个数据源

@Resource(name = "MTransactionTemplate")
TransactionTemplate MTransactionTemplate;

@Resource(name = "WTransactionTemplate")
TransactionTemplate WTransactionTemplate;

 ​​​​​​​        1.4 函数式多库事物回滚策略

@Override
public List getAllM() {
    //此处可以套很多层都没问题
    MTransactionTemplate.execute(status -> {
        WTransactionTemplate.execute(status1 -> {
            try {
                //库一操作
                getAll();
                //库二操作
                getAll2();
                int a = 1 / 0;
                return true;
            } catch (Exception e) {
                e.printStackTrace();
                //报错就回滚库一
                status.setRollbackOnly();
                //报错就回滚库二
                status1.setRollbackOnly();
                return false;
            }

        });
        return true;
    });
    
    return null;
}

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/939597.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号