SpringBoot实现多数据源配置详解
2023-09-07 Springboot实战案例锦集
加入收藏
开发环境：JDK1.8+SpringBoot2.4.12+Oracle
这里我们假设要使用两个数据源分别为：master和slave。
- pom.xml 依赖包
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.Github.noraui</groupId>
<artifactId>ojdbc7</artifactId>
<version>12.1.0.2</version>
</dependency>
<dependency>
<groupId>org.Apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>org.MyBatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
</dependencies>
- Application.yml配置
server:
port: 50000
---
spring:
jpa:
hibernate:
ddlAuto: update
openInView: true
showSql: false
databasePlatform: org.hibernate.dialect.Oracle10gDialect
---
# 第一个数据源
master:
datasource:
driverClassName: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@localhost:1521/orcl
username: t0
password: t0
type: com.zaxxer.hikari.HikariDataSource
hikari:
minimumIdle: 10
maximumPoolSize: 200
autoCommit: true
idleTimeout: 30000
poolName: MasterDatabookHikariCP
maxLifetime: 1800000
connectionTimeout: 30000
connectionTestQuery: SELECT 1 FROM DUAL
# 第二个数据源
slave:
datasource:
driverClassName: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@localhost:1521/orcl
username: t1
password: t1
type: com.zaxxer.hikari.HikariDataSource
hikari:
minimumIdle: 10
maximumPoolSize: 200
autoCommit: true
idleTimeout: 30000
poolName: SlaveDatabookHikariCP
maxLifetime: 1800000
connectionTimeout: 30000
connectionTestQuery: SELECT 1 FROM DUAL
---
# mybatis 配置，分表对应到不同的包中
master:
mybatis:
config-location: classpath:/MyBatis-conf.xml
type-aliases-package: com.pack.domAIn #master数据源对应的包
mapper-locations:
- classpath:/com/pack/mapper/oracle/*.xml #master数据源对应mapper文件
slave:
mybatis:
config-location: classpath:/MyBatis-conf.xml
type-aliases-package: com.pack.slave.domain #slave数据源对应的包
mapper-locations:
- classpath:/com/pack/slave/mapper/oracle/*.xml #slave数据源对应mapper文件
---
# jpa相关的配置
master:
jpa:
repos: com.pack.base.repository #master数据源对应的包配置
domain: com.pack.domain #master对应的实体包
slave:
jpa:
repos: com.pack.slave.repository #salve数据源对应的包配置
domain: com.pack.slave.domain #slave对应的实体包
以上就是两个数据源对应相关的配置了，大家注意看里面的注释。接下来我们看mabatis和jpa对应的类相关的配置了，都是固定的配置。
- 数据源属性配置对应的JAVA类配置
BaseProperties类
public class BaseDataSourceProperties implements BeanClassLoaderAware, InitializingBean {
private ClassLoader classLoader;
private String name;
private boolean generateUniqueName;
private Class<? extends DataSource> type;
private String driverClassName;
private String url;
private String username;
private String password;
private String jndiName;
private DataSourceInitializationMode initializationMode = DataSourceInitializationMode.EMBEDDED;
private String platform = "all";
private List<String> schema;
private String schemaUsername;
private String schemaPassword;
private List<String> data;
private String dataUsername;
private String dataPassword;
private boolean continueOnError = false;
private String separator = ";";
private Charset sqlScriptEncoding;
private EmbeddedDatabaseConnection embeddedDatabaseConnection = EmbeddedDatabaseConnection.NONE;
private Xa xa = new Xa();
private String uniqueName;
@Override
public void setBeanClassLoader(ClassLoader classLoader) {
this.classLoader = classLoader;
}
@Override
public void afterPropertiesSet() throws Exception {
this.embeddedDatabaseConnection = EmbeddedDatabaseConnection
.get(this.classLoader);
}
public DataSourceBuilder<?> initializeDataSourceBuilder() {
return DataSourceBuilder.create(getClassLoader()).type(getType())
.driverClassName(determineDriverClassName()).url(determineUrl())
.username(determineUsername()).password(determinePassword());
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public boolean isGenerateUniqueName() {
return this.generateUniqueName;
}
public void setGenerateUniqueName(boolean generateUniqueName) {
this.generateUniqueName = generateUniqueName;
}
public Class<? extends DataSource> getType() {
return this.type;
}
public void setType(Class<? extends DataSource> type) {
this.type = type;
}
public String getDriverClassName() {
return this.driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String determineDriverClassName() {
if (StringUtils.hasText(this.driverClassName)) {
Assert.state(driverClassIsLoadable(),
() -> "Cannot load driver class: " + this.driverClassName);
return this.driverClassName;
}
String driverClassName = null;
if (StringUtils.hasText(this.url)) {
driverClassName = DatabaseDriver.fromJdbcUrl(this.url).getDriverClassName();
}
if (!StringUtils.hasText(driverClassName)) {
driverClassName = this.embeddedDatabaseConnection.getDriverClassName();
}
if (!StringUtils.hasText(driverClassName)) {
throw new DataSourceBeanCreationException(
"Failed to determine a suitable driver class", this,
this.embeddedDatabaseConnection);
}
return driverClassName;
}
private boolean driverClassIsLoadable() {
try {
ClassUtils.forName(this.driverClassName, null);
return true;
}
catch (UnsupportedClassVersionError ex) {
// Driver library has been compiled with a later JDK, propagate error
throw ex;
}
catch (Throwable ex) {
return false;
}
}
public String getUrl() {
return this.url;
}
public void setUrl(String url) {
this.url = url;
}
public String determineUrl() {
if (StringUtils.hasText(this.url)) {
return this.url;
}
String databaseName = determineDatabaseName();
String url = (databaseName != null)
? this.embeddedDatabaseConnection.getUrl(databaseName) : null;
if (!StringUtils.hasText(url)) {
throw new DataSourceBeanCreationException(
"Failed to determine suitable jdbc url", this,
this.embeddedDatabaseConnection);
}
return url;
}
public String determineDatabaseName() {
if (this.generateUniqueName) {
if (this.uniqueName == null) {
this.uniqueName = UUID.randomUUID().toString();
}
return this.uniqueName;
}
if (StringUtils.hasLength(this.name)) {
return this.name;
}
if (this.embeddedDatabaseConnection != EmbeddedDatabaseConnection.NONE) {
return "testdb";
}
return null;
}
public String getUsername() {
return this.username;
}
public void setUsername(String username) {
this.username = username;
}
public String determineUsername() {
if (StringUtils.hasText(this.username)) {
return this.username;
}
if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {
return "sa";
}
return null;
}
public String getPassword() {
return this.password;
}
public void setPassword(String password) {
this.password = password;
}
public String determinePassword() {
if (StringUtils.hasText(this.password)) {
return this.password;
}
if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {
return "";
}
return null;
}
public String getJndiName() {
return this.jndiName;
}
public void setJndiName(String jndiName) {
this.jndiName = jndiName;
}
public DataSourceInitializationMode getInitializationMode() {
return this.initializationMode;
}
public void setInitializationMode(DataSourceInitializationMode initializationMode) {
this.initializationMode = initializationMode;
}
public String getPlatform() {
return this.platform;
}
public void setPlatform(String platform) {
this.platform = platform;
}
public List<String> getSchema() {
return this.schema;
}
public void setSchema(List<String> schema) {
this.schema = schema;
}
public String getSchemaUsername() {
return this.schemaUsername;
}
public void setSchemaUsername(String schemaUsername) {
this.schemaUsername = schemaUsername;
}
public String getSchemaPassword() {
return this.schemaPassword;
}
public void setSchemaPassword(String schemaPassword) {
this.schemaPassword = schemaPassword;
}
public List<String> getData() {
return this.data;
}
public void setData(List<String> data) {
this.data = data;
}
public String getDataUsername() {
return this.dataUsername;
}
public void setDataUsername(String dataUsername) {
this.dataUsername = dataUsername;
}
public String getDataPassword() {
return this.dataPassword;
}
public void setDataPassword(String dataPassword) {
this.dataPassword = dataPassword;
}
public boolean isContinueOnError() {
return this.continueOnError;
}
public void setContinueOnError(boolean continueOnError) {
this.continueOnError = continueOnError;
}
public String getSeparator() {
return this.separator;
}
public void setSeparator(String separator) {
this.separator = separator;
}
public Charset getSqlScriptEncoding() {
return this.sqlScriptEncoding;
}
public void setSqlScriptEncoding(Charset sqlScriptEncoding) {
this.sqlScriptEncoding = sqlScriptEncoding;
}
public ClassLoader getClassLoader() {
return this.classLoader;
}
public Xa getXa() {
return this.xa;
}
public void setXa(Xa xa) {
this.xa = xa;
}
public static class Xa {
private String dataSourceClassName;
private Map<String, String> properties = new LinkedHashMap<>();
public String getDataSourceClassName() {
return this.dataSourceClassName;
}
public void setDataSourceClassName(String dataSourceClassName) {
this.dataSourceClassName = dataSourceClassName;
}
public Map<String, String> getProperties() {
return this.properties;
}
public void setProperties(Map<String, String> properties) {
this.properties = properties;
}
}
static class DataSourceBeanCreationException extends BeanCreationException {
private static final long serialVersionUID = 1L;
private final BaseDataSourceProperties properties;
private final EmbeddedDatabaseConnection connection;
DataSourceBeanCreationException(String message, BaseDataSourceProperties properties,
EmbeddedDatabaseConnection connection) {
super(message);
this.properties = properties;
this.connection = connection;
}
public BaseDataSourceProperties getProperties() {
return this.properties;
}
public EmbeddedDatabaseConnection getConnection() {
return this.connection;
}
}
}
mybatis对应的base属性文件
public class BaseMybatisProperties {
private String configLocation;
private String[] mapperLocations;
private String typeAliasesPackage;
private String typeHandlersPackage;
private boolean checkConfigLocation = false;
private ExecutorType executorType;
private Configuration configuration;
public String getConfigLocation() {
return this.configLocation;
}
public void setConfigLocation(String configLocation) {
this.configLocation = configLocation;
}
@Deprecated
public String getConfig() {
return this.configLocation;
}
@Deprecated
public void setConfig(String config) {
this.configLocation = config;
}
public String[] getMapperLocations() {
return this.mapperLocations;
}
public void setMapperLocations(String[] mapperLocations) {
this.mapperLocations = mapperLocations;
}
public String getTypeHandlersPackage() {
return this.typeHandlersPackage;
}
public void setTypeHandlersPackage(String typeHandlersPackage) {
this.typeHandlersPackage = typeHandlersPackage;
}
public String getTypeAliasesPackage() {
return this.typeAliasesPackage;
}
public void setTypeAliasesPackage(String typeAliasesPackage) {
this.typeAliasesPackage = typeAliasesPackage;
}
public boolean isCheckConfigLocation() {
return this.checkConfigLocation;
}
public void setCheckConfigLocation(boolean checkConfigLocation) {
this.checkConfigLocation = checkConfigLocation;
}
public ExecutorType getExecutorType() {
return this.executorType;
}
public void setExecutorType(ExecutorType executorType) {
this.executorType = executorType;
}
public Configuration getConfiguration() {
return configuration;
}
public void setConfiguration(Configuration configuration) {
this.configuration = configuration;
}
public Resource[] resolveMapperLocations() {
List<Resource> resources = new ArrayList<Resource>();
if (this.mapperLocations != null) {
for (String mapperLocation : this.mapperLocations) {
Resource[] mappers;
try {
mappers = new PathMatchingResourcePatternResolver().getResources(mapperLocation);
resources.addAll(Arrays.asList(mappers));
} catch (IOException e) {
}
}
}
Resource[] mapperLocations = new Resource[resources.size()];
mapperLocations = resources.toArray(mapperLocations);
return mapperLocations;
}
}
因为我们使用的是Hikari数据源，所以这里我是直接copy默认系统Hikari的属性文件。
也就是这个文件：org.springframework.boot.autoconfigure.jdbc.DataSourceProperties 为啥我不直接继承这个类而是在自己的项目中新建这么一个类，是因为我发现这个类有这个注解
@ConfigurationProperties(prefix = "spring.datasource")
怕的是它的这个注解会覆盖我接下来两个类的注解（我主要是懒得测试，所以直接copy一份无所谓了）。
接下来看看具体master和slave两个数据源的属性文件：
- Properties（jpa和mybatis）文件
@Component
@ConfigurationProperties(prefix = "master.datasource")
public class MasterDataSourceProperties extends BaseDataSourceProperties {
}
@Component
@ConfigurationProperties(prefix = "slave.datasource")
public class SlaveDataSourceProperties extends BaseDataSourceProperties {
}
@Component
@ConfigurationProperties(prefix = "master.mybatis")
public class MasterMybatisProperties extends BaseMybatisProperties {
}
@Component
@ConfigurationProperties(prefix = "slave.mybatis")
public class SlaveMybatisProperties extends BaseMybatisProperties {
}
接下来是数据源的配置了。
- 数据源配置类
@Configuration
public class HikariDataSourceConfig {
@Bean
@Primary
public HikariDataSource masterDataSource(MasterDataSourceProperties properties) {
HikariDataSource dataSource = createDataSource(properties,
HikariDataSource.class);
if (StringUtils.hasText(properties.getName())) {
dataSource.setPoolName(properties.getName());
}
return dataSource;
}
@Bean
public HikariDataSource slaveDataSource(SlaveDataSourceProperties properties) {
HikariDataSource dataSource = createDataSource(properties,
HikariDataSource.class);
if (StringUtils.hasText(properties.getName())) {
dataSource.setPoolName(properties.getName());
}
return dataSource;
}
@SuppressWarnings("unchecked")
protected static <T> T createDataSource(BaseDataSourceProperties properties,
Class<? extends DataSource> type) {
return (T) properties.initializeDataSourceBuilder().type(type).build();
}
}
因为我们配置的是多个数据源所有其中一个数据源必须加入这个注解@Primary
接下来是jpa的EntityManagerFactory工厂的配置了
- EntityManagerFactory配置
public class EntityManagerFactoryConfig {
@Configuration
@EnableJpaRepositories(basePackages = {
"${master.jpa.repos}" }, entityManagerFactoryRef = "masterEntityManagerFactory", transactionManagerRef = "masterJPATransactionManager")
static class MasterEntityManagerFactory {
@Resource(name = "masterDataSource")
private DataSource masterDataSource;
@Value("${master.jpa.domain}")
private String masterDomainPkg;
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean masterEntityManagerFactory(EntityManagerFactoryBuilder builder) {
Map<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", "update");
properties.put("hibernate.id.new_generator_mappings", true);
properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName()) ;
return builder.dataSource(masterDataSource).packages(masterDomainPkg).persistenceUnit("master")
.properties(properties).build();
}
@Bean
@Primary
public PlatformTransactionManager masterJPATransactionManager(EntityManagerFactoryBuilder builder) {
JpaTransactionManager tm = new JpaTransactionManager(masterEntityManagerFactory(builder).getObject());
return tm;
}
}
@Configuration
@EnableJpaRepositories(basePackages = {
"${slave.jpa.repos}" }, entityManagerFactoryRef = "slaveEntityManagerFactory", transactionManagerRef = "slaveJPATransactionManager")
@ConditionalOnProperty(name = "multiple.ds.enabled", havingValue = "true")
static class SlaveEntityManagerFactory {
@Resource(name = "slaveDataSource")
private DataSource slaveDataSource;
@Value("${slave.jpa.domain}")
private String slaveDomainPkg;
@Bean
public LocalContainerEntityManagerFactoryBean slaveEntityManagerFactory(EntityManagerFactoryBuilder builder) {
Map<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", "update");
properties.put("hibernate.id.new_generator_mappings", true);
properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName()) ;
return builder.dataSource(slaveDataSource).packages(slaveDomainPkg).persistenceUnit("slave")
.properties(properties).build();
}
@Bean
public PlatformTransactionManager slaveJPATransactionManager(EntityManagerFactoryBuilder builder) {
JpaTransactionManager tm = new JpaTransactionManager(slaveEntityManagerFactory(builder).getObject());
return tm;
}
}
}
- mybatis SqlSessionFactory工厂配置
public class SqlSessionFactoryConfig {
@Configuration
static class MasterSqlSessionFactory {
@Resource
private MasterMybatisProperties properties;
@Autowired(required = false)
private Interceptor[] interceptors;
@Autowired
private ResourceLoader resourceLoader = new DefaultResourceLoader();
@Autowired(required = false)
private DatabaseIdProvider databaseIdProvider;
@Bean
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(dataSource);
factory.setVfs(SpringBootVFS.class);
if (StringUtils.hasText(this.properties.getConfigLocation())) {
factory.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfigLocation()));
}
factory.setConfiguration(properties.getConfiguration());
if (!ObjectUtils.isEmpty(this.interceptors)) {
factory.setPlugins(this.interceptors);
}
if (this.databaseIdProvider != null) {
factory.setDatabaseIdProvider(this.databaseIdProvider);
}
if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {
factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());
}
if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) {
factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());
}
if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) {
factory.setMapperLocations(this.properties.resolveMapperLocations());
}
return factory.getObject();
}
@Bean
public DataSourceTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean
public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory")SqlSessionFactory sqlSessionFactory) {
ExecutorType executorType = this.properties.getExecutorType();
if (executorType != null) {
return new SqlSessionTemplate(sqlSessionFactory, executorType);
} else {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
}
@Configuration
@ConditionalOnProperty(name = "multiple.ds.enabled", havingValue = "true")
static class SlaveSqlSessionFactory {
@Resource
private SlaveMybatisProperties properties;
@Autowired(required = false)
private Interceptor[] interceptors;
@Autowired
private ResourceLoader resourceLoader = new DefaultResourceLoader();
@Autowired(required = false)
private DatabaseIdProvider databaseIdProvider;
@Bean
public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(dataSource);
factory.setVfs(SpringBootVFS.class);
if (StringUtils.hasText(this.properties.getConfigLocation())) { factory.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfigLocation()));
}
factory.setConfiguration(properties.getConfiguration());
if (!ObjectUtils.isEmpty(this.interceptors)) {
factory.setPlugins(this.interceptors);
}
if (this.databaseIdProvider != null) {
factory.setDatabaseIdProvider(this.databaseIdProvider);
}
if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {
factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());
}
if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) { factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());
}
if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) {
factory.setMapperLocations(this.properties.resolveMapperLocations());
}
return factory.getObject();
}
@Bean
public DataSourceTransactionManager slaveTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean
public SqlSessionTemplate slaveSqlSessionTemplate(@Qualifier("slaveSqlSessionFactory")SqlSessionFactory sqlSessionFactory) {
ExecutorType executorType = this.properties.getExecutorType();
if (executorType != null) {
return new SqlSessionTemplate(sqlSessionFactory, executorType);
} else {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
}
}
接下来还需要配置mapper相关的配置
- mapper配置
public class MapperScanConfig {
@Configuration
@MapperScan(basePackages = {"com.pack.base.mapper"}, sqlSessionTemplateRef = "masterSqlSessionTemplate")
static class MasterMapper {
}
@Configuration
@MapperScan(basePackages = {"com.pack.slave.mapper"}, sqlSessionTemplateRef = "slaveSqlSessionTemplate")
@ConditionalOnProperty(name = "multiple.ds.enabled", havingValue = "true")
static class SlaveMapper {
}
}
到这里我们所有的相关配置就完成了，接下来我们只需建立对应的包即可。
图片
MyBatis-conf.xml配置文件内容如下：
图片
测试：
建立com.pack.domain包，然后新建类Users.java
@Entity
@Table(name = "T_USERS")
@Data
public class Users {
@Id
private Long id;
private String username ;
private String password ;
private String realName ;
private String phone ;
private String idNo ;
@Column(length=4000)
private String authority ;
@Column(columnDefinitinotallow="int default 0")
private Integer status = 0 ;
}
建立包com.pack.slave.domain，然后新建类
@Entity
@Table(name = "T_PERSON")
@Data
public class Person{
@Id
private Long id;
private String name ;
private String email ;
}
启动服务器分别在不同的用户下查看表是否建立，如果都建立了就表示成功。
完毕！！！
