Springboot 项目配置多数据源

基础环境

java8、springboot2.2.13、mybatis、mysql5.x、oracle

项目配置

1.application.yml

spring:datasource:mysql1:username: abcpassword: 123456url: jdbc:mysql://127.0.0.1:3306/panda?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=falsedriver-class-name: com.mysql.cj.jdbc.Driver# 使用druid数据源type: com.alibaba.druid.pool.DruidDataSourceoracle1:url: jdbc:oracle:thin:@127.0.0.1:1521:oracleusername: defaultpassword: 123456driver-class-name: oracle.jdbc.driver.OracleDriver# 使用druid数据源type: com.alibaba.druid.pool.DruidDataSource

2.数据源配置
有三个注意点，其一是 @Primary 作用是标记哪个是主数据源（默认不指定数据源时会调用的数据源）；其二是 basePackages 配置的是实际mapper路径，不同数据源路径不能混淆；其三是 setMapperLocations 配置的路径要加上 “classpath:” 前缀，才能找到对应包下的 .xml 文件。

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 javax.sql.DataSource;@Configuration
public class AllDataSourceConfig {@Bean(name = "safeDrivingDataSource")@ConfigurationProperties(prefix = "spring.datasource.mysql1") // application.yml 中对应属性的前缀@Primarypublic DataSource safeDrivingDataSource() {return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();}@Bean(name = "emmpAppDataSource")@ConfigurationProperties(prefix = "spring.datasource.oracle1")public DataSource emmpappDataSource() {return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();}}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.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.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;@Configuration
@MapperScan(basePackages = "com.emmp.safedriving.dao.mapper.ds1", sqlSessionFactoryRef = "safeDrivingSqlSessionFactory")
public class SafeDrivingDataSourceConfig {@Bean("safeDrivingSqlSessionFactory")@Primarypublic SqlSessionFactory sqlSessionFactory(@Qualifier("safeDrivingDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();org.apache.ibatis.session.Configuration mybatisConfig = new org.apache.ibatis.session.Configuration();mybatisConfig.setLazyLoadingEnabled(true);mybatisConfig.setAggressiveLazyLoading(false);mybatisConfig.setMapUnderscoreToCamelCase(true);sqlSessionFactoryBean.setConfiguration(mybatisConfig);sqlSessionFactoryBean.setDataSource(dataSource);sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/emmp/safedriving/dao/mapper/ds1/xml/*.xml"));return sqlSessionFactoryBean.getObject();}@Bean("safeDrivingDataSourceTransactionManager")@Primarypublic DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("safeDrivingDataSource") DataSource dataSource){return new DataSourceTransactionManager(dataSource);}@Bean(name = "safeDrivingSqlSessionTemplate")@Primarypublic SqlSessionTemplate sqlSessionTemplate(@Qualifier("safeDrivingSqlSessionFactory") SqlSessionFactory sqlSessionFactory){return new SqlSessionTemplate(sqlSessionFactory);}}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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;@Configuration
@MapperScan(basePackages = "com.emmp.safedriving.dao.mapper.ds2", sqlSessionFactoryRef = "emmpAppSqlSessionFactory")
public class EmmpAppDataSourceConfig {@Bean("emmpAppSqlSessionFactory")public SqlSessionFactory sqlSessionFactory(@Qualifier("emmpAppDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();org.apache.ibatis.session.Configuration mybatisConfig = new org.apache.ibatis.session.Configuration();mybatisConfig.setLazyLoadingEnabled(true);mybatisConfig.setAggressiveLazyLoading(false);mybatisConfig.setMapUnderscoreToCamelCase(true);sqlSessionFactoryBean.setConfiguration(mybatisConfig);sqlSessionFactoryBean.setDataSource(dataSource);sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/emmp/safedriving/dao/mapper/ds2/xml/*.xml"));return sqlSessionFactoryBean.getObject();}@Bean("emmpAppDataSourceTransactionManager")public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("emmpAppDataSource") DataSource dataSource){return new DataSourceTransactionManager(dataSource);}@Bean(name = "emmpAppSqlSessionTemplate")public SqlSessionTemplate sqlSessionTemplate(@Qualifier("emmpAppSqlSessionFactory") SqlSessionFactory sqlSessionFactory){return new SqlSessionTemplate(sqlSessionFactory);}
}

3.启动类配置
不需要再添加 @MapperScan

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class,  // 排除默认数据源配置DataSourceTransactionManagerAutoConfiguration.class,  // 排除默认事务管理器MybatisAutoConfiguration.class  // 排除 MyBatis 默认配置}
)

4.mapper 定义示例

// 映射对象定义
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;import java.time.LocalDate;
import java.time.LocalDateTime;@Data
@Builder
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class DriverInfo {private Long id;private String driverName;}import org.apache.ibatis.annotations.Mapper;import java.util.List;@Mapper
public interface DriverInfoMapper {List<DriverInfo> queryAll();List<DriverInfo> queryByCellphone(String cellphone);}

对应 DriverInfoMapper.xml 配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.emmp.safedriving.dao.mapper.ds2.DriverInfoMapper"><resultMap id="entity" type="com.emmp.safedriving.dao.entity.DriverInfo"><result property="id" column="ID"/><result property="driverName" column="DRIVERNAME"/></resultMap><select id="queryAll" resultMap="entity">SELECT * FROM DRIVER_INFO</select><select id="queryByCellphone" resultMap="entity">SELECT * FROM DRIVER_INFOWHERE CELLPHONE = #{cellphone}ORDER BY ID DESC</select></mapper>