springboot集成Mybatis配置多数据源

springboot配置多数据源有好几种方式

1.application.properties配置

## 端口
server.port=8080

# 数据库访问配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.first-url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.datasource.first-username=root
spring.datasource.first-password=123456

spring.datasource.historyUrl=jdbc:mysql://localhost:3306/test_history?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.datasource.historyUsername=root
spring.datasource.historyPassword=123456



# 下面为连接池的补充设置,应用到上面所有数据源中
spring.datasource.druid.initialSize=5
spring.datasource.druid.minIdle=5
spring.datasource.druid.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.druid.maxWait=60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=false
spring.datasource.druid.testOnReturn=false
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
spring.datasource.druid.filter.stat.enabled=true

2.创建两个数据源配置DataSourceConfiguration和HistoryDataSourceConfiguration

@Configuration
@MapperScan(basePackages = "com.example.demo.dao", sqlSessionTemplateRef  = "sqlSessionTemplate")
public class DataSourceConfiguration {
    public final static org.slf4j.Logger logger = LoggerFactory.getLogger(DataSourceConfiguration.class);
    
    @Value("${spring.datasource.first-url}")
    private String url;
    
    @Value("${spring.datasource.first-username}")
    private String username;
    
    @Value("${spring.datasource.first-password}")
    private String password;
    
    @Value("${spring.datasource.historyUrl}")
    private String historyUrl;
    
    @Value("${spring.datasource.historyUsername}")
    private String historyUsername;
    
    @Value("${spring.datasource.historyPassword}")
    private String historyPassword;
    
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    
    @Value("${spring.datasource.druid.initialSize}")
    private int initialSize;
    
    @Value("${spring.datasource.druid.minIdle}")
    private int minIdle;
    
    @Value("${spring.datasource.druid.maxActive}")
    private int maxActive;
    
    @Value("${spring.datasource.druid.maxWait}")
    private int maxWait;
    
    @Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    
    @Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    
    @Value("${spring.datasource.druid.validationQuery}")
    private String validationQuery;
    
    @Value("${spring.datasource.druid.testWhileIdle}")
    private boolean testWhileIdle;
    
    @Value("${spring.datasource.druid.testOnBorrow}")
    private boolean testOnBorrow;
    
    @Value("${spring.datasource.druid.testOnReturn}")
    private boolean testOnReturn;
    
    @Bean(name = "dataSource") 
    @Primary
    public DataSource dataSource(){  
        logger.info("初始化数据库连接池");
        DruidDataSource datasource = new DruidDataSource();  
        datasource.setUrl(url);  
        datasource.setUsername(username);  
        datasource.setPassword(password);  
        datasource.setDriverClassName(driverClassName);  
  
        //configuration  
        datasource.setInitialSize(initialSize);  
        datasource.setMinIdle(minIdle);  
        datasource.setMaxActive(maxActive);  
        datasource.setMaxWait(maxWait);  
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);  
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);  
        datasource.setValidationQuery(validationQuery);  
        datasource.setTestWhileIdle(testWhileIdle);  
        datasource.setTestOnBorrow(testOnReturn);  
        datasource.setTestOnReturn(testOnReturn);  
        logger.info("初始化数据库连接池完成");
        return datasource;  
    } 

    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource")DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
    
    @Bean(name = "sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        bean.setTypeAliasesPackage("com.example.demo.model");
        return bean.getObject();
    }
    
    @Bean(name = "sqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

@Configuration
@MapperScan(basePackages = "com.example.demo.historyDao", sqlSessionTemplateRef  = "historySqlSessionTemplate")
public class HistoryDataSourceConfiguration {
    public final static org.slf4j.Logger logger = LoggerFactory.getLogger(HistoryDataSourceConfiguration.class);
    
    @Value("${spring.datasource.first-url}")
    private String url;
    
    @Value("${spring.datasource.first-username}")
    private String username;
    
    @Value("${spring.datasource.first-password}")
    private String password;
    
    @Value("${spring.datasource.historyUrl}")
    private String historyUrl;
    
    @Value("${spring.datasource.historyUsername}")
    private String historyUsername;
    
    @Value("${spring.datasource.historyPassword}")
    private String historyPassword;
    
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    
    @Value("${spring.datasource.druid.initialSize}")
    private int initialSize;
    
    @Value("${spring.datasource.druid.minIdle}")
    private int minIdle;
    
    @Value("${spring.datasource.druid.maxActive}")
    private int maxActive;
    
    @Value("${spring.datasource.druid.maxWait}")
    private int maxWait;
    
    @Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    
    @Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    
    @Value("${spring.datasource.druid.validationQuery}")
    private String validationQuery;
    
    @Value("${spring.datasource.druid.testWhileIdle}")
    private boolean testWhileIdle;
    
    @Value("${spring.datasource.druid.testOnBorrow}")
    private boolean testOnBorrow;
    
    @Value("${spring.datasource.druid.testOnReturn}")
    private boolean testOnReturn;
    
    @Bean(name = "historyDataSource")     
    public DataSource historyDataSource(){  
        logger.info("初始化历史库数据库连接池");
        DruidDataSource datasource = new DruidDataSource();  
        datasource.setUrl(historyUrl);  
        datasource.setUsername(historyUsername);  
        datasource.setPassword(historyPassword);  
        datasource.setDriverClassName(driverClassName);  
  
        //configuration  
        datasource.setInitialSize(initialSize);  
        datasource.setMinIdle(minIdle);  
        datasource.setMaxActive(maxActive);  
        datasource.setMaxWait(maxWait);  
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);  
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);  
        datasource.setValidationQuery(validationQuery);  
        datasource.setTestWhileIdle(testWhileIdle);  
        datasource.setTestOnBorrow(testOnReturn);  
        datasource.setTestOnReturn(testOnReturn);  
        logger.info("初始化历史库数据库连接池完成");
        return datasource;  
    } 

    @Bean(name = "historyTransactionManager")
    public DataSourceTransactionManager historyTransactionManager(@Qualifier("historyDataSource")DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
    
    
    @Bean(name = "historySqlSessionFactory")
    public SqlSessionFactory historySqlSessionFactory(@Qualifier("historyDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        bean.setTypeAliasesPackage("com.example.demo.model");
        return bean.getObject();
    }
    
    @Bean(name = "historySqlSessionTemplate")
    public SqlSessionTemplate historySqlSessionTemplate(@Qualifier("historySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

3.建两个不同的DAO　　　　

package com.example.demo.dao;

import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import com.example.demo.model.User;

@Mapper
public interface UserDao {
    List<User> findAll();
}

package com.example.demo.historyDao;

import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import com.example.demo.model.User;

@Mapper
public interface UserHistoryDao {
    List<User> findAll();
}

4.service

@Service
public class UserService {

    @Autowired
    private UserDao userDao;
    
    @Autowired
    private UserHistoryDao userHistoryDao;
    
    public List<User> findList(){
        return userDao.findAll();
    }
    
    @Transactional
    public List<User> findHistoryList(){
        return userHistoryDao.findAll();
    }
    
}

5.测试代码

@RestController
public class TestController {

    @Autowired
    private UserService userService;
    
    @RequestMapping("/query")
    public List<User> query(HttpSession session) {
        
        return userService.findList();
    }
    
    @RequestMapping("/queryHistory")
    public List<User> queryHistory(HttpSession session) {
        return userService.findHistoryList();
    }
}

6.输入http://127.0.0.1:8080/query 和http://127.0.0.1:8080/queryHistory，会发现两个请求访问了不同的数据库

 

源代码地址:https://gitee.com/zhuyuehua/springboot-multi-database