SpringBoot+MybatisPlus项目配置MySQL+SqlServer两种数据库并动态修改SqlServer的IP

SpringBoot+Mybatis-Plus项目配置MySQL+SqlServer两种数据库并且动态修改SqlServer的IP和数据库名

一 需求

通过mysql上的数据库获取到各个服务器的IP地址和服务器名称，然后点击哪个服务器的ip就连接哪个服务器上面的SqlServer数据库，获取上面的数据。

主要参考资料

https://blog.csdn.net/qq_29752857/article/details/120214853     动态添加移除数据源 https://blog.csdn.net/liadc/article/details/108273358     mybatis项目配置多个数据库

二 开始

1.添加依赖

除SpringBoot+MybatisPlus需要的基本依赖，还需添加下面的依赖

		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
			<version>3.3.2</version>
		</dependency>

2.修改application.yml文件的配置

# 数据库
spring:
  datasource:
    dynamic:
      primary: exam #设置主库
      strict: false # 设置false 连接失败默认连主库
      datasource:
        exam: #主库
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/exam?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false
          username: root
          password: 123456
        netexam: # 副库
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=netExam
          username: sa
          password: sa

3.给对应业务层的方法添加注解

其中配置了两个数据源，exam与netexam，将exam设置为主库，如果对应的业务层方法未加@DS注解，则默认使用exam主库,如果想切换为副库netexam.可在整个service层或对应的方法上加注解并加上对应的数据源名称，例如@DS("netexam")。

@Service
@DS("netexam")
public class CourseService {

    @Autowired
    ChangeDBUtils changeDBUtils;
    @Autowired
    private SubjectMapper subjectMapper;

    
    @DS("netexam")
    public JSONObject test(String ip) {
        JSONObject json = new JSONObject();
        json = ExceptionUtils.setResultMsg(json, ExceptionUtils.SUCCESS);
        changeDBUtils.changDatabase(ip,"netExam");
        //查询subject
        QueryWrapper<Subject> subjectQueryWrapper = new QueryWrapper<>();
        List<Subject> list = subjectMapper.selectList(subjectQueryWrapper);
        json.put("data", list);
        return json;
    }
}

4.增加动态修改数据库的实体类

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.validation.constraints.NotBlank;

@Data
public class DataSourceDTO {
    @NotBlank
    @ApiModelProperty(value = "连接池名称", example = "db1")
    private String poolName;
    
    @NotBlank
    @ApiModelProperty(value = "JDBC driver", example = "com.mysql.cj.jdbc.Driver")
    private String driverClassName;
    
    @NotBlank
    @ApiModelProperty(value = "JDBC url 地址", example = "jdbc:mysql://x.x.x.x:3306/x?useUnicode=true&characterEncoding=utf-8")
    private String url;
    
    @NotBlank
    @ApiModelProperty(value = "JDBC 用户名", example = "sa")
    private String username;
    
    @ApiModelProperty(value = "JDBC 密码")
    private String password;

    private String ip;
    private String name;

}

5.增加获取Spring的bean的配置类

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

import java.util.Map;


@Component
public class SpringUtils implements ApplicationContextAware {

    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringUtils.applicationContext = applicationContext;
    }

    public static <T> T getBean(String beanName) {
        if(applicationContext.containsBean(beanName)){
            return (T) applicationContext.getBean(beanName);
        }else{
            return null;
        }
    }

    public static <T> Map<String, T> getBeansOfType(Class<T> baseType){
        return applicationContext.getBeansOfType(baseType);
    }

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }
}

6.添加更换数据库工具类

首先先在application.yml增加参数，然后可根据传的ip地址和数据库名更换数据库

database.net.poolname: netexam
database.net.driverclassname: com.microsoft.sqlserver.jdbc.SQLServerDriver
database.net.username: sa
database.net.password: sa

@Component
public class ChangeDBUtils {


    @Value("${database.net.poolname}")
    private String poolName;
    @Value("${database.net.driverclassname}")
    private String driverClassName;
    @Value("${database.net.username}")
    private String username;
    @Value("${database.net.password}")
    private String password;

    /**
     * @Description: 改变数据源
     * @Date 2022/3/16 10:40
     * @Param: ip 地址  name 数据源名字
     * @return
    */
    @DS("netexam")
    public void changDatabase(String ip, String name) {
        DataSourceDTO dto = new DataSourceDTO();
        //获取数据源bean
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) SpringUtil.getBean("dataSource");
        //删除当前需更换数据源  根据数据源名 netexam
        ds.removeDataSource(poolName); // 数据源名字
       //添加新的数据源  需更换的SqlServer数据库
        dto.setPoolName(poolName);
        //仅修改SqlServer数据源  mysql需改参数
        dto.setDriverClassName(driverClassName);
        dto.setUrl("jdbc:sqlserver://"+ip+":1433;DatabaseName="+name);
        dto.setUsername(username);
        dto.setPassword(password);
        DataSourceProperty dataSourceProperty = new DataSourceProperty();
        BeanUtils.copyProperties(dto, dataSourceProperty);
        DefaultDataSourceCreator dataSourceCreator = (DefaultDataSourceCreator) SpringUtil.getBean("dataSourceCreator");
        DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty);
        ds.addDataSource(dto.getPoolName(), dataSource);

    }

7.在Service层通过调用更改数据库的方法进行动态更改数据库

@Service
@DS("netexam")
public class CourseService {

	//
    @Autowired
    ChangeDBUtils changeDBUtils;
    @Autowired
    private SubjectMapper subjectMapper;


    @DS("netexam")
    public JSONObject test(String ip) {
        JSONObject json = new JSONObject();
        json = ExceptionUtils.setResultMsg(json, ExceptionUtils.SUCCESS);
        //可通过传的ip和数据库名称进行更换数据库
        changeDBUtils.changDatabase(ip,"netExam");
        //查询subject
        QueryWrapper<Subject> subjectQueryWrapper = new QueryWrapper<>();
        List<Subject> list = subjectMapper.selectList(subjectQueryWrapper);
        json.put("data", list);
        return json;
    }
}

如果需要动态更换mysql数据库，与之同理，只需在application.yml将配置更换为mysql的配置即可。

看到这里了，不妨留个赞再走吧