spring boot集成 mysql、sqlserver、oracle多数据源

最新推荐文章于 2024-04-12 17:37:13 发布

it1993

最新推荐文章于 2024-04-12 17:37:13 发布

阅读量2k

点赞数 2

分类专栏： spring boot 文章标签： java

本文链接：https://blog.csdn.net/it1993/article/details/105106189

版权

spring boot 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

应用场景：业务系统开发中，经常会遇到链接多数据源等情况

本文实现：实现链接mysql，oracle，sqlserver，可同时对3中数据源操作

项目目录如下

config目录下，在项目启动时加载不同的数据库sqlSessionFactory

controller目录下，正常的调用接口

dao目录下，分别为对具体的数据库访问方法，为了区分不同数据库，这里分了mysql、sqlserver、oracle目录，分别对mysql、sql server、oracle数据库操作

service目录下，正常的业务处理

resource下mybatis目录分别对3中数据库具体操作

config下mysql配置类

package com.liufan.mutildatasource.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.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

//表示这个类为一个配置类
@Configuration
// 配置mybatis的接口类放的地方
@MapperScan(basePackages = "com.liufan.mutildatasource.dao.mysql", sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class MysqlConfig {
    // 将这个对象放入Spring容器中
    @Bean(name = "mysqlDataSource")
    // 读取application.yml 中的配置参数映射成为一个对象
    // prefix表示参数的前缀
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource getDateSource1() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "mysqlSqlSessionFactory")
    // @Qualifier表示查找Spring容器中名字为mysqlDataSource的对象
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/mysql/*.xml"));
        return bean.getObject();
    }
    @Bean("mysqlSqlSessionTemplate")
    // 表示这个数据源是默认数据源
    @Primary
    public SqlSessionTemplate mysqlsqlsessiontemplate(
            @Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

sql server配置类

package com.liufan.mutildatasource.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.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

//表示这个类为一个配置类
@Configuration
// 配置mybatis的接口类放的地方
@MapperScan(basePackages = "com.liufan.mutildatasource.dao.sqlserver", sqlSessionFactoryRef = "sqlserverSqlSessionFactory")
public class SqlServerConfig {
    // 将这个对象放入Spring容器中
    @Bean(name = "sqlserverDataSource")
    // 表示这个数据源是默认数据源
    @Primary
    // 读取application.yml 中的配置参数映射成为一个对象
    // prefix表示参数的前缀
    @ConfigurationProperties(prefix = "spring.datasource.sqlserver")
    public DataSource getDateSource1() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "sqlserverSqlSessionFactory")
    // 表示这个数据源是默认数据源
    @Primary
    // @Qualifier表示查找Spring容器中名字为sqlserverDataSource的对象
    public SqlSessionFactory sqlserverSqlSessionFactory(@Qualifier("sqlserverDataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/sqlserver/*.xml"));
        return bean.getObject();
    }
    @Bean("sqlserverSqlSessionTemplate")
    public SqlSessionTemplate sqlserversqlsessiontemplate(
            @Qualifier("sqlserverSqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

oracle配置类

package com.liufan.mutildatasource.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.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

//表示这个类为一个配置类
@Configuration
// 配置mybatis的接口类放的地方
@MapperScan(basePackages = "com.liufan.mutildatasource.dao.oracle", sqlSessionFactoryRef = "oracleSqlSessionFactory")
public class OracleConfig {
    // 将这个对象放入Spring容器中
    @Bean(name = "oracleDataSource")
    // 读取application.yml 中的配置参数映射成为一个对象
    // prefix表示参数的前缀
    @ConfigurationProperties(prefix = "spring.datasource.oracle")
    public DataSource getDateSource1() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "oracleSqlSessionFactory")
    // @Qualifier表示查找Spring容器中名字为oracleDataSource的对象
    public SqlSessionFactory oracleSqlSessionFactory(@Qualifier("oracleDataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/oracle/*.xml"));
        return bean.getObject();
    }
    @Bean("oracleSqlSessionTemplate")
    public SqlSessionTemplate oraclesqlsessiontemplate(
            @Qualifier("oracleSqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

application.yml内容配置多种数据源,datasource下sqlserver、mysql、oracle分别代表定义数据库别名，可自定义名称，但是必须和上面配置类寻找路径相同

server:
  port: 8080

### 数据配置
spring:
  datasource:
    sqlserver:
      jdbc-url: jdbc:sqlserver://192.168.0.208:1433;database=aaaa;
      username: aaa
      password: aaa
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    oracle:
      jdbc-url: jdbc:oracle:thin:@192.168.0.209:1521/orcl
      username: aaa
      password: aaa
      driver-class-name: oracle.jdbc.driver.OracleDriver
    mysql:
      jdbc-url: jdbc:mysql://192.168.0.125:3306/tspsdb?useUnicode=true&characterEncoding=utf8
      username: aaa
      password: aaa
      driver-class-name: com.mysql.jdbc.Driver

pom.xml文件如下，oracle和sqlserver采用本地引入方式

<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.66</version>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/sqljdbc4-4.0.jar</systemPath>
        </dependency>

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/ojdbc-6.jar</systemPath>
        </dependency>

        <dependency>
            <groupId>cn.easyproject</groupId>
            <artifactId>orai18n</artifactId>
            <version>12.1.0.2.0</version>
        </dependency>


    </dependencies>


    <!-- 阿里云仓库 -->
    <repositories><!-- 代码库 -->
        <repository>
            <id>maven-ali</id>
            <url>http://maven.aliyun.com/nexus/content/groups/public//</url>
            <releases>
                <enabled>true</enabled>
            </releases>
            <snapshots>
                <enabled>true</enabled>
                <updatePolicy>always</updatePolicy>
                <checksumPolicy>fail</checksumPolicy>
            </snapshots>
        </repository>
    </repositories>

注意点：xml文件命名空间对应到具体dao下具体类

启动项目，通过controller中方法访问，输出如下图内容，controller和service就不贴代码了，

代码地址：https://github.com/liufan1993/multipleDataSources

it1993

关注

2
点赞
踩
12

收藏

觉得还不错? 一键收藏
3
评论
spring boot集成 mysql、sqlserver、oracle多数据源

应用场景：业务系统开发中，经常会遇到链接多数据源等情况本文实现：实现链接mysql，oracle，sqlserver，可同时对3中数据源操作项目目录如下config目录下，在项目启动时加载不同的数据库sqlSessionFactorycontroller目录下，正常的调用接口dao目录下，分别为对具体的数据库访问方法，为了区分不同数据库，这里分了mysql、sqlserver、...
复制链接

扫一扫