Spring boot+Mybatis连接多种数据库oracle，mysql，sqlserver

最新推荐文章于 2024-05-06 09:45:49 发布

misster华

最新推荐文章于 2024-05-06 09:45:49 发布

阅读量3.2k

点赞数 1

分类专栏：软件文章标签： spring boot+mybatis配置多个数据库

本文链接：https://blog.csdn.net/weixin_44774716/article/details/90369728

版权

软件专栏收录该内容

2 篇文章 0 订阅

订阅专栏

应用场景

	根据业务我需求，根据不通数据库放入不同的数据，进行项目重构整合，重构到一种数据库中，因为数据库涉及的表多，数据量大，下面，我进行的操作是到处数据库中的表结构。具体演示用配置类配置三种数据库。

项目结构

在这里插入图片描述

POM依赖

ps：这里注意一下SQL server的驱动特别要注意jdk的版本，MySQL的驱动我用的是最新的，Oracle驱动maven上下载不上的必须手动去官网下载然后添加到maven的本地仓库。


    <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>
            <version>2.1.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.1.41</version>
        </dependency>
        
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>

        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>

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

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.1.0</version>
        </dependency>

    </dependencies>

配置文件如下

server.port=8989
#mysql
spring.datasource.mysql.url=jdbc:mysql://localhost:3306/xxx?useUnicode=true&useSSL=true
spring.datasource.mysql.driverclassname=com.mysql.cj.jdbc.Driver
spring.datasource.mysql.password=xxx
spring.datasource.mysql.username=xxxx

#sqlserver
spring.datasource.sqlserver.url=jdbc:sqlserver://localhost:1433;DatabaseName=xxxx
spring.datasource.sqlserver.driverclassname=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.sqlserver.username=xxxx
spring.datasource.sqlserver.password=xxxx

#oracle
spring.datasource.oracle.driverclassname=oracle.jdbc.driver.OracleDriver
spring.datasource.oracle.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.oracle.username=xxxx
spring.datasource.oracle.password=xxxx

我的DAO层是用mybatis 注解开发。

这都不是重点，重点是单个数据库操作要放到单独的一个包下，不然会报错
在这里插入图片描述

重点，看这儿！！！

1. 首先启动类把自动配置数据源关闭

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class TableApplication {

    public static void main(String[] args) {
        SpringApplication.run(TableApplication.class, args);
    }
}

2.配置多种数据源，我用的是alibaba的Druid连接池，创建多种数据源的时候必须要有一个主数据源。用@Primary注解，下面有演示。

MySQL

package com.nec.table.conf;

import com.alibaba.druid.pool.DruidDataSource;
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.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.nec.table.dao.mysql", sqlSessionFactoryRef = "mysqlSqlSessionFactory")
@PropertySource({"classpath:application.properties"})
public class MysqlSqlSessionFactoryConfig {

	//从配置文件中获取值
    @Value("${spring.datasource.mysql.url}")
    private String mysqlurl;
    @Value("${spring.datasource.mysql.type}")
    private String mysqldriverClassName;
    @Value("${spring.datasource.mysql.username}")
    private String mysqlusername;
    @Value("${spring.datasource.mysql.password}")
    private String mysqlpassword;
	//创建数据源
    @Bean(name = "mysqlDataSource")
    public DataSource mysqlDataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(mysqldriverClassName);
        druidDataSource.setUrl(mysqlurl);
        druidDataSource.setUsername(mysqlusername);
        druidDataSource.setPassword(mysqlpassword);
        return druidDataSource;
    }
	//事务管理
    @Bean("mysqlTransactionManager")
    public DataSourceTransactionManager mysqlTransactionManager() {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager(mysqlDataSource());
        return dataSourceTransactionManager;
    }
	
    @Bean(name = "mysqlSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        return factoryBean.getObject();
    }

}

2.SQL server 是主数据源，看操作！！

package com.nec.table.conf;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.nec.table.dao.sqlserver", sqlSessionFactoryRef = "sqlserverSqlSessionFactory")
@PropertySource({"classpath:application.properties"})
public class SqlServerSqlSessionFactoryConfig {

    @Value("${spring.datasource.sqlserver.url}")
    private String sqlserverurl;
    @Value("${spring.datasource.sqlserver.type}")
    private String sqlserverdriverClassName;
    @Value("${spring.datasource.sqlserver.username}")
    private String sqlserverusername;
    @Value("${spring.datasource.sqlserver.password}")
    private String sqlserverpassword;

    @Bean(name = "sqlserverDataSource")
    @Primary
    public DataSource sqlserverDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUsername(sqlserverusername);
        dataSource.setPassword(sqlserverpassword);
        dataSource.setUrl(sqlserverurl);
        dataSource.setDriverClassName(sqlserverdriverClassName);
        return dataSource;
    }

    @Bean("sqlServerTractional")
    @Primary
    public DataSourceTransactionManager sqlServerTractional() {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(sqlserverDataSource());
        return transactionManager;
    }

    @Bean(name = "sqlserverSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory3(@Qualifier("sqlserverDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        return factoryBean.getObject();
    }

}

3.Oracle

package com.nec.table.conf;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.nec.table.dao.oracle", sqlSessionFactoryRef = "oracleSqlSessionFactory")
public class OracleSqlSessionFactoryConfig {
    @Value("${spring.datasource.oracle.url}")
    private String oracleurl;
    @Value("${spring.datasource.oracle.type}")
    private String oracledriverClassName;
    @Value("${spring.datasource.oracle.username}")
    private String oracleusername;
    @Value("${spring.datasource.oracle.password}")
    private String oraclepassword;

    @Bean(name = "oracleDataSource")
    public DataSource oracleDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(oracledriverClassName);
        dataSource.setUrl(oracleurl);
        dataSource.setUsername(oracleusername);
        dataSource.setPassword(oraclepassword);
        return dataSource;
    }

    @Bean("oracleTransactionManager")
    public DataSourceTransactionManager oracleTransactionManager() {
        DataSourceTransactionManager manager = new DataSourceTransactionManager(oracleDataSource());
        return manager;
    }

    @Bean(name = "oracleSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory2(@Qualifier("oracleDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        return factoryBean.getObject();
    }


}

基本操作就已经完成，配置这个东西网上有很多，但是适合自己实际业务的很少。如果你的需求刚好和我一样。

联系邮箱 fenhua_zhang@163.com

misster华

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
Spring boot+Mybatis连接多种数据库oracle，mysql，sqlserver

应用场景根据业务我需求，根据不通数据库放入不同的数据，进行项目重构整合，重构到一种数据库中，因为数据库涉及的表多，数据量大，下面，我进行的操作是到处数据库中的表结构。具体演示用配置类配置三种数据库。项目结构POM依赖ps：这里注意一下SQL server的驱动特别要注意jdk的版本，MySQL的驱动我用的是最新的，Oracle驱动maven上下载不上的必须手动去官网下载然后添加到ma...
复制链接

扫一扫