SpringBoot3配置Mysql与SQL Server双数据源

最新推荐文章于 2024-03-24 19:43:18 发布

Secede.

最新推荐文章于 2024-03-24 19:43:18 发布

阅读量1k

点赞数 11

文章标签： mysql 数据库 spring boot sqlserver

本文链接：https://blog.csdn.net/qq_41858327/article/details/135034272

版权

工程结构如上图

1.配置POM依赖

    <dependency>
      <groupId>org.json</groupId>
      <artifactId>json</artifactId>
      <version>20230618</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid-spring-boot-starter</artifactId>
      <version>1.2.16</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>3.0.2</version>
    </dependency>
    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
    </dependency>

    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>8.1.0</version>
    </dependency>

2.配置数据库连接信息->application.yml

spring:
  datasource:
    mysql:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/springboottest
      username: root
      password: mysql_dZJc2d
    sqlserver:
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://localhost:1433;databaseName=TutorialDB;encrypt=true;trustServerCertificate=true
      username: sa
      password: 123456


Server:
  port: 10086
  servlet:
    context-pa th: /dbw

3.多数据源配置定义

mysqlConfig.java

@Configuration
@MapperScan(basePackages = "org.dbw.mapper.mysqlDataSource", sqlSessionTemplateRef = "sqlSessionTemplateMysql")
public class mysqlConfig {

    @Bean(name = "MysqlSource")
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    @Primary //设置主数据源
    public DataSource DataSourceMysql(){
        DruidDataSource dataSource = new DruidDataSource();
        return dataSource;

    }

    @Bean(name = "sqlSessionFactoryMysql")
    @Primary
    public SqlSessionFactory sqlSessionFactoryOne(@Qualifier("MysqlSource") DataSource dataSource)throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "dataSourceTransactionManagerMysql")
    @Primary
    public DataSourceTransactionManager dataSourceTransactionManagerOne(@Qualifier("MysqlSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlSessionTemplateMysql")
    @Primary
    public SqlSessionTemplate sqlSessionTemplateOne(@Qualifier("sqlSessionFactoryMysql") SqlSessionFactory sqlSessionFactory)throws Exception{
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

sqlserverConfig.java

@Configuration
@MapperScan(basePackages = "org.dbw.mapper.sqlserverDataSource", sqlSessionTemplateRef = "sqlSessionTemplateSqlserver")
public class sqlserverConfig {
    @Bean(name = "SqlserverSource")
    @ConfigurationProperties(prefix = "spring.datasource.sqlserver")
    public DataSource DataSourceSqlserver(){
        DruidDataSource dataSource = new DruidDataSource();
        return dataSource;

    }

    @Bean(name = "sqlSessionFactorySqlserver")
    public SqlSessionFactory sqlSessionFactoryOne(@Qualifier("SqlserverSource") DataSource dataSource)throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "dataSourceTransactionManagerSqlserver")
    public DataSourceTransactionManager dataSourceTransactionManagerOne(@Qualifier("SqlserverSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlSessionTemplateSqlserver")
    public SqlSessionTemplate sqlSessionTemplateOne(@Qualifier("sqlSessionFactorySqlserver") SqlSessionFactory sqlSessionFactory)throws Exception{
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

3.Mapper实现

UserMapperMysql.java

public interface UserMapperMysql {

    @Select("select name,age from test")
    List<User> fetchAll();
}

UserMapperSqlserver.java

public interface UserMapperSqlserver {

    @Select("SELECT NAME,AGE FROM TEST")
    List<User> fetchAll();
}

4.Service实现

UserService.java


public interface UserService {
    List<User> fetchAll();
    List<User> fetchSubAll();
}

UserServiceImpl.java

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapperMysql userMapperMysql;

    @Autowired
    private UserMapperSqlserver userMapperSqlserver;

    @Override
    public List<User> fetchAll() {
        return userMapperMysql.fetchAll();
    }
    public List<User> fetchSubAll() {
        return userMapperSqlserver.fetchAll();
    }
}

5.Contoller实现

UserContoller.java

@CrossOrigin
@RestController
public class UserContoller {

    @Autowired
    private UserService userService;


    @GetMapping("/fetchall")
    public String FatchAll()
    {
        return utils.List2JsonArrayString(userService.fetchAll());
    }
    @GetMapping("/fetchsuball")
    public String FatchSubAll()
    {
        return utils.List2JsonArrayString(userService.fetchSubAll());
    }

}