SpringBoot MySQL SqlServer 多数据源配置

avatar 2021年10月07日09:57:05 6 2615 views
博主分享免费Java教学视频，B站账号：Java刘哥

最近帮一个朋友做了一个从 SqlServer 查询指定数据更新到 MySQL的一个工具，用Java实现。

本文介绍 SpringBoot 连接多个数据源时配置

这里以一个MySQL，2个SqlServer为例介绍。

一、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 https://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.5.5</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.liuyanzhao</groupId>
	<artifactId>transfer</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>transfer</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-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.16.22</version>
		</dependency>
		
		<!-- mysql的jdbc依赖 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.16</version>
		</dependency>
		<!-- sqlserver的jdbc依赖 -->
		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>mssql-jdbc</artifactId>
			<version>7.4.1.jre8</version>
		</dependency>

	</dependencies>

<!--	<build>-->
<!--		<plugins>-->
<!--			<plugin>-->
<!--				<groupId>org.springframework.boot</groupId>-->
<!--				<artifactId>spring-boot-maven-plugin</artifactId>-->
<!--			</plugin>-->
<!--		</plugins>-->
<!--	</build>-->
	<build>
		<!-- 加载资源文件 -->
		<resources>
			<resource>
				<directory>src/main/java</directory>
				<includes>
					<include>**/*.yml</include>
					<include>**/*.properties</include>
					<include>**/*.xml</include>
				</includes>
				<filtering>false</filtering>
			</resource>
			<resource>
				<directory>src/main/resources</directory>
				<includes>
					<include>**/*.yml</include>
					<include>**/*.properties</include>
					<include>**/*.xml</include>
				</includes>
				<filtering>false</filtering>
			</resource>
		</resources>
		<plugins>
			<!-- jar发布时跳过此模块 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-deploy-plugin</artifactId>
				<configuration>
					<skip>true</skip>
				</configuration>
			</plugin>
			<!-- 打包生成启动脚本 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
					<encoding>UTF-8</encoding>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.codehaus.mojo</groupId>
				<artifactId>appassembler-maven-plugin</artifactId>
				<version>1.2.1</version>
				<executions>
					<execution>
						<id>make-assembly</id>
						<phase>package</phase>
						<goals>
							<goal>assemble</goal>
						</goals>
					</execution>
				</executions>
				<configuration>
					<!--lib下直接存放jar，没有路径文件夹（如com/apache)，如果没有这个选项则放在lib下的jar包会被com/apache类似的多层文件夹包裹起来 -->
					<repositoryLayout>flat</repositoryLayout>
					<configurationDirectory>conf</configurationDirectory>
					<configurationSourceDirectory>src/main/resources</configurationSourceDirectory>
					<copyConfigurationDirectory>true</copyConfigurationDirectory>
					<includeConfigurationDirectoryInClasspath>true</includeConfigurationDirectoryInClasspath>
					<!--生成的项目的目录位置，这里的client是项目的名称，你可以根据你的需要自己随便命名 -->
					<assembleDirectory>${project.build.directory}/transfer</assembleDirectory>
					<!--java启动参数 -->
					<!-- <extraJvmArguments>-Xms1024m -Dfile.encoding=utf-8</extraJvmArguments> -->
					<binFileExtensions>
						<unix>.sh</unix>
					</binFileExtensions>
					<platforms>
						<platform>windows</platform>
						<platform>unix</platform>
					</platforms>
					<repositoryName>lib</repositoryName>
					<programs>
						<program>
							<!--指定主类，脚本名。会生成shell/bat两种类型,也可用platforms指定运行平台 -->
							<mainClass>com.liuyanzhao.transfer.TransferApplication</mainClass>
							<!-- 生成的脚本文件的名称，比如start.sh,你也可以根据你的需要命名成其他名字 -->
							<name>start</name>
						</program>
					</programs>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

 

二、application.yml

server:
  port: 8080

spring:
  application:
    name: transfer

  datasource:
    ## MySQL 目标库
    mysql:
      jdbc-url: jdbc:mysql://localhost:3306/transfer_demo?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&nullCatalogMeansCurrent=true
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver

    ## SqlServer 目标库
    mssql1:
      jdbc-url: jdbc:sqlserver://localhost:1433;databasename=ems
      username: sa
      password: sa
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

    ## SqlServer 源库
    mssql2:
      jdbc-url: jdbc:sqlserver://localhost:1433;databasename=uScada
      username: sa
      password: sa
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

 

三、Java配置类

1、MySQL数据源配置类

package com.liuyanzhao.transfer.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 org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


/**
 * @author 言曌
 * @date 2021/10/6 10:26 上午
 */
@Configuration(value = "mysql")
@MapperScan(basePackages = "com.liuyanzhao.transfer.dao.mysql", sqlSessionFactoryRef = "MysqlSqlSessionFactory")
public class MySQLConfig {
    @Bean(name = "MysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource getDateSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 配置事务管理
     */
    @Bean(name = "MysqlTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("MysqlDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 配置工厂
     *
     * @param datasource
     * @return
     * @throws Exception
     */
    @Bean(name = "MysqlSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("MysqlDataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mysql/*.xml"));
        return bean.getObject();
    }

    /**
     * 配置会话
     *
     * @param sessionfactory
     * @return
     */
    @Bean("MysqlSqlSessionTemplate")
    public SqlSessionTemplate testsqlsessiontemplate(
            @Qualifier("MysqlSqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

 

2、SqlServer 1 数据源配置类

package com.liuyanzhao.transfer.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 org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @author 言曌
 * @date 2021/10/6 10:26 上午
 */
@Configuration(value = "mssql1")
@MapperScan(basePackages = "com.liuyanzhao.transfer.dao.mssql1", sqlSessionFactoryRef = "MssqlSqlSessionFactory1")
public class MSSQL1Config {
    /**
     * 配置数据源
     *
     * @return
     */
    @Bean(name = "MssqlDataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.mssql1")
    public DataSource getDateSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 配置事务管理
     */
    @Bean(name = "MssqlTransactionManager1")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("MssqlDataSource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 配置工厂
     *
     * @param datasource
     * @return
     * @throws Exception
     */
    @Bean(name = "MssqlSqlSessionFactory1")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("MssqlDataSource1") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mssql1/*.xml"));
        return bean.getObject();
    }

    /**
     * 配置会话
     *
     * @param sessionfactory
     * @return
     */
    @Bean("MssqlSqlSessionTemplate1")
    public SqlSessionTemplate testsqlsessiontemplate(
            @Qualifier("MssqlSqlSessionFactory1") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

 

3、SqlServer 2数据源配置类

package com.liuyanzhao.transfer.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 org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @author 言曌
 * @date 2021/10/6 10:26 上午
 */
@Configuration(value = "mssql2")
@MapperScan(basePackages = "com.liuyanzhao.transfer.dao.mssql2", sqlSessionFactoryRef = "MssqlSqlSessionFactory2")
public class MSSQL2Config {
    /**
     * 配置数据源
     *
     * @return
     */
    @Bean(name = "MssqlDataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.mssql2")
    public DataSource getDateSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 配置事务管理
     */
    @Bean(name = "MssqlTransactionManager2")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("MssqlDataSource2") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 配置工厂
     *
     * @param datasource
     * @return
     * @throws Exception
     */
    @Bean(name = "MssqlSqlSessionFactory2")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("MssqlDataSource2") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mssql2/*.xml"));
        return bean.getObject();
    }

    /**
     * 配置会话
     *
     * @param sessionfactory
     * @return
     */
    @Bean("MssqlSqlSessionTemplate2")
    public SqlSessionTemplate testsqlsessiontemplate(
            @Qualifier("MssqlSqlSessionFactory2") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

关于 mapper xml，我开始是放在 resources 下面的，但是那个打包插件最终打包会丢掉外面的文件夹，只会把里面的xml放到最终从conf里面。

所以我这里把mapper xml 放在了dao包里，当然大家可以在里面新建一个mapping 包，用来放xml

 

代码结构如下

 

 

 

历史上的今天
十月
07日
  • 微信
  • 交流学习，有偿服务
  • weinxin
  • 博客/Java交流群
  • 资源分享，问题解决，技术交流。群号：590480292
  • weinxin
avatar

发表评论

avatar 登录者：匿名
匿名评论，评论回复后会有邮件通知

  

已通过评论：0   待审核评论数：0