springboot多数据源实现

Posted on

一.多数据库实例集成

原理:

springboot支持与mybatis和mybatis-plus的快速自动化配置starter.

需要初始化定制DataSource,DataSourceTransactionManager,SqlSessionFactory三个对象的实例化.

基于扫描不同package下的mapper和mapper.xml创建上述的实例.

其他service层/mapper层跟单独数据源操作写法相同.只是要注意,这是通过package来隔离不同datasource,所以最好把不同的DataSource操作的service/mapper/entity放到各自的业务package中.

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
main/java
com.mybatis.spring.demo
	cms
		entity
		mapper
		service
	open
		entity
		mapper
		service
main/resources
	mapper
		cms
			xxxMapper.xml
		open
			yyyMapper.xml

单数据源实例化方式:只需要配置:

1
2
3
4
5
//application.properties
spring.datasource.url=xxx
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

而多数据源实例话方式需要配置各个数据源的jdbc连接

1
2
3
4
5
6
7
8
9
10
//application.properties
spring.datasource.cms.url=jdbc:mysql:xxx
spring.datasource.cms.username=xxx
spring.datasource.cms.password=xxx
spring.datasource.cms.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.open.url=jdbc:mysql:xxx
spring.datasource.open.username=xxx
spring.datasource.open.password=xxx
spring.datasource.open.driver-class-name=com.mysql.cj.jdbc.Driver

创建数据源配置类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

/**
 * MyBatis 在 SpringBoot 中动态多数据源配置
 * @author sheng
 * @date 2022-08-02
 * */
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.open")
@MapperScan(basePackages = "com.mybatis.spring.demo.open.mapper", sqlSessionFactoryRef = "openSqlSessionFactory")
@Setter
public class OpenDbConfig {

    @Autowired
    private DBProperties dbProperties;

    // 数据库配置息信息
    private String driverClassName;
    private String url;
    private String username;
    private String password;

    @Bean(name = "openDataSource")
    public DataSource unionDataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        // 设置数据源信息
        dataSource.setDriverClassName(driverClassName);
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        // 公共属性配置
        dataSource.setMinimumIdle(dbProperties.getMinimumIdle());
        dataSource.setIdleTimeout(dbProperties.getIdleTimeout());
        dataSource.setMaximumPoolSize(dbProperties.getMaximumPoolSize());
        dataSource.setAutoCommit(dbProperties.isAutoCommit());
        dataSource.setPoolName("open_" + dbProperties.getPoolName());
        dataSource.setMaxLifetime(dbProperties.getMaxLifetime());
        dataSource.setConnectionTimeout(dbProperties.getConnectionTimeout());
        dataSource.setConnectionTestQuery(dbProperties.getConnectionTestQuery());
        return dataSource;
    }

    /**
     * union 事务管理器
     *
     * @return
     */
    @Role(100)
    @Bean(name = CmsConst.TRANSACTION_OPEN)
    public DataSourceTransactionManager unionTransactionManager() {
        return new DataSourceTransactionManager(unionDataSource());
    }
    /**
     * unionDataSource Session工厂类
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "openSqlSessionFactory")
    public SqlSessionFactory unionSqlSessionFactory(@Qualifier("openDataSource") DataSource dataSource)
            throws Exception {
      //如果是与mybatis-plus集成就需要使用MybatisSqlSessionFactoryBean来代理实例化SqlSessionFactory
        MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        MybatisConfiguration config = new MybatisConfiguration();
        config.setMapUnderscoreToCamelCase(true);
        sessionFactory.setConfiguration(config);
        //如果不使用xml的方式配置mapper，则可以省去下面这行mapper location的配置。
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/open/*.xml"));
        return sessionFactory.getObject();
    }

同理,其他的数据源配置文件只需要修改

1
2
3
4
5
6
7
8
//配置参数前缀
@ConfigurationProperties(prefix = "spring.datasource.cms")
//扫描包路径
@MapperScan(basePackages = "com.mybatis.spring.demo.cms.mapper", sqlSessionFactoryRef = "cmsSqlSessionFactory")
//Xml文件路径

//如果不使用xml的方式配置mapper，则可以省去下面这行mapper location的配置。
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/cms/*.xml"));

pom.xml

1
2
3
4
5
6
7
8
9
10
<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>2.2.2</version>
</dependency>
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.5.1</version>
</dependency>

FAQ问题

1.Spring Boot集成MyBatis报错：Invalid bound statement (not found)…解决方案

无法识别mybatis-plus包的BaseMapper通用CURD方法

一.SqlSessionFactory不要使用原生的，请使用MybatisSqlSessionFactory

2.自定义 SQL 无法执行

1
2
3
4
5
application.properties
##mybatis-plus mapper xml 文件地址
mybatis-plus.mapper-locations= classpath*:/mapper/**/*Mapper.xml
##mybatis-plus type-aliases 文件地址
mybatis-plus.type-aliases-package= com.mybatis.spring.demo

参考资料