java springboot 双数据源连接sqlserver mysql

背景：

测试某台机器能否连接sqlserver

1 首先安装sqlserver 2008:

https://www.cnblogs.com/yzl050819/p/8284242.html

首次通过sql server 2008 managerment studio登录服务器为机器名\SQLEXPRESS

2 配置sqlserver登录用户（此前为windows）

https://www.cnblogs.com/chlyA-F/p/6075675.html

1）左侧的对象资源管理器->安全性->登录名，右击sa->属性，为sa用户添加密码，选择sql server身份验证，在“状态”项中授予连接到数据库和登录启用;

2）右击对象资源管理器的根节点，选择属性->安全性->sql server和windows身份验证模式，然后就这样

3 配置远程连接及端口

sql server configuration manage － sql server网络适配－sqlexpress的协议－TCP／IP（启用）－ip地址－IPALL－TCP端口1433

studio－根节点属性－连接－允许远程连接勾选

4 spring boot配置数据源

https://www.cnblogs.com/wang-yaz/p/9561188.html

spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test
spring.datasource.second.username=xxx
spring.datasource.second.password=xxxx
spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
 
注意1：连接数据库的方式不一样，mysql是/test ,sqlServer是;DatabaseName=test
spring.datasource.url=jdbc:mysql://xxxx/test  
spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test

5 测试代码

https://blog.csdn.net/TTTTTdzhao/article/details/81627023

try {
    JdbcTemplate jdbcTemplate = (JdbcTemplate)SpringUtil.getBean("secondaryJdbcTemplate");
    Connection connection = jdbcTemplate.getDataSource().getConnection();
 
    if(connection != null) {
        logger.info("sqlserver 数据库连接成功！");
    }
    else {
        logger.info("sqlserver 数据库连接失败！");
    }
 
    List list = jdbcTemplate.queryForList("select * from master.dbo.MSreplication_options");
    logger.info("sql 输出{}", list);
} catch (Exception e) {
    logger.error(e.getMessage());
}

代码：

/**
 * Created by xxx on 19/7/11.
 */
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
 
/**
 * 取得spring上下文工具类
 * Created by xxx on 18/6/21.
 */
@Component
public class SpringUtil implements ApplicationContextAware {
 
    private static Logger LOGGER = LoggerFactory.getLogger(SpringUtil.class);
 
    private static ApplicationContext applicationContext = null;
 
    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        if(SpringUtil.applicationContext == null){
            SpringUtil.applicationContext  = applicationContext;
        }
        LOGGER.info("---------------Spring Application Context---------------");
    }
 
    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }
 
    public static Object getBean(String name){
        return getApplicationContext().getBean(name);
 
    }
 
    public static <T> T getBean(Class<T> clazz){
        return getApplicationContext().getBean(clazz);
    }
 
    public static <T> T getBean(String name,Class<T> clazz){
        return getApplicationContext().getBean(name, clazz);
    }
 
}

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
 
@Configuration
public class SqlServerDataSourceConfig {
 
    @Bean(name = "sqlServerDataSource")
    @Qualifier("sqlServerDataSource")
    @ConfigurationProperties(prefix="spring.datasource.second")
    public DataSource getMyDataSource(){
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = "mysqlDataSource")
    @Qualifier("mysqlDataSource")
    @ConfigurationProperties(prefix="spring.datasource.out")
    public DataSource getOutDataSource(){
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(
            @Qualifier("sqlServerDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
 
    @Bean(name = "mysqlJdbcTemplate")
    public JdbcTemplate mysqlJdbcTemplate(
            @Qualifier("mysqlDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
 
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
 
@SpringBootApplication
public class UserApplication {
 
    private static Logger logger = LoggerFactory.getLogger(UserApplication.class);
 
    public static void main(String[] args) {
        SpringApplication.run(UserApplication.class, args);
 
 
        try {
            JdbcTemplate jdbcTemplate = (JdbcTemplate)SpringUtil.getBean("secondaryJdbcTemplate");
            Connection connection = jdbcTemplate.getDataSource().getConnection();
 
            if(connection != null) {
                logger.info("sqlserver 数据库连接成功！");
            }
            else {
                logger.info("sqlserver 数据库连接失败！");
            }
 
            List list = jdbcTemplate.queryForList("select * from vvvvv");
            logger.info("sql 输出{}", list);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
         
        try {
            JdbcTemplate jdbcTemplate = (JdbcTemplate)SpringUtil.getBean("mysqlJdbcTemplate");
            Connection connection = jdbcTemplate.getDataSource().getConnection();
 
            if(connection != null) {
                logger.info("mysql 数据库连接成功！");
            }
            else {
                logger.info("mysql 数据库连接失败！");
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
 
    }
 
}

spring.datasource.second.jdbc-url=jdbc:sqlserver://192.168.xx.xx:1433
spring.datasource.second.username=sa
spring.datasource.second.password=xxxxxxx
spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
 
 
 
spring.datasource.out.jdbcUrl=jdbc:mysql://192.168.xx.xx:3306?useUnicode=true&characterEncoding=utf8
spring.datasource.out.driverClassName=com.mysql.jdbc.Driver
spring.datasource.out.username=xxx
spring.datasource.out.password=xxx