devbox
不正经
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：不正经 | 2024-05-01 23:15:35

赞

踩

springboot sqlserver mybatis

应用场景

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

  • 1

项目结构

在这里插入图片描述

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>


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71

配置文件如下

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

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

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

重点，看这儿！！！

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

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

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

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

  1. 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();
    }

}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57

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();
    }

}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59

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();
    }


}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52

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

联系邮箱 fenhua_zhang@163.com

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/不正经/article/detail/520992
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号