Spring Boot 基于Druid连接池的多数据源 + 自定义事务管理器

spring:   datasource:     driver-class-name: com.mysql.cj.jdbc.Driver     type: com.alibaba.druid.pool.DruidDataSource     url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&tinyInt1isBit=false     username: root     password: root   datasource2:     driver-class-name: com.mysql.cj.jdbc.Driver     type: com.alibaba.druid.pool.DruidDataSource     url: jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&tinyInt1isBit=false     username: root     password: root 

这两个数据源可以是异构数据库(未实测),注意调整相关配置

package com.xxx.config;

import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

/** * Description: * 阿里巴巴数据库库连接池配置类 * 
 * date: 2021/11/26 15:20 * * @author Arvin Lee * @version 1.0 */
@Configuration
@Slf4j
public class DruidConfig { 
        
    @Value("${spring.datasource.url:#{null}}")
    private String dbUrl;
    @Value("${spring.datasource.username: #{null}}")
    private String username;
    @Value("${spring.datasource.password:#{null}}")
    private String password;
    @Value("${spring.datasource.driverClassName:#{null}}")
    private String driverClassName;

    @Value("${spring.datasource2.url:#{null}}")
    private String dbUrl2;
    @Value("${spring.datasource2.username: #{null}}")
    private String username2;
    @Value("${spring.datasource2.password:#{null}}")
    private String password2;
    @Value("${spring.datasource2.driverClassName:#{null}}")
    private String driverClassName2;

    @Bean
    @Primary
    public DataSource dataSource() { 
        
        return CommonFunctionInConfig.getDataSource(dbUrl,
                username,
                password,
                driverClassName,
                statFilter(), wallFilter());
    }

    @Bean
    public DataSource dataSource2() { 
        
        return CommonFunctionInConfig.getDataSource(dbUrl2, username2, password2, driverClassName2,
                statFilter(), wallFilter());
    }

    @Bean
    public ServletRegistrationBean druidServlet() { 
        
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        //控制台管理用户，加入下面2行 进入druid后台就需要登录
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "admin");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() { 
        
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }

    @Bean
    public StatFilter statFilter() { 
        
        StatFilter statFilter = new StatFilter();
        //slowSqlMillis用来配置SQL慢的标准，执行时间超过slowSqlMillis的就是慢。
        statFilter.setLogSlowSql(true);
        //SQL合并配置
        statFilter.setMergeSql(true);
        //slowSqlMillis的缺省值为3000，也就是3秒。
        statFilter.setSlowSqlMillis(1000);
        return statFilter;
    }

    @Bean
    public WallFilter wallFilter() { 
        
        WallFilter wallFilter = new WallFilter();
        //允许执行多条SQL
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        wallFilter.setConfig(config);
        return wallFilter;
    }
}

package com.xxx.config;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallFilter;

import java.util.ArrayList;
import java.util.List;

/** * Description: * 配置包下的通用方法 * 
 * date: 2021/11/29 15:06 * * @author Arvin Lee * @version 1.0 */
public class CommonFunctionInConfig { 
        

    /** * getDataSource * 获取数据源 * * @param dbUrl 数据库连接地址 * @param username 用户名 * @param password 密码 * @param driverClassName 驱动名 * @param statFilter - * @param wallFilter - * @return com.alibaba.druid.pool.DruidDataSource * @author Arvin Lee * @date 2021/12/7 9:28 **/
    protected static DruidDataSource getDataSource(String dbUrl,
                                                   String username,
                                                   String password,
                                                   String driverClassName,
                                                   StatFilter statFilter,
                                                   WallFilter wallFilter) { 
        
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        List<Filter> filters = new ArrayList<>();
        filters.add(statFilter);
        filters.add(wallFilter);
        datasource.setProxyFilters(filters);
        return datasource;
    }
}

连接池其他配置参数可以在这里统一调整

package com.xxx.config;

import com.xxx.BaseMapper;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import tk.mybatis.spring.annotation.MapperScan;

import javax.annotation.Resource;
import javax.sql.DataSource;

/** * Description: * mybatis数据源配置 * 
 * date: 2021/11/26 15:39 * * @author Arvin Lee * @version 1.0 */
@Configuration
@MapperScan(basePackages = "com.xxx.dao.datasource1", markerInterface = BaseMapper.class, sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig { 
        
    @Resource
    @Qualifier("dataSource")
    private DataSource ds;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception { 
        
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(ds);
        //指定mapper xml目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factoryBean.setMapperLocations(resolver.getResources("classpath:mapper/datasource1/*.xml"));
        return factoryBean.getObject();

    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate() throws Exception { 
        
        // 使用上面配置的Factory
        return new SqlSessionTemplate(sqlSessionFactory());
    }

    /** * masterTransactionManager * 关于事务管理器，不管是JPA还是JDBC等都实现自接口 PlatformTransactionManager * 如果你添加的是 spring-boot-starter-jdbc 依赖，框架会默认注入 DataSourceTransactionManager 实例。 * 在Spring容器中，我们手工注解@Bean 将被优先加载，框架不会重新实例化其他的 PlatformTransactionManager 实现类。 * * @return org.springframework.jdbc.datasource.DataSourceTransactionManager * @author Arvin Lee * @date 2021/11/26 15:42 **/
    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() { 
        
        //MyBatis自动参与到spring事务管理中，无需额外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源
        // 与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。
        return new DataSourceTransactionManager(ds);

    }
}

1. 此处需要注意一下，代码经过脱敏，所以MapperScan 注解的 basePackages 路径com.xxx.dao.datasource1 大意如下
   –com.xxx
   ------dao (存放dao层接口)
   ----------datasource1(与第一个数据源映射的dao)
   ----------datasource2(与第二个数据源映射的dao)
2. markerInterface 对应的BaseMapper.class是我自定义的Mybatis的BaseMapper，如果没有可以不写。
3. sqlSessionFactoryRef 参考的sql会话工厂，这个与返回SqlSessionFactory的方法的方法名对应
4. @Qualifier(“dataSource”) 这是给DataSource起别名，因为是多数据源，命名没有特殊要求。
5. classpath:mapper/datasource1/*.xml 这个是mapper.xml映射路径，路径格式参考dao
   示例：
   –reosurce
   ------mapper
   ----------datasource1
   ----------datasource2

package com.xxx.config;

import com.xxx.common.BaseMapper;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import tk.mybatis.spring.annotation.MapperScan;

import javax.annotation.Resource;
import javax.sql.DataSource;

/** * Description: * mybatis数据源2配置 * 
 * date: 2021/11/26 16:02 * * @author Arvin Lee * @version 1.0 */
@Configuration
@MapperScan(basePackages = "com.xxx.dao.datasource2", markerInterface = BaseMapper.class, sqlSessionFactoryRef = "sqlSessionFactory2")
public class DataSource2Config { 
        
    @Resource
    @Qualifier("dataSource2")
    private DataSource ds;

    @Bean
    public SqlSessionFactory sqlSessionFactory2() throws Exception { 
        
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(ds);
        //指定mapper xml目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factoryBean.setMapperLocations(resolver.getResources("classpath:mapper/datasource2/*.xml"));
        return factoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate2() throws Exception { 
        
        // 使用上面配置的Factory
        return new SqlSessionTemplate(sqlSessionFactory2());
    }

    /** * masterTransactionManager * 关于事务管理器，不管是JPA还是JDBC等都实现自接口 PlatformTransactionManager * 如果你添加的是 spring-boot-starter-jdbc 依赖，框架会默认注入 DataSourceTransactionManager 实例。 * 在Spring容器中，我们手工注解@Bean 将被优先加载，框架不会重新实例化其他的 PlatformTransactionManager 实现类。 * * @return org.springframework.jdbc.datasource.DataSourceTransactionManager * @author Arvin Lee * @date 2021/11/26 15:42 **/
    @Bean(name = "transactionManager2")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() { 
        
        //MyBatis自动参与到spring事务管理中，无需额外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源
        // 与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。
        return new DataSourceTransactionManager(ds);

    }
}

1. 格式与第一个一致，只不过dao、mapper.xml、datasource、SQLSessionFactory不同，需要加个2
2. 如果3个数据源，以此类推，再加个DataSourceConfig 同时也要修改DruidConfig