springboot 多数据源配置

程序员文章站 2022-04-27 22:23:55

...

本文主要讲解在springboot项目中使用多数据源操作，并结合阿里巴巴数据源连接池监控数据库。

一、pom文件配置

        <dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-redis</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-security</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.security.oauth</groupId>
			<artifactId>spring-security-oauth2</artifactId>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.1.36</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.8</version>
		</dependency>
		<dependency>
			<groupId>com.github.wxpay</groupId>
			<artifactId>wxpay-sdk</artifactId>
			<version>0.0.3</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>

二、druid.properties文件配置

#数据源1 使用JPA
spring.datasource.primary.driverClassName=com.mysql.jdbc.Driver
spring.datasource.primary.url=jdbc:mysql://127.0.0.1:3306/api_oauth?useUnicode=true&serverTimezone=GMT%2B8&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.initialSize=5
spring.datasource.primary.minIdle=1
spring.datasource.primary.maxActive=50
spring.datasource.primary.maxWait=60000
spring.datasource.primary.timeBetweenEvictionRunsMillis=60000
spring.datasource.primary.minEvictableIdleTimeMillis=300000
spring.datasource.primary.validationQuery=SELECT 1 FROM DUAL
spring.datasource.primary.testWhileIdle=true
spring.datasource.primary.testOnBorrow=false
spring.datasource.primary.testOnReturn=false
spring.datasource.primary.poolPreparedStatements=false
spring.datasource.primary.filters=stat,wall
spring.datasource.primary.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.primary.useGlobalDataSourceStat=true

#数据源2 使用MyBatis
spring.datasource.secondary.driverClassName=com.mysql.jdbc.Driver
spring.datasource.secondary.url=jdbc:mysql://127.0.0.1:3306/tissue?useUnicode=true&serverTimezone=GMT%2B8&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.initialSize=5
spring.datasource.secondary.minIdle=1
spring.datasource.secondary.maxActive=50
spring.datasource.secondary.maxWait=60000
spring.datasource.secondary.timeBetweenEvictionRunsMillis=60000
spring.datasource.secondary.minEvictableIdleTimeMillis=300000
spring.datasource.secondary.validationQuery=SELECT 1 FROM DUAL
spring.datasource.secondary.testWhileIdle=true
spring.datasource.secondary.testOnBorrow=false
spring.datasource.secondary.testOnReturn=false
spring.datasource.secondary.poolPreparedStatements=false
spring.datasource.secondary.filters=stat,wall
spring.datasource.secondary.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.secondary.useGlobalDataSourceStat=true

三、DruidConfig数据源类配置

package com.leshare.api.main.config.druid;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;

@Configuration
@PropertySource(value = "classpath:druid.properties")
public class DruidConfig {

	@Primary
	@Qualifier("primaryDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.primary")
	@Bean(name = "primaryDataSource", destroyMethod = "close", initMethod = "init")
	public DataSource primaryDruid() {
		return new DruidDataSource();
	}

	@Qualifier("secondaryDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.secondary")
	@Bean(name = "secondaryDataSource", destroyMethod = "close", initMethod = "init")
	public DataSource secondaryDruid() {
		return new DruidDataSource();
	}

	@Bean
	public ServletRegistrationBean druidStatViewServlet() {
		ServletRegistrationBean registrationBean = new ServletRegistrationBean();
		registrationBean.setServlet(new StatViewServlet());
		registrationBean.setUrlMappings(Arrays.asList("/druid/*"));
		Map<String, String> initMap = new HashMap<>();
		initMap.put("allow", "");
		initMap.put("deny", "");
		initMap.put("resetEnable", "false");
		initMap.put("loginUsername", "admin");
		initMap.put("loginPassword", "123456");
		registrationBean.setInitParameters(initMap);
		return registrationBean;
	}

	@Bean
	public FilterRegistrationBean statFilter() {
		FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
		filterRegistrationBean.setFilter(new WebStatFilter());
		filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
		Map<String, String> initMap = new HashMap<>();
		initMap.put("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
		filterRegistrationBean.setInitParameters(initMap);
		return filterRegistrationBean;
	}

}

注意加载多数据源时，需要区分不同数据源的配置。

四、PrimaryConfig类配置，第一个默认数据源配置

package com.leshare.api.main.config.druid;

import java.util.Map;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryPrimary", transactionManagerRef = "transactionManagerPrimary", basePackages = {
		"com.leshare.api.main.mvc.dao.primary" })
public class PrimaryConfig {

	@Resource
	@Qualifier("primaryDataSource")
	private DataSource primaryDataSource;

	@Bean(name = "entityManagerPrimary")
	public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
		return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
	}

	@Resource
	private JpaProperties jpaProperties;

	private Map<String, String> getVendorProperties() {
		return jpaProperties.getProperties();
	}

	@Primary
	@Bean(name = "entityManagerFactoryPrimary")
	public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
		return builder.dataSource(primaryDataSource).packages("com.leshare.api.main.mvc.domain.primary")
				.persistenceUnit("primaryPersistenceUnit").properties(getVendorProperties()).build();
	}

	@Primary
	@Bean(name = "transactionManagerPrimary")
	public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
		return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
	}

}

注意使用数据源的DAO层路径，在命名时需要区分，以及数据源管理类名称定义时，需要注意。

五、SecondaryConfig类配置，第二个默认数据源配置。如有更多数据源，依次添加对应数据源配置。

package com.leshare.api.main.config.druid;

import javax.annotation.Resource;
import javax.sql.DataSource;

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 org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = "com.leshare.api.main.mvc.dao.secondary", sqlSessionTemplateRef = "sqlSessionTemplate")
public class SecondaryConfig {

	@Resource
	@Qualifier("secondaryDataSource")
	private DataSource secondaryDataSource;

	@Bean(name = "sqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource)
			throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
		return bean.getObject();
	}

	@Bean(name = "transactionManager")
	public DataSourceTransactionManager transactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	@Bean(name = "sqlSessionTemplate")
	@Primary
	public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory)
			throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}

}

到这里，多数据源配置已经完成，项目中如果遇到问题，可加入QQ 832133370 群一起学习交流。

springboot 多数据源配置

本文主要讲解在springboot项目中使用多数据源操作，并结合阿里巴巴数据源连接池监控数据库。

Springboot Freemarker配置全局变量

仿58一站多城市的实现及路由配置（二）

SpringBoot(二十一)IntelliJ IDEA配置Quartz启动项

Springboot的application.properties或者application.yml环境的指定运行与配置

Mac 电脑中 jdk多版本的切换配置

SpringBoot+ajax实现多图上传及回显

详解Tomcat多域名的配置

springboot redis使用lettuce配置多数据源的实现

不同端口配置多站点错误

maven 多配置文件设置