【Spring Boot】配置多数据源：MySQL + SQL Server

作者：Albert.Wen 添加时间：2023-11-27 12:54:35 修改时间：2024-04-25 16:16:38 分类：Java框架/系统编辑

注意事项：需要驱动导包

Druid的包和SQL Server的包为

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>
<!--sqlserver驱动 -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc4</artifactId>
    <version>4.0</version>
</dependency>

注意：MySQL和SQL Server配置中有一点要做判断

if (driveClassName.equals("com.microsoft.sqlserver.jdbc.SQLServerDriver")) {
    //sqlserver数据库要加这个
    datasource.setValidationQuery("select 'x'");
}

开始

导包驱动呀连接呀都得有

我展示我的

<dependencies>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-generator</artifactId>
        <version>3.2.0</version>
    </dependency>
    <!-- aop -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aspects</artifactId>
        <version>5.2.19.RELEASE</version>
    </dependency>

    <!-- mybatis plus 代码生成器 -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.2.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.12</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- 引入 redis 依赖 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-redis</artifactId>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.6</version>
    </dependency>
    <!--sqlserver驱动 -->
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>sqljdbc4</artifactId>
        <version>4.0</version>
    </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>
    </dependency>
</dependencies>

接下来看配置，我直接加我的配置

server.port=8081

spring.datasource.demo.user.url = jdbc:mysql://localhost:3306/sqlpractice?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
spring.datasource.demo.user.username = root
spring.datasource.demo.user.password = 123456
spring.datasource.demo.user.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.demo.server.url = jdbc:mysql://172.11.11.12:3306/sqltest?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.demo.server.username = root
spring.datasource.demo.server.password = 123456
spring.datasource.demo.server.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.demo.SqlServer.url=jdbc:sqlserver://localhost:1433;DatabaseName=aiwozhonghua;
spring.datasource.demo.SqlServer.username= sa
spring.datasource.demo.SqlServer.password= Aa1212121
spring.datasource.demo.SqlServer.driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

#mapper-locations= classpath*:/com/example/demo/dao/*.xml

#mapper文件的路径
mybatis.demo.server.mapper-location=classpath*:/com/example/demo/demo-user/*.xml
mybatis.demo.user.mapper-location=classpath*:/com/example/demo/demo-server/*.xml
mybatis.demo.SqlServer.mapper-location=classpath*:/com/example/demo/demo-sqlserver/*.xml

mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.auto-mapping-behavior=full
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis-plus.mapper-locations=classpath*:mapper/**/*Mapper.xml
mybatis-plus.global-config.db-config.logic-not-delete-value=1
mybatis-plus.global-config.db-config.logic-delete-value=0


############################################################
#
# REDIS 配置
#
############################################################
# Redis数据库索引（默认为0）
spring.redis.database=1
# Redis服务器地址
spring.redis.host=localhost
# Redis服务器连接端口
spring.redis.port=6379
# Redis服务器连接密码（默认为空）
spring.redis.password=
# 连接池最大连接数（使用负值表示没有限制）
spring.redis.pool.max-active=1000
# 连接池最大阻塞等待时间（使用负值表示没有限制）
spring.redis.pool.max-wait=-1
# 连接池中的最大空闲连接
spring.redis.pool.max-idle=10
# 连接池中的最小空闲连接
spring.redis.pool.min-idle=2
# 连接超时时间（毫秒）
spring.redis.timeout=0

上边注意配置的的不同

接下来加注解

一共三个注解分别给三个库用 MySQL + MySQL + SQL Server

package com.example.demo.interfaceAcc;

import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
@Component
@Mapper
public @interface DemoServerMapper {

    /**
     * The value may indicate a suggestion for a logical component name,
     * to be turned into a Spring bean in case of an autodetected component.
     * @return the suggested component name, if any (or empty String otherwise)
     */
    String value() default "";
}

package com.example.demo.interfaceAcc;

import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
@Component
@Mapper
public @interface DemoUserMapper {

    /**
     * The value may indicate a suggestion for a logical component name,
     * to be turned into a Spring bean in case of an autodetected component.
     * @return the suggested component name, if any (or empty String otherwise)
     */
    String value() default "";
}

package com.example.demo.interfaceAcc;

import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
@Component
@Mapper
public @interface DemoSqlServerMapper {

    /**
     * The value may indicate a suggestion for a logical component name,
     * to be turned into a Spring bean in case of an autodetected component.
     * @return the suggested component name, if any (or empty String otherwise)
     */
    String value() default "";
}

接下来这步非常重要配置通用连接数据库的配置

package com.example.demo.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;


public abstract class AbstractDbConfig {

    protected SqlSessionFactory sqlSessionFactory(DataSource dataSource, String mapperLocation) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
        Resource[] resource= resourceResolver.getResources(mapperLocation);
        factoryBean.setMapperLocations(resource);
        return factoryBean.getObject();
    }

    protected DataSource dataSourceFactory(String driveClassName, String url, String userName, String password){
        DruidDataSource datasource = new DruidDataSource();
        datasource.setDriverClassName(driveClassName);
        datasource.setUrl(url);
        datasource.setUsername(userName);
        datasource.setPassword(password);
        datasource.setMaxActive(20);
        datasource.setInitialSize(20);
        if(driveClassName.equals("com.microsoft.sqlserver.jdbc.SQLServerDriver")) {
            //sqlserver数据库要加这个
            datasource.setValidationQuery("select 'x'");
        }
        return datasource;
    }
}

接来这一步就是几个库分别继承上边这个然后实现连接等操作

我这是三个就是 MySQL + MySQL + SQL Server

package com.example.demo.config;

import com.example.demo.interfaceAcc.DemoUserMapper;
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.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;


import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = {"com.example"},annotationClass = DemoUserMapper.class,
        sqlSessionTemplateRef = "demoUserTemplate")
public class DemoUserDbConfig extends AbstractDbConfig {

    @Value("${spring.datasource.demo.user.url}")
    private String url;

    @Value("${spring.datasource.demo.user.username}")
    private String userName;

    @Value("${spring.datasource.demo.user.password}")
    private String password;

    @Value("${spring.datasource.demo.user.driver-class-name}")
    private String driveClassName;

    @Value(value = "${mybatis.demo.user.mapper-location}")
    private String mapperLocation;


    @Bean(name = "demoUser")
    public DataSource secondaryDataSource() {
        return dataSourceFactory(driveClassName, url, userName, password);
    }

    @Bean(name = "demoUserTemplate")
    public SqlSessionTemplate demoUserSqlTemplate() throws Exception {
        return new SqlSessionTemplate((sqlSessionFactory(secondaryDataSource(), mapperLocation)));
    }

    @Bean
    @Qualifier("demoUserTransaction")
    public PlatformTransactionManager demoUserTransaction() {
        return new DataSourceTransactionManager(secondaryDataSource());
    }
}

package com.example.demo.config;

import com.example.demo.interfaceAcc.DemoServerMapper;
import com.example.demo.interfaceAcc.DemoUserMapper;
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.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;


import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.example"},annotationClass = DemoServerMapper.class,
        sqlSessionTemplateRef = "demoServerTemplate")
public class DemoServerDbConfig extends AbstractDbConfig {

    @Value("${spring.datasource.demo.server.url}")
    private String url;

    @Value("${spring.datasource.demo.server.username}")
    private String userName;

    @Value("${spring.datasource.demo.server.password}")
    private String password;

    @Value("${spring.datasource.demo.server.driver-class-name}")
    private String driveClassName;

    @Value(value = "${mybatis.demo.server.mapper-location}")
    private String mapperLocation;


    @Bean(name = "demoServer")
    public DataSource secondaryDataSource() {
        return dataSourceFactory(driveClassName, url, userName, password);
    }

    @Bean(name = "demoServerTemplate")
    public SqlSessionTemplate demoServerSqlTemplate() throws Exception {
        return new SqlSessionTemplate((sqlSessionFactory(secondaryDataSource(), mapperLocation)));
    }

    @Bean
    @Qualifier("demoServerTransaction")
    public PlatformTransactionManager demoServerTransaction() {
        return new DataSourceTransactionManager(secondaryDataSource());
    }
}

package com.example.demo.config;

import com.example.demo.interfaceAcc.DemoSqlServerMapper;
import com.example.demo.interfaceAcc.DemoUserMapper;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.example"},annotationClass = DemoSqlServerMapper.class,
        sqlSessionTemplateRef = "demoSqlServerTemplate")
public class DemoSqlServerDbConfig extends AbstractDbConfig {

    @Value("${spring.datasource.demo.SqlServer.url}")
    private String url;

    @Value("${spring.datasource.demo.SqlServer.username}")
    private String userName;

    @Value("${spring.datasource.demo.SqlServer.password}")
    private String password;

    @Value("${spring.datasource.demo.SqlServer.driver-class-name}")
    private String driveClassName;

    @Value(value = "${mybatis.demo.SqlServer.mapper-location}")
    private String mapperLocation;


    @Bean(name = "demoSqlServer")
    public DataSource secondaryDataSource() {
        return dataSourceFactory(driveClassName, url, userName, password);
    }

    @Bean(name = "demoSqlServerTemplate")
    public SqlSessionTemplate demoSqlServerSqlTemplate() throws Exception {
        return new SqlSessionTemplate((sqlSessionFactory(secondaryDataSource(), mapperLocation)));
    }

    @Bean
    @Qualifier("demoSqlServerTransaction")
    public PlatformTransactionManager demoSqlServerTransaction() {
        return new DataSourceTransactionManager(secondaryDataSource());
    }
}

目前配置都已完成接下来的就是使用写dao写mapper

MySQL , SQL Server 这个简单就只展示两个了，这分别是 .java , .xml 要区分开哦

package com.example.demo.dao;

import com.example.demo.interfaceAcc.DemoUserMapper;

import java.util.List;

@DemoUserMapper
public interface UserDao {
    
    List<SC> list();
    
}

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.demo.dao.UserDao">

    <select id="list" resultType="com.example.demo.entity.SC">
        SELECT Sid as SId,Cid as CId FROM sc 
    </select>
</mapper>

package com.example.demo.dao;

import com.example.demo.entity.SysUseRole;
import com.example.demo.entity.TenantMst;
import com.example.demo.interfaceAcc.DemoSqlServerMapper;

import java.util.List;

@DemoSqlServerMapper
public interface SqlServerDao {

    List<SysUseRole> list();

}

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.demo.dao.SqlServerDao">

    <select id="list" resultType="com.example.demo.entity.SC">
        SELECT id as SId from sys_user_role
    </select>
</mapper>

最后随便写一个Controller测试即可

@GetMapping("/getMysql3")
public String getMysql3() {
    List<SysUseRole> list = sqlServerDao.list();
    return String.valueOf(list);
}

摘自：https://www.cnblogs.com/sanjinxin/p/15901614.html

不灭的焱

革命尚未成功，同志仍须努力下载JDK17