Java后端爱上SpringBoot 第四节:SpringBoot多数据源

最新推荐文章于 2024-03-11 09:31:19 发布

粉蒸肉加馍

最新推荐文章于 2024-03-11 09:31:19 发布

阅读量198

点赞数

分类专栏： SpringBoot

本文链接：https://blog.csdn.net/baidu_37307156/article/details/85414820

版权

SpringBoot 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Java后端爱上SpringBoot 第四节:SpringBoot多数据源

配置一个Oracle数据库
配置一个SQLServer数据库
配置一个MySql数据库

PS：配置多数据源这个事情是项目中用到的，之前也做过dome，但是做的时候又踩了几个坑，因此要记录一下。

配置一个Oracle数据库

先贴一下pom.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.1.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.summit</groupId>
	<artifactId>summit-tas</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>SmartTAS</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<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-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>net.sourceforge.jtds</groupId>
			<artifactId>jtds</artifactId>
			<version>1.2.6</version>
		</dependency>
		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>mssql-jdbc</artifactId>
			<version>6.1.0.jre8</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.11</version><!--$NO-MVN-MAN-VER$ -->
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

我们配置一个主数据源：primary

application.yml配置如下：

spring:
  datasource: 
    primary:
###运维库
      driver-class-name: oracle.jdbc.driver.OracleDriver 
      jdbc-url: jdbc:oracle:thin:@//127.0.0.1:1521/ORCL
      username: oracle
      password: oracle
###验证连接有效性
      test-while-idle: true
###获取连接时候验证，会影响性能
      test-on-borrow: false
###在连接归还到连接池时是否测试该连接
      test-on-return: false
###空闲连接回收的时间间隔
      time-between-eviction-runs-millis: 300000
###连接池空闲连接的有效时间 ，设置30分钟
      min-evictable-idle-time-millis: 1800000
###连接池配置
      maximum-pool-size: 50
      minIdle: 50
      connection-timeout: 30000
      max-lifetime: 1800000

新增一个DataSourcesConfig 用来配置数据源的Bean
坑点一：
SpringBoot1.*的时候是spring.datasource.jdbcUrl，2.0的时候变成了spring.datasource.jdbc-url


import javax.sql.DataSource;

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;

/**
 * 多数据源配置
 * 
 * @Title: DataSourcesConfig.java
 * @Package com.summit.tas.cfg.db
 * @Description: TODO
 * @author hyn  
 * @date 2018年12月27日 下午3:43:53
 * @version V1.0  
 */
@Configuration
public class DataSourcesConfig {
	@Bean(name = "primaryDataSource")
	@Qualifier("primaryDataSource")
	@Primary
	@ConfigurationProperties(prefix = "spring.datasource.primary")
	public DataSource primaryDataSource() {
		return DataSourceBuilder.create().build();
	}
}

使用@Qualifier 来区分数据源。
新增一个PrimaryConfig类：

package com.summit.tas.cfg.db;

import java.util.Properties;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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.summit.tas.primary.repository" })
//basePackages 为dao包，会自动扫面配置下面的包
public class PrimaryConfig {
	@Autowired 
	@Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

	/**
	 * 主库jpa 实例管理器工厂配置
	 */
	@Primary
	@Bean(name = "entityManagerFactoryPrimary")
	public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
	//实体类的配置路径
		LocalContainerEntityManagerFactoryBean em = builder.dataSource(primaryDataSource).packages("com.summit.tas.primary.dto")
				.build();
		Properties properties = new Properties();
		properties.setProperty("hibernate.physical_naming_strategy",
				"org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
		em.setJpaProperties(properties);
		return em;
	}

	/**
	 * 主库事务管理器配置
	 */
	@Primary
	@Bean(name = "transactionManagerPrimary")
	public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
		JpaTransactionManager txManager = new JpaTransactionManager();
		txManager.setEntityManagerFactory(entityManagerFactoryPrimary(builder).getObject());
		return txManager;
	}
}

下面就可以进行增删改查了。

配置一个SQLServer数据库

再配一个SQLServer数据源：
给application.yml添加以下配置：

    sydn:
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      jdbc-url: jdbc:sqlserver://127.0.0.1:1433;database=cqswssydn
      username: sa
      password: 123456
    ###验证连接有效性
      test-while-idle: true
###获取连接时候验证，会影响性能
      test-on-borrow: false
###在连接归还到连接池时是否测试该连接
      test-on-return: false
###空闲连接回收的时间间隔
      time-between-eviction-runs-millis: 300000
###连接池空闲连接的有效时间 ，设置30分钟
      min-evictable-idle-time-millis: 1800000
###连接池配置
      maximum-pool-size: 20
      minIdle: 20
      connection-timeout: 30000
      max-lifetime: 1800000

在DataSourcesConfig添加以下：

	@Bean(name = "sydnDataSource")
	@Qualifier("sydnDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.sydn")
	public DataSource sydnDataSource() {
		return DataSourceBuilder.create().build();
	}

新增一个SydnConfig：

package com.summit.tas.cfg.db;

import java.util.Properties;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
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 = "entityManagerFactorySydn", transactionManagerRef = "transactionManagerSydn", basePackages = {
		"com.summit.tas.sydn.repository" })
public class SydnConfig {

	@Autowired
	@Qualifier("sydnDataSource")
	private DataSource sydnDataSource;

	/**
	 * sydn jpa 实例管理器工厂配置
	 */
	@Bean(name = "entityManagerFactorySydn")
	public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
		LocalContainerEntityManagerFactoryBean em = builder.dataSource(sydnDataSource).packages("com.summit.tas.sydn.dto")
				.build();
		Properties properties = new Properties();
		properties.setProperty("hibernate.physical_naming_strategy",
				"org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
		em.setJpaProperties(properties);
		return em;
	}

	/**
	 * sydn 事务管理器配置
	 */
	@Bean(name = "transactionManagerSydn")
	public PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
		JpaTransactionManager txManager = new JpaTransactionManager();
		txManager.setEntityManagerFactory(entityManagerFactorySecondary(builder).getObject());
		return txManager;
	}
}

坑点二：
用sqlserver的时候不要用

		<dependency>
			<groupId>net.sourceforge.jtds</groupId>
			<artifactId>jtds</artifactId>
			<version>1.2.6</version>
		</dependency>

要用：

		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>mssql-jdbc</artifactId>
			<version>6.1.0.jre8</version>
		</dependency>

配置一个MySql数据库

再配一个MySql数据源：
给application.yml添加以下配置：

    mysql:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/spring?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&allowPublicKeyRetrieval=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
      username: hyn
      password: hyn1234
###验证连接有效性
      test-while-idle: true
###获取连接时候验证，会影响性能
      test-on-borrow: false
###在连接归还到连接池时是否测试该连接
      test-on-return: false
###空闲连接回收的时间间隔
      time-between-eviction-runs-millis: 300000
###连接池空闲连接的有效时间 ，设置30分钟
      min-evictable-idle-time-millis: 1800000
###连接池配置
      maximum-pool-size: 20
      minIdle: 20
      connection-timeout: 30000
      max-lifetime: 1800000

在DataSourcesConfig添加以下：

	@Bean(name = "mysqlDataSource")
	@Qualifier("mysqlDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.mysql")
	public DataSource mysqlDataSource() {
		return DataSourceBuilder.create().build();
	}

新增一个MysqlConfig：

package com.summit.tas.cfg.db;

import java.util.Properties;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
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 = "entityManagerFactoryMysql", transactionManagerRef = "transactionManagerMysql", basePackages = {
		"com.summit.tas.mysql.repository" })
public class MysqlConfig {

	@Autowired
	@Qualifier("mysqlDataSource")
	private DataSource wdsDataSource;

	/**
	 * mysql jpa 实例管理器工厂配置
	 */
	@Bean(name = "entityManagerFactoryWds")
	public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
		LocalContainerEntityManagerFactoryBean em = builder.dataSource(wdsDataSource).packages("com.summit.tas.mysql.dto")
				.build();
		Properties properties = new Properties();
		properties.setProperty("hibernate.physical_naming_strategy",
				"org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
		em.setJpaProperties(properties);
		return em;
	}

	/**
	 * mysql 事务管理器配置
	 */
	@Bean(name = "transactionManagerWds")
	public PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
		JpaTransactionManager txManager = new JpaTransactionManager();
		txManager.setEntityManagerFactory(entityManagerFactorySecondary(builder).getObject());
		return txManager;
	}
}

粉蒸肉加馍

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java后端爱上SpringBoot 第四节:SpringBoot多数据源

Java后端爱上SpringBoot 第四节:SpringBoot多数据源配置一个Oracle数据库配置一个SQLServer数据库配置一个MySql数据库PS：配置多数据源这个事情是项目中用到的，之前也做过dome，但是做的时候又踩了几个坑，因此要记录一下。配置一个Oracle数据库先贴一下pom.xml文件：&amp;lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8...
复制链接

扫一扫