springboot配置多个数据源mybatis

springboot项目有时需要配置多个数据源

首先是配置文件需要增多一级配置源,注意这里的层级关系与数据库的命名,另外就是写配置文件冒号后面记得空格
配置文件:

 datasource:
      ##数据库1
      ai-website:
         jdbc-url: jdbc:mysql://***/company_web?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
         driver-class-name: com.mysql.cj.jdbc.Driver
         username: ***
         password: ***
      ##数据库2
      ai-program:
         jdbc-url: jdbc:mysql://***:4306/ctl_ai?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
         driver-class-name: com.mysql.cj.jdbc.Driver
         username: ***
         password: ***
      hikari:
         minimum-idle: 5
         maximum-pool-size: 15
         auto-commit: true
         idle-timeout: 600000
         pool-name: DatebookHikariCP
         max-lifetime: 28740000
         connection-timeout: 30000
         connection-test-query: SELECT 1

之后每一个数据源需要写一个配置类,配置数据源事务管理sqlsession工厂这些配置

DataSourceAiProgramConfig.java:

package com.common.config;


import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
/**
 * @author kongzhiqiang
 * @Description
 * @Date 2021/6/29 10:50
 */

@Configuration//注解到spring容器中
@MapperScan(basePackages = {"com.snimay.aiprogramdao.**.dao","com.snimay.aiprogramservice.**.dao"},sqlSessionFactoryRef = "programSqlSessionFactory")
public class DataSourceAiProgramConfig {
    /**
     * 返回program数据库的数据源
     * @return
     */
    @Bean(name="programSource")
    @ConfigurationProperties(prefix = "spring.datasource.ai-program")
    public DataSource dataSource(){
        return DataSourceBuilder.create().build();
    }

    /**
     * 返回program数据库的会话工厂
     * @param ds
     * @return
     * @throws Exception
     */
    @Bean(name = "programSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("programSource") DataSource ds) throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ds);
        return bean.getObject();
    }

    /**
     * 返回program数据库的会话模板
     * @param sessionFactory
     * @return
     * @throws Exception
     */
    @Bean(name = "programSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("programSqlSessionFactory") SqlSessionFactory sessionFactory) throws  Exception{
        return  new SqlSessionTemplate(sessionFactory);
    }

    /**
     * 返回program数据库的事务
     * @param ds
     * @return
     */
    @Bean(name = "programTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("programSource") DataSource ds){
        return new DataSourceTransactionManager(ds);
    }
}

DataSourceAiWebSiteConfig.java:

package com.common.config;


import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
/**
 * @author kongzhiqiang
 * @Description
 * @Date 2021/6/29 10:49
 */

@Configuration//注解到spring容器中
@MapperScan(basePackages = "com.snimay.**.dao",sqlSessionFactoryRef = "websiteSqlSessionFactory")
public class DataSourceAiWebSiteConfig {


    /**
     * 返回website数据库的数据源
     * @return
     */
    @Bean(name="websiteSource")
    @Primary//主数据源
    @ConfigurationProperties(prefix = "spring.datasource.ai-website")
    public DataSource dataSource(){
        return DataSourceBuilder.create().build();
    }

    /**
     * 返回website数据库的会话工厂
     * @param ds
     * @return
     * @throws Exception
     */
    @Bean(name = "websiteSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("websiteSource") DataSource ds) throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ds);
        return bean.getObject();
    }

    /**
     * 返回website数据库的会话模板
     * @param sessionFactory
     * @return
     * @throws Exception
     */
    @Bean(name = "websiteSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("websiteSqlSessionFactory") SqlSessionFactory sessionFactory) throws  Exception{
        return  new SqlSessionTemplate(sessionFactory);
    }

    /**
     * 返回website数据库的事务
     * @param ds
     * @return
     */
    @Bean(name = "websiteTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("websiteSource") DataSource ds){
        return new DataSourceTransactionManager(ds);
    }
}

这里可能需要注意的几个点:
1.主库@Primary的指定(指定哪个数据源为主库)

2.dao层service层的扫描包要根据需求配置,一般是分包扫描处理,否则将出现查询的数据源与想要的数据源不匹配的情况

3.事务的使用,项目使用过程中,默认@Tranceational的话是默认回滚主库,这时需要如果要使用特定的数据源事务需要指定例如:
//@Transactional(value = “programTransactionManager”)

另外如果觉得指定事务管理麻烦,其实有一种可以自动识别的方式,可以省去那步,这里不做赘述,这次仅介绍mybatis+springboot的配置,另外可能存在多个不同的库使用不同的orm的时候,但主要流程其实并不会偏移太多.

…