快捷導(dǎo)航

SpringBoot整合mysql、postgres及sqlserver實(shí)現(xiàn)多數(shù)據(jù)源配置實(shí)戰(zhàn)案例

更新時(shí)間：2023年12月01日 10:38:31 作者：袁久

在工作中業(yè)務(wù)的發(fā)展或業(yè)務(wù)數(shù)據(jù)隔離的場(chǎng)景下,通常需要一個(gè)項(xiàng)目中引入多個(gè)數(shù)據(jù)源,但SpringBoot默認(rèn)的自動(dòng)化配置是單數(shù)據(jù)源的,這篇文章主要給大家介紹了關(guān)于SpringBoot整合mysql、postgres及sqlserver實(shí)現(xiàn)多數(shù)據(jù)源配置的相關(guān)資料,需要的朋友可以參考下

1、首先要引入以下依賴
2、demo的項(xiàng)目結(jié)構(gòu)如下
3、yml配置文件
4、配置類
5、controller、dao、service以及對(duì)應(yīng)的.xml文件可以根據(jù)自身業(yè)務(wù)情況進(jìn)行開(kāi)發(fā)，再次不做過(guò)多贅述。
總結(jié)

分享一下近期處理的一個(gè)小demo，關(guān)于配置多數(shù)據(jù)源實(shí)現(xiàn)不同服務(wù)之間的數(shù)據(jù)推送和數(shù)據(jù)治理。第一次接觸到pg庫(kù)和sqlserver一頭霧水，選擇了JDBC+mybatis-plus的方式去鏈接。

1、首先要引入以下依賴

	<dependencies>
		<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-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.12</version>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.apache.httpcomponents</groupId>
			<artifactId>httpclient</artifactId>
			<version>4.5.10</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.62</version>
		</dependency>
		<!--mysql 驅(qū)動(dòng)-->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.22</version>
		</dependency>
		<!--postgresql 驅(qū)動(dòng)-->
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>
		<!--sqlserver 配置-->
		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>mssql-jdbc</artifactId>
			<version>9.4.0.jre8</version>
		</dependency>
		<!--//spring默認(rèn)的jdbc連接-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.13</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.4.0</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-base</artifactId>
			<version>4.4.0</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<version>2.7.12</version>
				<configuration>
					<!-- 指定該Main Class為全局的唯一入口 -->
					<mainClass>com.zkgl.ZsjDemoApplication</mainClass>
				</configuration>
				<executions>
					<execution>
						<goals>
							<!--可以把依賴的包都打包到生成的Jar包中 -->
							<goal>repackage</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>

2、demo的項(xiàng)目結(jié)構(gòu)如下

3、yml配置文件

server:
  port:3666
spring:
  application:
    name: multiple-data
  datasource:
    show-sql: false
    db1:
      jdbc-url: jdbc:mysql://ip地址:端口/數(shù)據(jù)庫(kù)名?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true
      username: 數(shù)據(jù)庫(kù)賬號(hào)
      password: 數(shù)據(jù)庫(kù)密碼
      driver-class-name: com.mysql.cj.jdbc.Driver
    db2:
      jdbc-url: jdbc:postgresql://ip地址:端口/數(shù)據(jù)庫(kù)名?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true
      username: 數(shù)據(jù)庫(kù)賬號(hào)
      password: 數(shù)據(jù)庫(kù)密碼
      driver-class-name: org.postgresql.Driver
    db3:
      jdbc-url: jdbc:sqlserver://ip地址:端口;databaseName=數(shù)據(jù)庫(kù)名
      username: 數(shù)據(jù)庫(kù)賬號(hào)
      password: 數(shù)據(jù)庫(kù)密碼
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

4、配置類

package com.zkgl.config;

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.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.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
/**
 * @Author: 袁振豪
 * @Description:
 * @Date: Created in  2023-07-23 11:04 PM
 * @Modified By:
 */
@Configuration
@MapperScan(basePackages = "com.zkgl.dao.db1",sqlSessionFactoryRef = "db1SqlSessionFactory")
public class MysqlDataSourceConfig {
    static final String MAPPER_LOCATION = "classpath:/mapper/db1/*.xml";
    @Bean("db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean("db1SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return bean.getObject();
    }
    @Bean("db1SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.zkgl.config;

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.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.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
/**
 * @Author: 袁振豪
 * @Description:
 * @Date: Created in  2023-07-23 11:04 PM
 * @Modified By:
 */
@Configuration
@MapperScan(basePackages = "com.zkgl.dao.db2",sqlSessionFactoryRef = "db2SqlSessionFactory")
public class PgDataSourceConfig {

    static final String MAPPER_LOCATION = "classpath:/mapper/db2/*.xml";
    @Bean("db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource getDb2DataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean("db2SqlSessionFactory")
    public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return bean.getObject();
    }
    @Bean("db2SqlSessionTemplate")
    public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.zkgl.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;

/**
 * @Author: 袁振豪
 * @Description:
 * @Date: Created in  2023-07-23 11:04 PM
 * @Modified By:
 */
@Configuration
@MapperScan(basePackages = "com.zkgl.dao.db3",sqlSessionFactoryRef = "db3SqlSessionFactory")
public class SqlServerDataSourceConfig {
    static final String MAPPER_LOCATION = "classpath:/mapper/db3/*.xml";
    @Bean("db3DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db3")
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean("db3SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db3DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return bean.getObject();
    }
    @Bean("db3SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db3SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

5、controller、dao、service以及對(duì)應(yīng)的.xml文件可以根據(jù)自身業(yè)務(wù)情況進(jìn)行開(kāi)發(fā)，再次不做過(guò)多贅述。

總結(jié)：要進(jìn)行多數(shù)據(jù)源的配置，以此案例為例，最重要的是對(duì)各個(gè)庫(kù)的適配，也就是各自的驅(qū)動(dòng)。眾所周知，要使用JDBC連接數(shù)據(jù)庫(kù)，主要有以下步驟：

注冊(cè)驅(qū)動(dòng)
建立數(shù)據(jù)庫(kù)連接
創(chuàng)建數(shù)據(jù)庫(kù)操作對(duì)象
執(zhí)行SQL語(yǔ)句
處理查詢結(jié)果集
關(guān)閉資源

而在本案例中，pom中引入了相關(guān)依賴，在yml配置了驅(qū)動(dòng)，之后在config中以Bean的形式分別命名和初始化相關(guān)配置，這樣在Springboot項(xiàng)目中，通過(guò)@SpringBootApplication注解中的@EnableAutoConfigtion注解就可以掃描到這些配置好的Bean，從而正常使用了。

總結(jié)

到此這篇關(guān)于SpringBoot整合mysql、postgres及sqlserver實(shí)現(xiàn)多數(shù)據(jù)源配置的文章就介紹到這了,更多相關(guān)SpringBoot多數(shù)據(jù)源配置內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: